Reinitialize the Merge Process Without Uploading Data First.

A workload direction is considered as a disquisitional aspect of SQL Server transactional replication. Replication is the oldest of the loftier availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature applied science, SQL Server transactional replication is also very robust and, in most cases, very straightforward to fix up and manage.

In the previous article SQL Replication: Basic setup and configuration, I've discussed a lot virtually setting up a SQL Server transactional replication solution and synchronizing the subscription with a snapshot. As we all know, by default, subscriptions in a SQL Server transactional replication publication are reinitialized with a snapshot, which is generated by the Snapshot Amanuensis and applied by the Distribution Agent. In virtually of the scenarios, such every bit those working with very large database and big initial datasets, information technology is preferable to reinitialize a subscription using some other method.

Other methods of initializing a Subscriber include:

1. Specifying a fill-in
2. Copying the initial dataset

Specifying a backup method:

This is a very uncomplicated method. First, restore the backup on the Subscriber, and then setup a subscription using T-SQL. Subsequently the setup, the Distribution Amanuensis copies the required SQL Server transactional replication metadata and system procedures that are required for data propagation. Information technology considered as an efficient way to reinitialize a subscriber and information technology is the fastest fashion to evangelize data to the subscriber. The virtually recent backup can be used if it was taken after the publication was enabled for initialization with a backup.

In this article, we'll discuss more about "Specifying a backup" method

Copying an initial dataset:

In this method, the schema-and-information are copied to the Subscriber through any of the known schema-and-data re-create method. And then setup a subscription with "replication support only" selection. The Distribution Amanuensis copies any required metadata and system procedures. At the Publisher on the publication database, execute sp_addsubscription. Specify the proper noun of the database at the Subscriber containing the published data for @destination_db, a value of push button for @subscription_type, and a value of "replication support only" for @sync_type.

It will be discussing more well-nigh this method in my adjacent article.

SQL Server transactional replication diagram:

Problem statement

As nosotros all know, by default, subscriptions in a transactional publication are reinitialized with a snapshot, which is generated by the Snapshot Amanuensis and applied by the Distribution Amanuensis. A snapshot is a betoken-in-time motion picture of the related objects (articles) of the publication. Past default, in SQL Server transactional replication, the information volition be loaded via BCP or Bulk Insert. Permit us assume a scenario where it is required to publish manufactures of VLDB (Very Large Database) to Subscribers. By default, for the initial synchronization, requires a snapshot generation. In some cases, space is a large constraint and also it will lock the articles. Information technology will create concurrency issues every bit well. For mission-critical database this is not practical as it can take a very long fourth dimension to create an initial snapshot and reinitialize the Subscribers from that generated snapshot.

Getting Started

To set upward SQL Server transactional replication, y'all must configure the Distributor and create a Publication and a Subscription using backup selection

To reinitialize a subscription with a fill-in, yous first must enable "Initialize with fill-in" option when you lot create a publication, and then specify values for @sync_type, @backupdevicetype, and @backupdevicename options when yous create a subscription. Publication option tin can exist enabled through the New Publication Wizard using SSMS or programmatically using T-SQL. Notwithstanding, the values required for the setting up a subscription option can just be specified programmatically using T-SQL.

Let us get into the details of the setup:

1. To configure the SQL Server transactional replication distributor, refer to the Configure distributor section of the commodity How to setup a bones SQL Server Transactional Replication
2. To configure the SQL Server transactional replication publisher, refer to the Configure Publisher section of the article How to setup a basic SQL Server Transactional Replication. In the viii^th step, On the Snapshot Agent page, the options "Create a snapshot immediately and continue the snapshot available to reinitialize subscriptions" and
   "Schedule the Snapshot Agent to run the following times" are left bare and Click Next.

   

3. The other steps remain the aforementioned

   

4. Y'all could detect that at that place is no snapshot step in the Creating Publication page.

   

5. Later on creating the publication, you need to set the 'Allow_Initialize_From_Backup' parameter to true at the publisher. You tin either do this using T-SQL or SMSS.

   Apply [ AdventureWorks2016 ] GO DECLARE @ publication As sysname Ready @ publication = N 'AdventureWorks2016_Publisher_BKP' EXEC sp_changepublication @ publication = @ publication , @ holding = Due north 'allow_reinitialize_from_backup' , @ value = 'true' Go

   

   OR

   Scan to the Publication Properties and select the Subscription Options, set "Allow initialization from fill-in files" to "true" from the drop-downwardly list and Click Ok to salvage the change.

   

6. Create a backup of the SQL Server transactional replication publication database using the Backup command. Let's initiate a full backup or T-log backup if y'all've already initiated a full backup.

   BACKUP DATABASE AdventureWorks2016 TO Disk = 'f:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH FORMAT

   

7. Next, restore the database backup on the SQL Server transactional replication Subscriber using the RESTORE database control

   Apply [ master ] RESTORE DATABASE [ AdventureWorks2016 ] FROM Disk = N 'F:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH FILE = ane , MOVE Due north 'AdventureWorks2016_Data' TO N 'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\AdventureWorks2016_Data.mdf' , MOVE N 'AdventureWorks2016_Log' TO N 'g:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\AdventureWorks2016_Log.ldf' , NOUNLOAD , Supersede , STATS = 5

8. On the publication database, execute the sp_addsubscription system stored procedure. Specify the following parameter

   • @sync_type – "reinitialize with backup"
   • @backupdevicetype – the type of the backup device. In this case information technology's Deejay
   • @backupdevicename – Concrete or logical fill-in device proper noun. In this case, its physical, so blazon in the full path of the fill-in file. If the nigh recent backup is transactional log then specify the transaction log file full path.

   • This script uses sqlcmd scripting variables

   :setvar publicationDB N'AdventureWorks2014′;
   :setvar publication N'AdventureWorks2016_Publisher_BKP';
   :setvar job_login N'Domain\ID001′;
   :setvar job_password N'thanVitha@2016′;
   :setvar subscriber N'hqdbt01\SQL2017′
   :setvar subscriptionDB Northward'AdventureWorks2016_REPL_Rpt'

   DECLARE @ publication Equally sysname ; DECLARE @ subscriber As sysname ; DECLARE @ subscriptionDB Every bit sysname ; SET @ publication = $ ( publication ) ; Fix @ subscriber = $ ( subscriber ) ; SET @ subscriptionDB = $ ( subscriptionDB ) ;

   --Add a button subscription to a transactional publication. USE [ AdventureWorks2014 ] EXEC sp_addsubscription @ publication = @ publication , @ subscriber = @ subscriber , @ destination_db = @ subscriptionDB , @ sync_type = N 'reinitialize with backup' , @ backupdevicetype = 'Disk' , @ backupdevicename = 'F:\PowerSQL\AdventureWorks2016PublisherDB.bak' , @ subscription_type = N 'button' , @ update_mode = North 'read merely' ;

9. The easiest way to launch the SQL Replication Monitor is from SSMS, equally described in the following steps:

   • Using SSMS, connect to the replication SQL Server instance
   • In Object Explorer, locate the Replication folder, correct-click the folder, and so click Launch Replication Monitor

   • You can see that the performance is in an excellent state

     

   • If you select the Agents tab, you can see that there is no Snapshot agent created in the entire procedure.

   

Summary

So far, we discussed the details to reinitialize a SQL Server transaction replication subscription to a publication from a backup, enable the publication to let initialization from a backup, and then specify backup information when creating the subscription.

This method has a big reward in terms of bypassing the snapshot generation stride and a bulk copy of data over a network but the drawback is that, we have to restore the full database on the subscriber. In this case, you lot need to perform a deletion of those objects that are not necessary for SQL Server transactional replication.

In the side by side commodity, we will see how to build a SQL Server transactional replication solution using the "Schema and data re-create only method".

That's all for now… Happy replicating!

Table of contents

SQL Server replication: Overview of components and topography

SQL Replication: Bones setup and configuration

How to Add together/Drop articles from existing publications in SQL Server

How to do a quick estimated compare of information in two large SQL Server databases to see if they are equal

SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database fill-in

How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases

How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases

How to set upward a DDL and DML SQL Server database transactional replication solution

How to setup cantankerous-platform transactional SQL Server replication for database reporting on Linux

SQL Server database migrations with nix data loss and nada reanimation

Using transactional information replication to replay and test production loads on a staging server

How to setup SQL Server database replication for a reporting server

SQL Server transactional replication: How to reinitialize a subscription using a "Replication support only" –TBA

SQL Server Replication Monitoring and setting alerts using PowerShell –TBA

• Author
• Recent Posts

I'm a Database technologist having eleven+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Reckoner Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

guerrastratersest.blogspot.com

Source: https://www.sqlshack.com/sql-server-transactional-replication-how-to-reinitialize-a-subscription-using-a-sql-server-database-backup/

Share this post