x

Schema changes in Replicated environment SQL server 2000

Hi,

Everytime we need to alter any schema, we need to generate the entire schema change script along with replication script. We follow following steps.

1> Script to Drop subscription

2> Script to Drop article

3> Script to Alter the schema (write Alter Table\Procedure script)

4> Script to Add article

5> Script to add subcription

So even a simple alter table script becomes very big due to the extra script we write for add\drop subcription, article.

At present the SQL server doesnt provide any way to generate ALTER table\procedure script Along with Replication script. If you try to get the SQL script for a table\procedure, the SQL server gives you Alter table\procedure script without add\drop subcription\article script. (there is no option such as "generate script with replication").

Please let us know the simpler way to generate script with replication, to alter\create any sql server 2000 object.

Thanks,

Vijendra
more ▼

asked Aug 12, 2010 at 06:25 AM in Default

vijendra_cs gravatar image

vijendra_cs
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

What you describe is the proper and appropriate way to handle a schema change (though if you have certain 3rd party software like Red Gates SQL Compare you at least have choices on how do step 3.)

Of course, the other thing to consider is that you probably do not want a large number of schema changes in production whether or not it is replicated.
more ▼

answered Aug 12, 2010 at 06:29 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+1. Just as a note: SQL 2005+ allows for replication of schema changes. We use it on 2008 and it is very effective when rolling out table design changes.
Aug 12, 2010 at 06:49 AM WilliamD
Another reason to stay current... or at least N-1.
Aug 12, 2010 at 07:03 AM Blackhawk-17
Good thing to add, thanks WilliamD.
Aug 12, 2010 at 07:03 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x305

asked: Aug 12, 2010 at 06:25 AM

Seen: 1489 times

Last Updated: Aug 12, 2010 at 07:00 AM