| Author |
Topic |
|
ssunny
Posting Yak Master
133 Posts |
 Posted - 2012-02-07 : 18:55:41
|
| Hi All,I am trying to set up a transactional replication between SQL 2000 (publisher, distributer) and SQL. 2008(subscriber) for some 50 tables.Data on publisher constantly gets updated. I have restored a week old backup on subscriber. I have selected "no initial snapshot required" on subscriber property since I have the data with schema on subscriber ( week old data)Now when adding tables(articles) , I get following options: Do not touch the data Drop and recreate the table on subscriber Drop all the data on subscriberI have selected first option ( do not touch the data) because I don't want to recreate the table or want to loose the existing data.If I add a single row in one table on publisher, it sucssesfully gets inserted on subscriber.Huhhh now my questions are:1. If I go with this approach and turn on the replication, I am not going to get the delta of that one week data and things will break which I absolutely don't want. How should I overcome this problem?2. Should I create a snapshot replication any sync the data on subscriber with publisher and then create a transactional replication so that I don't loose any data?3. Or for the first time I create the initial snapshot and then change option to " initial snapshot not needed" ?Sorry for the long message. Please help.Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-08 : 08:25:00
|
| If all servers are in the same data center, run a snapshot. Which you should have done in the first place. Then choose option #2 (Drop and recreate the table on subscriber -- the default). |
 |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 09:26:16
|
| Thanks for the reply russell. Just to make sure I get this correct.what you are saying is:1. Stop any development on publisher.2. Take a snapshot on subscriber so that data will be in sync.3. Turn on the transactional replication using option #2 ( drop and recreate table on subscriber)But I already have the table with week old data on subscriber. And if I go with option 2 , for every insert table on subscriber will be recreated with data. Is that correct? And table does not get created with all dependencies ( fk constraints etc)I guess I don't want to drop and create table on subscriber for every transaction and that too without all dependencies.Please let me know how can I solve this issue?I really appreciate your help. Thank you. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-08 : 09:37:13
|
quote:
Originally posted by ssunny
what you are saying is:1. Stop any development on publisher.2. Take a snapshot on subscriber so that data will be in sync.3. Turn on the transactional replication using option #2 ( drop and recreate table on subscriber)
1. No need to stop development.2. Snapshot runs against the publisher, objects and data are copied (distributed) to subscriberquote:
Originally posted by ssunny
But I already have the table with week old data on subscriber. And if I go with option 2 , for every insert table on subscriber will be recreated with data. Is that correct? And table does not get created with all dependencies ( fk constraints etc)
No, table is dropped/created only when snapshot is run (which should be close to never except for the 1st time.In the article properties, you can select which types of constraints, indexes partition schemes etc to copy with the snapshot. I almost always include nonclustered indexes.I usually drop foreign keys at the subscribers. They aren't needed there.quote:
Originally posted by ssunny
I guess I don't want to drop and create table on subscriber for every transaction
It's just for the snapshot. Not for the transactions. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 10:19:20
|
| russell,So where is the option to tell the replication to run a snapshot for the 1st time only and once the data is in sync, don't drop the table for every transaction ? Ideally I would like to keep all the dependencies on the subscriber as subscriber will become the source in some time. Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-08 : 13:59:00
|
| Just run the snapshot agent.There is no option to tell it not to recreate the tables with every transaction. Because it doesn't do that ever.I'd like to know what you're really trying to accomplish here. Also, I'd strongly suggest you read How Transaction Replication Works.You have some fundamental misconceptions. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 14:50:01
|
| Thanks for the reply again Russell. Here's what I am arting to do. Right now our application and cms talks to database server A which constantly gets updated. I am trying to set up a replication (transactional) from server A (sql 2000) to server C (sql 2008) Because server C can't see server A, I have created a server B (sql 2008) which can connect to both server A and server C. So the paln is to set up a transactional replication from A to B and from B to C and once it is set up and working , point the application to C. I have restored the full backup on B and C from A (old backup) Right now I am working on setp 1 which is the replication from A to B. These are the reasons I can not afford to loose any dependencies on Subscriber (server B for step 1) or to drop and recreate the table on Subscriber. I have tried as you suggested that is drop and recreated table on Subscriber for the initial snapshot but got an errorsaying can't drop the table because of the foreign key relationships which makes sense.What approach should I take? Can I create a snapshot replication and have tables synced with it for the first time (I don't know if snapshot replication also drop and recreate the table on Subscriber) and then turn off the Snapshot replication and trun on the Transactional replication so that i don't have a data loos?Or to ask for a downtime on Server A and take a backup,restore on Server B and create the Transactional replication?Please advice. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 15:12:19
|
| Thank you for the reply Tara. But if I use backup/restore there won't be any real time update on B or C right? If we are to point our application one by one to C, I want C to be updated real time just like A |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-08 : 15:21:16
|
| That's correct that it won't be real time. But why do you need it to be real time? You can get C up to date via transaction log backups from A. When you are going to do the switch to server C, you would do a final tlog backup on A and then restore it to C. In the meantime, you would be shipping the tlog backups and restoring them to C every 15 minutes or so.Backup/restore is the proper method here as it guarantees identical databases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 15:28:55
|
| So I should do following:1. Take a fresh backup of A and restore it on C (don't worry about restoring on B)2. Set up a transaction log backup job just for this purpose to run every 10 minutes on A and have B restore that on C.3. When the switch over time comes, take a log backup on A and restore it on C.4. Switch over. And this approach should work smoothly right? And at the most data on C will be 10-15 minutes older than A. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-08 : 15:31:09
|
| That's correct. I did this quite frequently when we had 2000 version around, with the exception that I don't need a B server. For 2005/2008, you'd instead use database mirroring. How old C is depends on your copy/restore frequency. To do this properly, you would restore WITH NORECOVERY until the final tlog backup is ready. Then you'd restore WITH RECOVERY to make it available on C.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 15:41:19
|
| C is month old backup but that is not an issue. I can take fresh backup on A today and restore it on C. Is there anything else other than WITH RECOVERY/NORECOVERY I need to make sure?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-08 : 16:03:15
|
| Agree with Tara 100% |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-08 : 16:28:44
|
| I have already migrated DTS packages to SSIS , sql jobs,scheduled tasks etc. from A to C. SO I think now restoring a fresh backup ans start log shipping should work fine. Thank you so much Tara and Russell. I will let you know how it goes or if I have any questions.Thank you again |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-09 : 11:41:04
|
| Back again with some more questions.So as discussed I tried restoring a fresh backup from A to C but got following error:Restore cannot operate on database because it is configured for database mirroring .Use alter database to remove mirroring if you intend to restore the database.If I remove mirroring, restore a backup,setup mirroring again and then set up a utility to transfer transaction logs every 15 minutes from A to C.Will this work? Will mirror database have any issues with this setup?Thanks. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2012-02-09 : 12:33:00
|
| Or can I just pause mirroring,restore a db and resume mirroring? If I do that, database mirroring will sync the mirror db data with principal db (C) data correct? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
Transactional Replication SQL 2000 -SQL 2008