Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Init From Backup Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2008 11:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:27 AM
Points: 6,800, Visits: 1,917
Comments posted to this topic are about the item Init From Backup

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #452566
Posted Wednesday, February 6, 2008 11:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 3:02 AM
Points: 49, Visits: 102
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).





Post #452568
Posted Thursday, February 7, 2008 3:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 28, 2009 2:09 AM
Points: 112, Visits: 196
your paper [I can't say "article"!] 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.



Post #452624
Posted Thursday, February 7, 2008 6:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:46 PM
Points: 24, Visits: 504
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.
Post #452689
Posted Thursday, February 7, 2008 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 13, Visits: 128
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
Post #452917
Posted Friday, February 8, 2008 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 28, 2009 2:09 AM
Points: 112, Visits: 196
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
Post #453198
Posted Friday, February 8, 2008 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 13, Visits: 128
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
Post #453260
Posted Thursday, February 14, 2008 5:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2009 10:30 AM
Points: 4, Visits: 41
After setting up replication with this option, can we make schema changes on the publisher?
Post #456068
Posted Wednesday, June 18, 2008 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 19, 2009 7:35 AM
Points: 4, Visits: 43
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?
Post #518881
Posted Friday, August 8, 2008 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:13 AM
Points: 10, Visits: 108
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
Post #549178
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse