Replicating Changing Databases
-
If I am using Merge Replication with a database of say 20GB, how do I upgrade my database? Do I have to break replications and reconfigure or is there another way that I can do it? I am using SQL Server 2000 SP3a.
-
What do you mean by upgrading your db, applying new service packs?
With Service Packs you should read the notes that come with it to understand in wich order you need to apply service packs to the subscribers, publisher and distributor, but you shouldn't have problems.
-
hey racosa, By upgrading I mean changing the structure of the database, i.e. expanding tables and fields, adding tables, and populating those fields as well with an automated process.
-
This is not a trivial thing. If you modify the publication you will likely end up having to re-initalize all your subscriptions. So before setting up this scenario TEST TEST TEST! Now then, to answer your question, you either do the modification through the agent GUI or through the replication stored procedures like sp_addmergearticle, sp_dropmergearticle, and sp_changemergearticle.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Hi Gary, Thanks for the reply, I totally agree. What does the re-initialize subscription actually do. I understand how to do it, I am just not sure what exactly it does. Does it mean that the subscriptions get a new copy of the snapshot? What about killing all subscriptions and then changing the master database, republish and resubscribing the subscribers? Would that not be better?
-
I've never actually done a thorough test to see what all initializing the subscription does. One thing it does is goof up managed identities! So if you are using them (I am redisigning now to get rid of them) be prepared to do lots of repairing of data after the managed identity ranges get goofed up.
I got this job as DBA after the DB had already been setup and in operation for 6 months. I had never been a DBA before however I had been a SQL Dev for 6 years prior to coming here. I literally had to learn on the fly(no problem there I actually enjoy that !) and still have a long ways to go. However I can tell you that there is no way I want to use managed identities again!
Why do you think that you will be changing the schema after you have the replication set up? Is there a way you can change your schema now to be more adaptable for different data so that a schema change is not required but you simply add a record to a type table with a foreign key relationship so that as needs change the same structure will work without changing it? Even though I have some people that resisted the types of changes I was proposing as soon as I explained how much flexibility they were going to gain by being able to simply add records to a table rather than redesigning the DB they were more than happy to have those changes done.
BTW: Even though you are using merge replication don't include your sprocs, and udfs in the publication. Either make a separate snapshot publication for them or use a dts package. You will be much happier in the long run!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary, Thanks again for the quick reply. You make some very interesting comments. If you can imagine the database I have originated from an old ISAM database and with continued development of the product the database changes. Just recently we moved to SQL and that works fine, although not as fast as we would like. We have spent a few moths improving the queries etc, but the structure of the database can change especially when we have to make specific modules for new improved features, hence the need for database changes. Once replicating and we have to make these changes, which is better, to re-initialize or to discontinue replication and reconfigure it? Thanks for your kind information Gary, greatly appreciated.
-
I would have to say it all depends. If you are simply adding new tables you might be better off just adding another publication. If you can get all the subscribers to sync before making the changes and then rolling them out then you could make your modifications and then reinitialize your subscriptions or drop re-create the publication. I don't see a good way to do this if you have too many subscriptions though!
So, aprox how many subscribers would you have?
How often do you think you will really need to modify the database?
Are you planning on using managed identities?
When you say you need to add specific modules to support new functionality can you try to forsee the data requirements and develop them up front and then add the sp support later? (I know that most of this will be no, but the more you can do up front the better!)
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary,
-
Gary, I would probably have about 8 subscribers. The modifications to the database could be as frequent as every 3 months. Terrible I know, but at this time I do not have control of this. As for developing the database prior, it is not as simple as it sounds. This is due to the fact that our product is being improved with and by customer specifications, which change from customer to customer. I am not sure what you mean by managed identities, this is something our developers would probably know about ( I am just the DBA involved here
) but what sort of impact does this have? Which do you prefer dto do, a reinitialize or a new subscription? Thanks Gary! -
Managed identities are when you have a column in the table with the identity propberty turned on but the "Not for Replication" flag set. Then when you replicate the database replication is supposed to allocate id ranges for each subscriber so that you don't have collisions. Sounds like a great concept and it is WHEN IT WORKS RIGHT. The problem is that every time you initialize a subscription it changes the ranges. And everytime you add a new subscription it seems to apply the lowest possible range it knows about. Only problem being that sometimes that range has already been given so now the new subscription is trying to overwrite old data. Unfortunately I have been bitten by this all too many times (twice just today) and have had to fix the data every time.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Hmmm, sounds like a nightmare to manage Gary 🙂 We have a separate unique identifier within our product to manage the replication and prevent collisions so generally we are OK. With your experience would you rather resubscribe or reinitialize? How do you go about changing the state of your databases when perofrming tasks that I have explained? Thanks again for your time!
-
Well it will be far less work to re-initialize. You just need to make sure to sync up all your subscribers before modifying your schema and re-initializing.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
My two cents on managed identities. They work until they don't. Biggest issue I've had (using transactional with updating subscribers) is that someone will push a transaction that exhausts the available key range.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply