Adding a new node to an existing cluster

I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
These servers are getting old so what I wanted to do is add two new servers
to the cluster and take the original two offline. I have loaded the new
servers, connected them to our sans, and added them to the cluster. I am at
the point of loading SQL. I have read the books on line and according to BOL,
I should be able to "On the Cluster Management screen, select the node and
click Add'. However, I am not given the option, ALL four servers show up in
the "configured nodes" list. What my question is, if I continue, will ALL
servers be loaded/reloaded with SQL or just the top server (the one I am
trying to load).
You will add SQL to the new servers only, just watch the install screen
carefully, cause you can also uninstall the original two nodes
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:96494E06-031A-4370-B2AA-C8615627E5FD@.microsoft.com...
>I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
> These servers are getting old so what I wanted to do is add two new
> servers
> to the cluster and take the original two offline. I have loaded the new
> servers, connected them to our sans, and added them to the cluster. I am
> at
> the point of loading SQL. I have read the books on line and according to
> BOL,
> I should be able to "On the Cluster Management screen, select the node and
> click Add'. However, I am not given the option, ALL four servers show up
> in
> the "configured nodes" list. What my question is, if I continue, will ALL
> servers be loaded/reloaded with SQL or just the top server (the one I am
> trying to load).
|||That is what I am worried about...
I do NOT want to uninstall SQL from the existing nodes. What specifically
should I keep my eye out for?
"Rodney R. Fournier [MVP]" wrote:

> You will add SQL to the new servers only, just watch the install screen
> carefully, cause you can also uninstall the original two nodes
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
> "Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
> news:96494E06-031A-4370-B2AA-C8615627E5FD@.microsoft.com...
>
>
|||Make sure you install from one of the new machines.
Make sure you have someone standing over your shoulder watching the screens
and double checking your work.
Make sure you select ADD and see the server move to the right side (which
means they will be installed).
Make sure think about any and all defaults (this is how I uninstalled my one
node SQL Cluster a few months back, instead of adding a node).
It's very easy to either do it correctly or incorrectly. Like I said, have
someone double check the screen BEFORE you hit next and all should go well.
Lastly make sure you have a good restore on hand. Yes, I said restore, cause
a backup is worthless, unless you can and have actually tried restoring it.
I hate that people brag about backups, brag about restoring Or don't brag
about either, cause lack of either is a firing offense in my book. Ok, I am
off my soap box!
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:A8424504-3A78-4E91-B18B-42B76B18C8F8@.microsoft.com...[vbcol=seagreen]
> That is what I am worried about...
> I do NOT want to uninstall SQL from the existing nodes. What specifically
> should I keep my eye out for?
> "Rodney R. Fournier [MVP]" wrote:
|||Thank you so much. I understand, however where you say "Make sure you select
ADD and see the server move to the right side (which means they will be
installed)." That was my problem, all four servers are ALREADY on the right
side.
P.S. I am in full agreement on the backup part... ;-)
"Rodney R. Fournier [MVP]" wrote:

> Make sure you install from one of the new machines.
> Make sure you have someone standing over your shoulder watching the screens
> and double checking your work.
> Make sure you select ADD and see the server move to the right side (which
> means they will be installed).
> Make sure think about any and all defaults (this is how I uninstalled my one
> node SQL Cluster a few months back, instead of adding a node).
> It's very easy to either do it correctly or incorrectly. Like I said, have
> someone double check the screen BEFORE you hit next and all should go well.
> Lastly make sure you have a good restore on hand. Yes, I said restore, cause
> a backup is worthless, unless you can and have actually tried restoring it.
> I hate that people brag about backups, brag about restoring Or don't brag
> about either, cause lack of either is a firing offense in my book. Ok, I am
> off my soap box!
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
> "Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
> news:A8424504-3A78-4E91-B18B-42B76B18C8F8@.microsoft.com...
>
>
|||For more information about adding a node to a virtual server, refer to the
"How to add nodes to an existing virtual server (Setup)" topic in SQL Server
2000 Books Online.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:392A3D50-7EFA-4C36-82AB-0D74BE1ABB79@.microsoft.com...[vbcol=seagreen]
> Thank you so much. I understand, however where you say "Make sure you
> select
> ADD and see the server move to the right side (which means they will be
> installed)." That was my problem, all four servers are ALREADY on the
> right
> side.
> P.S. I am in full agreement on the backup part... ;-)
>
> "Rodney R. Fournier [MVP]" wrote:
|||Marilyn
Make sure your specifying the correct SQL Virtual Server name. It sounds
like you might be accidently trying to install a new instance of SQL and all
nodes are showing up.
Are you selecting the "Advanced Options" and getting the option to
"Maintain a Virtual Server for Failover Clustering" the Virtual Server? If
not then your more than likely attempting to install a new instance of SQL.
Regards
cT
"Marilyn" wrote:

