August 25, 2009 at 9:25 am
Guys,
In SQL Server 2000, is it possible to create a subscription of a live database which is continuously getting updated? Would data be synchronized with master?
Regards,
August 25, 2009 at 9:32 am
Your post is somewhat unclear, let me see if I can clarify. You have a database you want to replicate that is in SQL 2000 that gets updated a lot. You reference to master concerns me, you aren't talking about the master database but the master copy of the data? Also is the database getting updated the master copy, if not then we should talk about your replication topology and what your goals are. Please give more detail.
CEWII
August 25, 2009 at 9:35 am
depends on what kind of replication you want to set up.... but anyways if you are thinking of doing a transactional replication (which is the most common), then the answer is Yes.
When you create the replication on your so called live database, and you run the snapshot agent, it would LOCK your tables (defined in replication) one - by - one and BCP out the datab from there. meanwhile the log reader agent would be capturing the DML for that table and keeping a record in the distribution database. During this time, sy for e.g if you have table A locked by the snapshot - all DML would have to WAIT until the lock is released by the bcp. there could be timeouts at the application level this time due to blocking.
When the snapshot completes, the distribution agent would take over and go to your subscriber to DROP (default setting) the tables there and create it with the schema files it generated in the snapshot. After that it would populate the data at the subsciber using the bcp files. This would also happen one by one. its a good idea to prepare for this downtime in advance at the subscriber.
Once the snapshot is applied to the subscriber, the DML captured by log reader agent while all of this was happening would be applied.
So yes you could set up replication on a live server.
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 12:11 pm
Thanks a lot for your quick response.
As you said, there could be timeouts at the application level this time due to blocking. It means Client access will be affected in the master copy and I cannot do it without it.
In SQL Server 2005 and 2008, we have a feature of Initializing a Transactional Subscription with a backup but unfortunately we dont have it here.
We have 3 servers (Publisher, Distributor and Subsccriber). If I have deleted the distribution agent job in subscriber, can I restore msdb database(backup taken one day before) and recover the lost subscription. I believe we will just loose job hitory information of 1 day.
Regards.
August 25, 2009 at 12:18 pm
Elliott W,
Yes, you are absolutely right. I am talking about master copy of the data.
My goal is to create subscription of a publisher database without affecting any access to it since its continously in use. This is SQL Server 2000.
We can do it in SQL Server 2005 and 2008 using Initializing a Transactional Subscription with a backup.
August 25, 2009 at 12:34 pm
Is this a 24/7 app? When is your normal maintenance window?
CEWII
August 25, 2009 at 12:40 pm
Yes, it is 24*7.
Maintainance window requires notification to several of clients which will be done as last resort.
August 25, 2009 at 12:56 pm
restoring msdb database might work in getting the distribution agent back ... but I am not sure if it would solve your problem or replication thinking that I am all synced up with publisher or not.......
i have never tried and would not recommend it on a production box, without testing it with setting up an exact condition on one of your test boxes.
makes me think that you somehow lost the distribution agent job on your subscriber while everything was fine at the publisher and distributor.
If that was the case then - was log reader agent still running on publisher during the time you lost distribution agent on the subscriber ??
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 2:16 pm
yes Amit, Logreader was running when distributor agent job was lost.
August 25, 2009 at 2:44 pm
http://www.replicationanswers.com/Articles.asp is good resource .... where you might find an answer to your situation ... some cool tricks there .....
I dont think you are left with much choice right now but to reinitialize the replication
this might work - test it out in your test environment by creating a similar situation !!! before you even try this
1. find a record on published table thats present in publisher, but not in subscriber (since the time you lost your distributer agent)
2. add the same subscription back to publication, and this time choose NO SYNC option - subscriber already ahs the data
3. See if log reader pushes the data to distribution agent (since the last time it pushed) and you get the records in step 1 back to subscription.
if this does not work - I wish i had the time to test it, and most likely it could fail, then you are not left with any choice than to reinitialize the replication with your downtime at publisher
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 2:55 pm
Amit,
Thank you very much for your quick response and very helpful information in this regard.
I think, I got answer of almost all of my doubts. Just some testings are required to satisfy myself
Regards.
August 25, 2009 at 3:24 pm
sinhaashish2001 (8/25/2009)
We can do it in SQL Server 2005 and 2008 using Initializing a Transactional Subscription with a backup.
I am not sure what you meant there ....
did you mean restore publication backup and transaction log backup to subscription and then initialize replication with nosync ???
Please enlighten me !!
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply