added article to current transactional repl. How to replicate additional article?

Hi, I already have transactional replication setup between two servers
(production server to reporting server).
I have created a new table witha primary key and have added this table as
an article to the publication.
How can i get my setup to take an initial snapshot of the table and apply it
to the reporting server?
Any help most appreciated!
thanks
John
John,
you will need to run the snapshot agent then synchronize. The whole process
from start to finish is:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start snapshot agent, start distribuition agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Are you saying that I have to take a snapshot of the whole database all over
again?
I thought I could take a snapshot of just the article that has been newely
added to the publication?
In actual fact, the 'Last Action' column on my agent (subsicriber' is saying
"The initial snapshot for article 'myTable' is not yet available"
Many thanks!
John
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQ2XvSVEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> John,
> you will need to run the snapshot agent then synchronize. The whole
> process from start to finish is:
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Start snapshot agent, start distribuition agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||John,
if you add a new table and subscription to that table, running the snapshot
agent won't create a complete snapshot - just the new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add user to R\O Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I? This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***"Toni" <teibner@.SQLallina.com> wrote in message
news:evNCYtFSGHA.5736@.TK2MSFTNGP10.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I? This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
The only way is to add that user to a role that has access... you could
create a server role that has SELECT permissions for all objects, but I'm
not sure that a new role's access would be grand-fathered into an r/o db.
Unfortunately, the only built-in role that leaves you with is sysadmin...
but it's worth a try.
-Mark

> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***
You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***You need to add a login for that user on the originating server, add a user
for that login on the
originating server and then add the same login on the reporting machine (mak
ing sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...

>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***

Add columns to an existing table

Question:
If I take an existing production table that already has data in it, and add
some columns to it. Does that fragment the table?
What I'm really getting at is: if I alter a table, will it perform
differently than it would have if I had dropped the table, rebuilt it with
the new columns, and then re-inserted the data?Yes, it's different. As far as I understand, when you add
new column (especially the fixed length columns) on
existing table, it will normally have page split if the
page is already very full, and thus gets fragmented. You
can rebuild the indexes after you added the column.
>--Original Message--
>Question:
>If I take an existing production table that already has
data in it, and add
>some columns to it. Does that fragment the table?
>What I'm really getting at is: if I alter a table, will
it perform
>differently than it would have if I had dropped the
table, rebuilt it with
>the new columns, and then re-inserted the data?
>
>.
>|||Hi Jeremy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you would like to know if there are difference
behaviors (especially on fragments) between altering a table and creating a new table. Have I
fully understood you? If there is anything I misunderstood, please feel free to let me know.
If you alter the table by means of scripting the code that EM uses you will see that it actually
creates a new table from scratch and then populates it with the old data. On the other hand, if
you create a new table, when inserting a new row into a table, SQL Server must determine
where to put it.
When a table has no clustered index, that is, when the table is a heap-a new row is always
inserted wherever room is available in the table. Even without a clustered index, space
management is quite efficient. If no pages with space are available, SQL Server must allocate
a whole new extent to the table.
A clustered index directs an insert to a specific page based on the value the new row has for
the clustered index key columns. The insert occurs when the new row is the direct result of an
INSERT statement or when it's the result of an UPDATE statement executed via a delete-
followed-by-insert (delete/insert) strategy. New rows are inserted into their clustered position,
splicing in a page via a page split if the current page has no room.
When the index is first created, an explicit FILLFACTOR setting can be applied. SQL Server
does not dynamically keep the specified percentage of empty space in the pages. If
FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT
or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page
splits for each INSERT and possibly each UPDATE.
For more detailed information on the page splitting, I'd recommend you read the book "Inside
SQL Server 2000" written by "Kalen Delaney" on the topic "Creating Tables" and "Data
Modification Internals".
Jeremy, does this answer your question? Please feel free to let me know if this answers your
question. If there is anything more I can still assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you, I believe that you have answered my question completely.
Regards
Jeremy
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:cXkVlEZqDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Jeremy,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your description, I understand that you would like to know if there
are difference
> behaviors (especially on fragments) between altering a table and creating
a new table. Have I
> fully understood you? If there is anything I misunderstood, please feel
free to let me know.
>
> If you alter the table by means of scripting the code that EM uses you
will see that it actually
> creates a new table from scratch and then populates it with the old data.
On the other hand, if
> you create a new table, when inserting a new row into a table, SQL Server
must determine
> where to put it.
> When a table has no clustered index, that is, when the table is a heap-a
new row is always
> inserted wherever room is available in the table. Even without a clustered
index, space
> management is quite efficient. If no pages with space are available, SQL
Server must allocate
> a whole new extent to the table.
> A clustered index directs an insert to a specific page based on the value
the new row has for
> the clustered index key columns. The insert occurs when the new row is the
direct result of an
> INSERT statement or when it's the result of an UPDATE statement executed
via a delete-
> followed-by-insert (delete/insert) strategy. New rows are inserted into
their clustered position,
> splicing in a page via a page split if the current page has no room.
> When the index is first created, an explicit FILLFACTOR setting can be
applied. SQL Server
> does not dynamically keep the specified percentage of empty space in the
pages. If
> FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent
full. An INSERT
> or UPDATE made after the creation of an index with a 100 percent
FILLFACTOR causes page
> splits for each INSERT and possibly each UPDATE.
>
> For more detailed information on the page splitting, I'd recommend you
read the book "Inside
> SQL Server 2000" written by "Kalen Delaney" on the topic "Creating Tables"
and "Data
> Modification Internals".
>
> Jeremy, does this answer your question? Please feel free to let me know if
this answers your
> question. If there is anything more I can still assist you with, please
feel free to post it in the
> group.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>

add an sql 2000 instance to a production server

Hi,
If I add an sql 2000 instance to a existing prod sql box used as a
developping sql instance.
The developers don't need to tough the production box..
Is it feasible? it will reduce the production performance?
What's the side effect?
What about creating a virtual server -- 2-3 virtual sql servers in a one
physical box.
Thanksmecn wrote:
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
Don't do that. Production and developing (or test) environment should be
separated. Separation means that there is no influence between them.
Unfortunately, when two instances are installed on the same server, one
instance can easily affect performance of other instance.
> What's the side effect?
When you add another (dev) instance, it will use common system
resources. Dev instance will consume RAM memory potentially available
for prod instance, it will also utilize processor(s) and IO.
IMHO it's a risky move.
> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
Better idea, because virtualization introduces much higher separation
level, but I think that's still not enough. Let production SQL Server
work on dedicated machine.
Best regards,
Marcin Guzowski
http://guzowski.info|||Thanks. I got it.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ufol$grr$1@.news.onet.pl...
> mecn wrote:
>> Hi,
>> If I add an sql 2000 instance to a existing prod sql box used as a
>> developping sql instance.
>> The developers don't need to tough the production box..
>> Is it feasible? it will reduce the production performance?
> Don't do that. Production and developing (or test) environment should be
> separated. Separation means that there is no influence between them.
> Unfortunately, when two instances are installed on the same server, one
> instance can easily affect performance of other instance.
>> What's the side effect?
> When you add another (dev) instance, it will use common system resources.
> Dev instance will consume RAM memory potentially available for prod
> instance, it will also utilize processor(s) and IO.
> IMHO it's a risky move.
>> What about creating a virtual server -- 2-3 virtual sql servers in a one
>> physical box.
> Better idea, because virtualization introduces much higher separation
> level, but I think that's still not enough. Let production SQL Server work
> on dedicated machine.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info|||Do I need more sql licenses for virtual servers
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uWMC86OuHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
> What's the side effect?
> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
> Thanks
>|||mecn wrote:
> Do I need more sql licenses for virtual servers
I don't know if it is true for SQL Server 2000 (probably not), but in
SQL Server 2005 it depends on SQL Server version (edition). If you have
SQL Server 2005 Enterprise Edition, you don't need additional licenses
for each virtual server.
In other cases, you must buy new licenses.
Best regards,
Marcin Guzowski
http://guzowski.info|||Thanks a lot dfor your help!!
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ui6o$pu8$1@.news.onet.pl...
> mecn wrote:
>> Do I need more sql licenses for virtual servers
> I don't know if it is true for SQL Server 2000 (probably not), but in SQL
> Server 2005 it depends on SQL Server version (edition). If you have SQL
> Server 2005 Enterprise Edition, you don't need additional licenses for
> each virtual server.
> In other cases, you must buy new licenses.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info

add an sql 2000 instance to a production server

Hi,
If I add an sql 2000 instance to a existing prod sql box used as a
developping sql instance.
The developers don't need to tough the production box..
Is it feasible? it will reduce the production performance?
What's the side effect?
What about creating a virtual server -- 2-3 virtual sql servers in a one
physical box.
Thanks
mecn wrote:
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
Don't do that. Production and developing (or test) environment should be
separated. Separation means that there is no influence between them.
Unfortunately, when two instances are installed on the same server, one
instance can easily affect performance of other instance.

> What's the side effect?
When you add another (dev) instance, it will use common system
resources. Dev instance will consume RAM memory potentially available
for prod instance, it will also utilize processor(s) and IO.
IMHO it's a risky move.

> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
Better idea, because virtualization introduces much higher separation
level, but I think that's still not enough. Let production SQL Server
work on dedicated machine.
Best regards,
Marcin Guzowski
http://guzowski.info
|||Thanks. I got it.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ufol$grr$1@.news.onet.pl...
> mecn wrote:
> Don't do that. Production and developing (or test) environment should be
> separated. Separation means that there is no influence between them.
> Unfortunately, when two instances are installed on the same server, one
> instance can easily affect performance of other instance.
>
> When you add another (dev) instance, it will use common system resources.
> Dev instance will consume RAM memory potentially available for prod
> instance, it will also utilize processor(s) and IO.
> IMHO it's a risky move.
>
> Better idea, because virtualization introduces much higher separation
> level, but I think that's still not enough. Let production SQL Server work
> on dedicated machine.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info
|||Do I need more sql licenses for virtual servers
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uWMC86OuHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
> What's the side effect?
> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
> Thanks
>
|||mecn wrote:
> Do I need more sql licenses for virtual servers
I don't know if it is true for SQL Server 2000 (probably not), but in
SQL Server 2005 it depends on SQL Server version (edition). If you have
SQL Server 2005 Enterprise Edition, you don't need additional licenses
for each virtual server.
In other cases, you must buy new licenses.
Best regards,
Marcin Guzowski
http://guzowski.info
|||Thanks a lot dfor your help!!
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ui6o$pu8$1@.news.onet.pl...
> mecn wrote:
> I don't know if it is true for SQL Server 2000 (probably not), but in SQL
> Server 2005 it depends on SQL Server version (edition). If you have SQL
> Server 2005 Enterprise Edition, you don't need additional licenses for
> each virtual server.
> In other cases, you must buy new licenses.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info

add an sql 2000 instance to a production server

Hi,
If I add an sql 2000 instance to a existing prod sql box used as a
developping sql instance.
The developers don't need to tough the production box..
Is it feasible? it will reduce the production performance?
What's the side effect?
What about creating a virtual server -- 2-3 virtual sql servers in a one
physical box.
Thanksmecn wrote:
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
Don't do that. Production and developing (or test) environment should be
separated. Separation means that there is no influence between them.
Unfortunately, when two instances are installed on the same server, one
instance can easily affect performance of other instance.

> What's the side effect?
When you add another (dev) instance, it will use common system
resources. Dev instance will consume RAM memory potentially available
for prod instance, it will also utilize processor(s) and IO.
IMHO it's a risky move.

> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
Better idea, because virtualization introduces much higher separation
level, but I think that's still not enough. Let production SQL Server
work on dedicated machine.
Best regards,
Marcin Guzowski
http://guzowski.info|||Thanks. I got it.
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ufol$grr$1@.news.onet.pl...
> mecn wrote:
> Don't do that. Production and developing (or test) environment should be
> separated. Separation means that there is no influence between them.
> Unfortunately, when two instances are installed on the same server, one
> instance can easily affect performance of other instance.
>
> When you add another (dev) instance, it will use common system resources.
> Dev instance will consume RAM memory potentially available for prod
> instance, it will also utilize processor(s) and IO.
> IMHO it's a risky move.
>
> Better idea, because virtualization introduces much higher separation
> level, but I think that's still not enough. Let production SQL Server work
> on dedicated machine.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info|||Do I need more sql licenses for virtual servers
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uWMC86OuHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> If I add an sql 2000 instance to a existing prod sql box used as a
> developping sql instance.
> The developers don't need to tough the production box..
> Is it feasible? it will reduce the production performance?
> What's the side effect?
> What about creating a virtual server -- 2-3 virtual sql servers in a one
> physical box.
> Thanks
>|||mecn wrote:
> Do I need more sql licenses for virtual servers
I don't know if it is true for SQL Server 2000 (probably not), but in
SQL Server 2005 it depends on SQL Server version (edition). If you have
SQL Server 2005 Enterprise Edition, you don't need additional licenses
for each virtual server.
In other cases, you must buy new licenses.
Best regards,
Marcin Guzowski
http://guzowski.info|||Thanks a lot dfor your help!!
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:f5ui6o$pu8$1@.news.onet.pl...
> mecn wrote:
> I don't know if it is true for SQL Server 2000 (probably not), but in SQL
> Server 2005 it depends on SQL Server version (edition). If you have SQL
> Server 2005 Enterprise Edition, you don't need additional licenses for
> each virtual server.
> In other cases, you must buy new licenses.
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info