> I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
> These servers are getting old so what I wanted to do is add two new servers
> to the cluster and take the original two offline. I have loaded the new
> servers, connected them to our sans, and added them to the cluster. I am at
> the point of loading SQL. I have read the books on line and according to BOL,
> I should be able to "On the Cluster Management screen, select the node and
> click Add'. However, I am not given the option, ALL four servers show up in
> the "configured nodes" list. What my question is, if I continue, will ALL
> servers be loaded/reloaded with SQL or just the top server (the one I am
> trying to load).
|||I got it! I just went for it. It took about 10 minutes to load with no
problems at all. Thanks for the help and advice. I'm really glad Microsoft
has these newsgroups. I have used them serveral times to find additional
suggestions and ideas. Thanks again. M ;-)
"Charles Tolento" wrote:
[vbcol=seagreen]
> Marilyn
> Make sure your specifying the correct SQL Virtual Server name. It sounds
> like you might be accidently trying to install a new instance of SQL and all
> nodes are showing up.
> Are you selecting the "Advanced Options" and getting the option to
> "Maintain a Virtual Server for Failover Clustering" the Virtual Server? If
> not then your more than likely attempting to install a new instance of SQL.
> Regards
> cT
> "Marilyn" wrote:

Adding a Distributor

I have a current merge replication system with a publisher server that is its
own distributor, and four remote subscriber servers. I now want to make the
present publisher/distributor a distibutor, and add a new publisher only
server. What is the easiest method?
Can I avoid having to recreate the whole system and remake the subscribers?
Thanks for advice.
You will have to script out your publications and subscriptions. Do a final
synchronization, drop them, and then migrate to the new publisher.
Then recreate everything.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"MDP" <MDP@.discussions.microsoft.com> wrote in message
news:0578784C-DA1B-40D5-9903-3F7228E79EA5@.microsoft.com...
>I have a current merge replication system with a publisher server that is
>its
> own distributor, and four remote subscriber servers. I now want to make
> the
> present publisher/distributor a distibutor, and add a new publisher only
> server. What is the easiest method?
> Can I avoid having to recreate the whole system and remake the
> subscribers?
> Thanks for advice.

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaks
If you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>
sql

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:
> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sq
l
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no nee
d
> additional one.
> THnaks
>
>
>
>

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 replication component

Hello,
I have a publication that is pushed from a SQL 2005 Standard box to
about 40 SQL express subscribers. One of the servers went down and was
rebuild and I reran the same install script as I ran on the other
servers ( I made the script). I pushed out the same subscription as
before to it, and it is running smoothly (transactional). I then
needed to set up a snapshot subscription to all servers, and this
server was the only one that would not allow me to. When I try and set
up a new subscription to it I get this message:
"Replication components are not installed on this server. Run SQL
Server SETUP again and select the option to install replication.
(Microsoft SQL Server, Error: 21028).
So I thought I would just redo the setup and only pick the replication
component, but you cant, it makes you pick the whole database engine
tree to get it, and then the install says that it is already installed
and won't let you continue. Same happens if I go through add/remove
programs and use the add component link.
Any thoughts or ideas on this error. Right now the only solution I
have is reinstall from scratch.
OK, that error message was totally wrong. Since adding it through the
UI was not working, I added another subscription through the UI and at
the end generated a script file instead of actually finishing it. I
then changed the script file to point at my stubborn server that
supposedly did not have replication components, and the script ran fine
and the subscription was created, and worked.
PT
|||Can you try to create a local publication on this server? I am not sure if
this message is erroneous or not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1168454660.767978.320110@.p59g2000hsd.googlegr oups.com...
> Hello,
> I have a publication that is pushed from a SQL 2005 Standard box to
> about 40 SQL express subscribers. One of the servers went down and was
> rebuild and I reran the same install script as I ran on the other
> servers ( I made the script). I pushed out the same subscription as
> before to it, and it is running smoothly (transactional). I then
> needed to set up a snapshot subscription to all servers, and this
> server was the only one that would not allow me to. When I try and set
> up a new subscription to it I get this message:
> "Replication components are not installed on this server. Run SQL
> Server SETUP again and select the option to install replication.
> (Microsoft SQL Server, Error: 21028).
> So I thought I would just redo the setup and only pick the replication
> component, but you cant, it makes you pick the whole database engine
> tree to get it, and then the install says that it is already installed
> and won't let you continue. Same happens if I go through add/remove
> programs and use the add component link.
> Any thoughts or ideas on this error. Right now the only solution I
> have is reinstall from scratch.
>

Add Article to Transactional

Greetings,
Have a transactional replication between 2 SQL 2000 servers. 1 is the publisher and the 2nd one is both distributor and subscriber.
Attempted to add an article to a publication via EM: right click the publication under 'Replication' folder and navigate to the
'Articles' tab. I 'showed' unpublished tables, check-marked the table I wanted added, got the warning screen about version compatibility, selected 'Apply' and then 'OK' to exit the properties page.
Of course the table does not appear in the subscriber db. I thought I followed what BOL said but I obviously missed something.
Anyone have any ideas as to what it was?
Thank you for your time.
Joe,
you need to run the snapshot agent to create the table snapshot, then run
the distribution agent to send it down.
Regards,
Paul Ibison

Add Article To Trans Rep

Greetings again,
I am trying to add a table to a publication that already has a subscriber and have that table replicate to the subscriber.
2 servers, win2000, SQL 2000 all sp's applied.
1 server is the publisher and the 2nd is both the distributor and subscriber.
I went through the publication's properties page (em-replication-right click- properties) and added the table to the published articles list.
Then on the good advice of Paul Ibson in an earlier post to this newsgroup I created a new snapshot of the table and had
the dist. agent apply it. All went well.
However, new data in the table is not being replicated.
I've exhausted BOL and the web searches. Any recommendations as to the exact process I should follow would be greatly appreciated.
Thank you for your time.
Joe,
have you run the distribution agent? Run sp_browsereplcmds in the
distribution database to see if your new rows are waiting there. If they are
then just running the distribution agent will fix it. If not, then perhaps
the log reader is disabled?
HTH,
Paul Ibison

Add a view in a diagram

Hi
I would like to import views in SQL Server's Diagram. I could only import tables. Thank you for your help
SQL Servers database diagram tool only supports diagrams
using tables. Some of the other data modeling tools support
views in diagrams. If you have Visio, you can reverse
engineer your database and include views in the diagrams.
-Sue
On Wed, 14 Apr 2004 07:36:02 -0700, "SLEMARIE"
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I would like to import views in SQL Server's Diagram. I could only import tables. Thank you for your help

Add a clustered index in a published table

Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:

> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:

> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:

> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:

> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

AD migration and SQL server logins.

Our SQL 2000 servers use Windows authentication. My question pertains to th
e
effect an AD migration has on SQL logins.
After the NT4 user accounts and groups are migrated to the AD domain and the
SQL server is upgraded to the AD domain, do the SQL logins automatically
reference the new user accounts in AD? If not do I have to manually add the
AD users and groups to SQL login and access to the databases? Or are there
tools available?You should not have to manually add the account again to SQL Server if your
network administrator choose to migrate the accounts with sid history. If
the accounts do not use sid history then you will need to add the accounts
back into SQL Server. I beleive one of the stored procedures you could use
is sp_sidmap.
"anthonyp" wrote:

> Our SQL 2000 servers use Windows authentication. My question pertains to
the
> effect an AD migration has on SQL logins.
> After the NT4 user accounts and groups are migrated to the AD domain and t
he
> SQL server is upgraded to the AD domain, do the SQL logins automatically
> reference the new user accounts in AD? If not do I have to manually add t
he
> AD users and groups to SQL login and access to the databases? Or are ther
e
> tools available?

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
Jerry
Hi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to a
n
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to
an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:
> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry