February 6, 2008 at 11:15 pm
Comments posted to this topic are about the item Init From Backup
February 6, 2008 at 11:30 pm
Great article, thanks!
One comment, though, which can be useful:
You mentioned the problem when you want to manually init the subscriber. So you have to stop all users, init and then get them online again.
But even in 2000, you could do it with much shorter downtime:
1.Stop users
2.Backup the database
3.Create publication and (!) subscription
4.Disable all replication agents
5.Now you can allow users to work again!
6.Whenever you can, slowly copy your backup over the WAN and restore it
7.Enable all replication agents
This way the replication will not try to copy any row until the destination is prepared. But it will store the changes in the log (you can let the log reader work so it will be stored in the distribution db instead, that way the log will be cleaned up).
February 7, 2008 at 3:33 am
your paper notes BULK INSERT (table-by-table) is an exercise in slow ...
one can create/amend a replication pub/sub via
1. the replication wizard from SSMS
2. subsequently modify the config using SSMS property editor [as you do in your paper]
3. use SSMS to script out the pub+sub into a .SQL file (drop then append create)
- and edit [lots] to make it readable, have @variables instead of hardwired strings etc
where #3 is what I suspect most DBA's will do for good practices (DR, HA etc).
Within the .sql script you will find various sprocs like sp_addpublication (all documented fully in BOL) which give you even more fine control to those sprocs, e.g. by params
@sync_method
@post_snapshot_script
@enabled_for_p2p
@compress_snapshot
which are not [fully] exposed by #1 or #2 development stages.
I have not attempted the compress feature [BOL extract below], but if you had a few large tables (and didn't justify the .BAK approach in the paper) it would be worth investigating
- might be an exercise in lots faster !
[ @compress_snapshot= ] 'compress_snapshot'
Specifies that the snapshot that is written to the @alt_snapshot_folder location is to be compressed into the Microsoft CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed; true specifies that the snapshot will be compressed. Snapshot files that are larger than 2 gigabytes (GB) cannot be compressed. Compressed snapshot files are uncompressed at the location where the Distribution Agent runs; pull subscriptions are typically used with compressed snapshots so that files are uncompressed at the Subscriber. The snapshot in the default folder cannot be compressed.
February 7, 2008 at 6:24 am
I am doing the same thing where I restore from a backup. After I do the restore and create the subscription everything is fine. Except that there have been changes made to the publisher database that don't seem to get to the subscriber that were done while setting up the subscriber. I manually inserted 3 rows to test this process and make sure that changes made after the backup and before the subscriber was built would get the new rows. In the replication monitor it showed 3 transactions with 3 commands but the data never made it to the subscriber. The steps I did was create the Publisher with initialize from backup set to true. I turned off the distribution cleanup job on the publisher server. Then I did a backup of the database, moved the file to the new server and did the restore. Then I created the subscription on the publisher server using sp_addsubscription on the publisher server using init from backup and the device and backup name. Then I went to the subscriber server and created the subscription. The rows never made it to the subscriber. Any transactions after that come across but not the 3 new rows. This publisher is a live database and I want to do the backup, do the restore and have replication pick up any changes in the time it takes to do the restore and get the subscriber built.
February 7, 2008 at 1:08 pm
Andy,
Thanks for your time posting this article. I totally love this method - but I have some concerns ...
Doesn't this method have issues if the database being replicated contains triggers and/or identity columns? I specify "NOT FOR REPLICATION" in the triggers I use, but when I use this method, the triggers still fire - because, presumably, the subscriber database is in fact a real full copy of the database - not the typical modified subscriber database.
Will I need to disable all the triggers? Plus, there is no easy way I know of to disable identity columns, without dropping the column and re-adding it as a non-identity column.
Thanks.
- Sean
February 8, 2008 at 6:47 am
when you specify NFR, the associated objects will behave normally for all clients apart from the actual replication Distribution Agent itself. The DA first calls sp_MSinit_subscription_agent sproc to establish itself as that "special one" (actually writes its SPID into MSsubscription_agents system table). The database engine recognises that SPID and skips the normal IDENTITY processing if NFR attribute has been set.
There may be multiple DA's (from various different pubs) pushing into the same table [one hopes non-overlapping IDENTITY ranges!]. So you might like to verify this against sp_who2 etc
select * from MSreplication_subscriptions
select * from MSsubscription_agents
Inevitably the pub db has extra tables such as syspublications,syssubscriptions as a result of
exec sp_replicationdboption @optname=N'publish', @value=N'true'
and these tables will inevitably be embedded in the .BAK db-dump file
the target SQL200x instance may not have replication turned on so may lack lotsa sprocs that understand such tables (and clauses like KEEP_REPLICATION), so you may get odd errors on restore and have to online and make multiuser.
It is sometimes appropriate to enable repl on such targets even if you have no intention that they be Pub/Dist) but just so the sprocs get populated, and you get an easier restore ride.
experiments show that you can flush these unwanted system tables by flipping value
exec sp_replicationdboption @optname=N'publish', @value=N'true'
exec sp_replicationdboption @optname=N'publish', @value=N'false'
so I request that you try
1. BACKUP DATABASE -- at pub (c/w "NFR" and system tables)
2. RESTORE DATABASE -- at sub
3. exec sp_replicationdboption @optname=N'publish', @value=N'true'
4. exec sp_replicationdboption @optname=N'publish', @value=N'false'
5. now add the sub to the existing pub
6. check behaviour of INSERTs at pub and ditto at sub
and post results back to this forum
HTH
February 8, 2008 at 8:06 am
Well, it turns out that I made a mistake. NOT FOR REPLICATION was missing fro my trigger. I apologize for the error.
After properly adding it back in, the Init From Backup method succeeded and worked properly - including the trigger and identity columns - provided they had 'NOT FOR REPLICATION' in them.
I did not have to use the method above to flip the "Publish" option form true to false.
The reason this method intrigues me is because I have a client whose subscription cannot initialize. Neither we, nor Microsoft - after over a month of an open, unresolved case - was able to solve. This method may allow the initialization to succeed where the snapshots failed.
Thanks for your article, Andy and thanks dbaker (or HTH) for the follow-ups.
- Sean
February 14, 2008 at 5:20 pm
After setting up replication with this option, can we make schema changes on the publisher?
June 18, 2008 at 5:16 am
Yes you should be able to make schema changes, except changing the Primary Key.
When you run, sp_addsubscription, does it at this point start restoring from the backup? Can you restore from a transaction Log backup (having made the full backup restore manually?)
If tansactions where added to the publisher DB after the backup was taken, but before sp_addsubscription is run, are these replicated to the subscription?
August 8, 2008 at 7:46 am
Hello,
I followed instructions in this article to setup updateable replication and found identity ranges are same at subscriber. If I try to insert records on subscriber it results in following error.
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_HardwareWMI'. Cannot insert duplicate key in object 'dbo.HardwareWMI'.
The statement has been terminated.
It seems that (1) new identity range is not allocated on subscriber and (2) check constraint for identity column is not created on subscriber.
Is there any additional step necessary to setup Updateable subscriber using this method?
Thanks
October 3, 2008 at 2:35 pm
Initialize with backup option works fine but I have a question on functionality.
Once my Replication is working fine can I add new articles to publication?
If I add articles using sp_addarticle and mark for Reinitialization is set will the new articles snapshot is generated and deliver it to subscriber?
Pat...
October 6, 2008 at 2:22 am
Yes you are able to add new articles and snapshot them in the normal way.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply