Configuring Snapshot Replication Using SQL Server Management Studio
Throughout this book, we will be using SQL Server Management Studio. You should already be familiar with this. Installation instructions are available in the preface, and all exercises can be completed on the free tier.
Configuring snapshot replication is a two-step process: the first step is to create the publication and the second step is to create the subscription. We will first create the publication.
Exercise 1: Creating a Publication
In this exercise, we will create a publication for our snapshot replication:
Open SQL Server Management Studio and connect to the Object Explorer (press F8 to open and connect to Object Explorer).
Find and expand the Replication node and right-click on the Local Publication node. In the context menu, select New Publication:
Figure 1.7: Select New Publication
In the New Publication Wizard introduction page, click Next to continue:
Figure 1.8: The New Publication Wizard window
The New Publication Wizard | Distributor page defines where the distribution database will be created. The first option specifies that the publisher server will act as the distribution server and will host the distribution database and distributor jobs.
The second option allows you to add a new server to act as the distribution server.
Leave the first option checked and click Next to continue:
Figure 1.9: The Distributor window
In the New Publication Wizard | Snapshot Folder window, specify the location where snapshots (schema:
.schfiles and data) are stored. This needs to be a network path, and both the distributor and the subscriber should have access to this path.Create a new folder in any location on your computer and share it with everyone by modifying the sharing settings of the new folder that's been created.
Copy the shared path in the Snapshot folder box, as shown in the following screenshot. Click Next to continue:
Figure 1.10: The Snapshot Folder window
In the New Publication Wizard | Publication Database window, choose the database you wish to publish or replicate. Click Next to continue:
Figure 1.11: The Publication Database window
In the New Publication Wizard | Publication Type window, select Snapshot publication and click Next to continue:
Figure 1.12: The Publication Type window
In the New Publication Wizard | Articles window, select the database objects to be replicated.
Expand Tables and select the required tables, as shown in the following screenshot:
Figure 1.13: The Articles window
Note
Make sure you don't select any temporal tables. Temporal tables aren't supported for replication at the time of writing this book. Temporal tables are the ones with the
_archivepostfix, for example,Customers_Archive.Do not select any other objects for now.
Select the
BuyingGroupstable and then click Article Properties. Then, select Set properties of Highlighted Table Article.The Article Properties window lists multiple article properties that you may have to change as and when required. For example, you can change the table name and owner at the subscriber database or you can copy the non-clustered
Columnstoreindex from the publisher to the subscriber. This property is disabled by default:
Figure 1.14: The Articles Properties window
The New Publication Wizard | Filter Table Rows window allows you to filter out data to be replicated to the subscriber:
Figure 1.15: The Filter Table Rows window
In the Filter Table Rows window, click on the Add button to add filters. In the Add Filter window, add the filter, as shown in the following screenshot:
Figure 1.16: The Add Filter window
The shown filter will filter out any order with an order year of less than 2016 and will replicate all orders made after the year 2016.
Click OK to go back to the Filter Table Rows window:
Figure 1.17: The Filter Table Rows window
Observe that the filter has been added.
You can't add filters by joining one or more tables. The filter only works on a single table. It is therefore advised that you add the filter to other tables as well. Otherwise, all tables other than the
Orderstable will have data for all the years. This example, however, applies the filter on theOrderstable only.Click Next to continue.
In the New Publication Wizard | Snapshot Agent window, check the Create a snapshot immediately and keep the snapshot available to initialize subscriptions option.
This will generate the snapshot in the snapshot folder that's specified:
Figure 1.18: The Snapshot Agent window
Click Next to continue.
Note
It is recommended that you schedule the snapshot agent during off business hours so as to avoid performance degradation that occurs as a result of snapshot generation.
In the New Publication Wizard | Agent Security window, specify the service account under which the snapshot agent process will run and how it connects to the publisher:
Figure 1.19: The Agent Security window
Click on Security Settings to continue.
In the Snapshot Agent Security window, choose options, as shown in the following screenshot:
Figure 1.20: The Snapshot Agent Security window
Note
Running the snapshot agent process under a SQL agent service account isn't a good practice on production environments as a SQL agent service account has more privileges than required by the snapshot agent. However, we are only using it for demonstrative purposes.
The minimum permissions required by the Windows account under which the snapshot agent process runs are
db_ownerrights on the distribution database,db_ownerrights on the publisher database, and read, write, and modify rights on the shared snapshot folder.Click OK to continue. You'll be redirected to the Agent Security window. In this window, click Next to continue:
Figure 1.21: The Agent Security window with account selected
In the Wizard Actions window, select Create the publication, and then click Next to continue:
Figure 1.22: The Agent Security window with account selected
In the Complete the Wizard window, provide the publication name, as shown in the following screenshot, and then click on Finish to complete the wizard and create the publication:
Figure 1.23: The Complete the Wizard window
The wizard creates the publication, adds the selected articles, and creates and starts the snapshot agent job:
Figure 1.24: The Creating Publication window
After the publication is created, click on Close to exit the wizard.
Now, let's look at the objects or components that are created as part of creating the publication.
Exercise 2: Exploring the Distribution Database
In step 4 of the previous exercise, we specified that the publisher will act as its own distributor. This results in the creation of a distribution database and snapshot agent job on the publisher itself. We can also use a different instance for distribution, however, let's keep it simple for this demonstration.
In SQL Server Management Studio, connect to the Object Explorer, expand Databases, and then expand System Database. Observe that a new system database distribution has been added as a result of the previous exercise:
Figure 1.25: System tables
The distribution database has system tables that keep track of replication metadata. Let's explore the metadata tables that are related to snapshot replication.
Open a new query window in SSMS and execute the following queries one by one:
To get publication information, run this query:
SELECT [publisher_id] ,[publisher_db] ,[publication] ,[publication_id] ,[publication_type] ,[thirdparty_flag] ,[independent_agent] ,[immediate_sync] ,[allow_push] ,[allow_pull] ,[allow_anonymous] ,[description] ,[vendor_name] ,[retention] ,[sync_method] ,[allow_subscription_copy] ,[thirdparty_options] ,[allow_queued_tran] ,[options] ,[retention_period_unit] ,[allow_initialize_from_backup] ,[min_autonosync_lsn] FROM [distribution].[dbo].[MSpublications]You should see the following output:
Figure 1.26: Publication information
You can get the publication details from Object Explorer as well:
Figure 1.27: Publication details from the Object Explorer
To get the article information, run this query:
SELECT [publisher_id] ,[publisher_db] ,[publication_id] ,[article] ,[article_id] ,[destination_object] ,[source_owner] ,[source_object] ,[description] ,[destination_owner] FROM [distribution].[dbo].[MSarticles]You should get the following output:
Figure 1.28: Article information
To get the snapshot agent information, run this query:
SELECT [id] ,[name] ,[publisher_id] ,[publisher_db] ,[publication] ,[publication_type] ,[local_job] ,[job_id] ,[profile_id] ,[dynamic_filter_login] ,[dynamic_filter_hostname] ,[publisher_security_mode] ,[publisher_login] ,[publisher_password] ,[job_step_uid] FROM [distribution].[dbo].[MSsnapshot_agents]You should get the following output:
Figure 1.29: Snapshot agent information
Note
The preceding output is also useful in identifying which snapshot agent job belongs to which publication when there are multiple snapshot agent jobs configured on a SQL Server instance.
To get the snapshot agent's execution history, run this query:
SELECT [agent_id] ,[runstatus] ,[start_time] ,[time] ,[duration] ,[comments] ,[delivered_transactions] ,[delivered_commands] ,[delivery_rate] ,[error_id] ,[timestamp] FROM [distribution].[dbo].[MSsnapshot_history]You should get the following output:
Figure 1.30: Snapshot agent execution history
You can also get the snapshot agent and its history from the Object Explorer under the SQL Server Agent node:
Figure 1.31: Snapshot agent history from the object explorer
Right-click on the snapshot agent job and select View History from the context menu.
Database Snapshot
Navigate to the snapshot folder (WideWorldImporters-Snapshot) that was provided in step 5 of the previous exercise. This contains the snapshot files for the articles that were selected for replication.
Observe that this folder acts as a base and has a subfolder named WIN2012R2$SQL2016_WIDEWORLDIMPORTERS_WWI-SNAPSHOT. The subfolder is named by concatenating the SQL Server instance name and the publication name to the base snapshot folder. This is done to separate out snapshots for different publications; the base snapshot folder can therefore have snapshots from multiple publications.
Every time a snapshot agent is run, a new folder is created inside WIN2012R2$SQL2016_WIDEWORLDIMPORTERS_WWI-SNAPSHOT. This is named by the timestamp when the snapshot agent ran and contains the schema and data files. This is shown in the following screenshot:
Figure 1.32: Snapshot folder
Observe that the snapshot folder has .sch, .idx, .pre, and .bcp files. Let's see what these files are used for.
.pre Files
The .pre files are the pre-snapshot scripts that drop the object at the subscriber if it exists. This is because every time a snapshot runs, it initializes the tables from scratch. Therefore, the objects are first dropped at the subscriber. Let's look at an example:
SET QUOTED_IDENTIFIER ON
go
if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec sys.sp_MSdropfkreferencingarticle @destination_object_name = N'CustomerTransactions', @destination_owner_name = N'Sales'
go
drop Table [Sales].[CustomerTransactions]
goThe preceding query is from the customertransactions.pre file. It first drops foreign keys, if any, for the CustomerTransactions table and then drops the CustomerTransactions table.
Note
Another way to do this is to set the Action if name is in use option in the Article Properties window to the value Drop existing object and create a new one.
.sch Files
The .sch files contain the creation script for the articles to be replicated. Let's look at an example:
drop Table [Sales].[CustomerTransactions] go SET ANSI_PADDING ON go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Sales].[CustomerTransactions]( [CustomerTransactionID] [int] NOT NULL, [CustomerID] [int] NOT NULL, [TransactionTypeID] [int] NOT NULL, [InvoiceID] [int] NULL, [PaymentMethodID] [int] NULL, [TransactionDate] [date] NOT NULL, [AmountExcludingTax] [decimal](18, 2) NOT NULL, [TaxAmount] [decimal](18, 2) NOT NULL, [TransactionAmount] [decimal](18, 2) NOT NULL, [OutstandingBalance] [decimal](18, 2) NOT NULL, [FinalizationDate] [date] NULL, [IsFinalized] AS (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) PERSISTED, [LastEditedBy] [int] NOT NULL, [LastEditedWhen] [datetime2](7) NOT NULL ) GO
This query is from the customertransactions.sch file.
.idx Files
The .idx files contain the indexes and constraints on the tables to be created at the subscriber. Let's look at an example:
CREATE CLUSTERED INDEX [CX_Sales_CustomerTransactions] ON [Sales].[CustomerTransactions]([TransactionDate]) go ALTER TABLE [Sales].[CustomerTransactions] ADD CONSTRAINT [PK_Sales_CustomerTransactions] PRIMARY KEY NONCLUSTERED ([CustomerTransactionID]) Go
This query is from the customertransactions.idx file.
.bcp Files
The .bcp files contain the table data to be inserted into the tables at the subscriber. There can be multiple .bcp files, depending on the table size.
Snapshot Agent Job
The snapshot agent job is a SQL Server agent job that executes snapshot.exe to generate the snapshot (these are the files we discussed earlier: .sch, .pre, .idx, and .bcp).
The snapshot agent job is created as part of creating the snapshot publication. You can locate the job in Object Explorer under the SQL Server Agent | Jobs node:
Figure 1.33: Snapshot agent job location
Double-click on the job to open it.
Note
The job name may be different in your case.
In the Job Properties window, click on Steps on the left-hand side in the Select a page pane:
Figure 1.34: The Job Properties window
This job has three steps.
Step 1 - Snapshot Agent startup message: This inserts the Starting Agent message in the
Msnapshot_historytable in thedistributiondatabase:
Figure 1.35: The Snapshot Agent startup message window
It uses the system-stored
sp_MSadd_snapshot_historyprocedure to insert a row in theMsnapshot_historytable, indicating the start of the snapshot agent.Step 2 - Run agent: This runs the
snapshot.execommand with the required parameters to generate the snapshot:
Figure 1.36: The Run agent window
Observe that the Type parameter shows Replication Snapshot (this points to
snapshot.exe). The Command text is the list of parameters that are passed to thesnapshot.exeprocess. Similar to the previous step, where the Starting Agent status is written to theMSsnapshot_historytable,snapshot.exealso logs the progress to theMSsnapshot_historytable.Step 3 - Detect nonlogged agent shutdown: This step uses the systems-stored
sp_MSdetect_nonlogged_shutdownprocedure to check if the agent is shut down without logging any message to theMSsnapshot_historytable. It then checks and logs a relevant message for the agent that was shut down to theMSsnapshot_historytable:
Figure 1.37: The Detect nonlogged agent shutdown window
This completes the objects and components that were created as part of creating a publication.
We'll now look into the snapshot.exe process in detail.
Replication Snapshot (snapshot.exe)
The snapshot.exe process is installed with SQL Server (if you choose to install replication when installing SQL Server) and is stored in the C:\Program Files\Microsoft SQL Server\130\COM folder (for SQL Server 2016 installation).
The snapshot.exe process accepts quite a few parameters that are used to generate snapshots and to tune snapshot generation.
Note
This book covers the most important parameters, and not all parameters. For a complete list of parameters, go to https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-snapshot-agent?view=sql-server-2017.
The snapshot.exe process can be run independently from the command line by passing the relevant parameters.
Execute the following commands in a command-line window to run snapshot.exe with the default parameters, as specified in step 2 of the snapshot agent job that we discussed previously:
REM -- Change the variable values as per your environment SET Publisher=WIN2012R2\SQL2016 SET PublisherDB=WideWorldImporters SET Publication=WWI-Snapshot "C:\Program Files\Microsoft SQL Server\130\COM\SNAPSHOT.EXE" -Publisher %Publisher% -PublisherDB %PublisherDB% -Distributor %Publisher% -Publication %Publication% -DistributorSecurityMode 1
Note
You can also copy the code from the C:\Code\Lesson01\snapshot.txt file.
You will have to change the Publisher variable with the SQL Server instance you created in the snapshot publication. You may have to change the PublisherDB and Publication parameters if you chose a different database and publication name when creating the snapshot publication in Exercise 1: Creating a Publication.
Once it runs successfully, it generates the following output:
Figure 1.38: Running snapshot.exe
The snapshot agent also logs the progress status in the distribution.dbo.MSsnapshot_history table. You can query the table to verify these steps.
An important thing to note here is that the snapshot agent locks the published tables (schema lock and exclusive locks) while generating the snapshot. This is to make sure that no changes are made to the schema when a snapshot is being generated. Any changes to the schema during snapshot generation will leave the snapshot in an inconsistent state and it will error out when applying the snapshot to the subscriber.
For example, when the snapshot agent generated a .sch file, a table had two columns; however, when it exports the data for that table, another transaction modifies the table by adding one more column to it. Therefore, the table creation script, .sch, has two columns and .bcp, which is the data file, has three columns. This will error out when applied to the subscriber.
This also means that for large databases, generating snapshots may result in blocking issues.
To review the locks being applied during snapshot generation, run the snapshot.exe process, as described earlier. Then, quickly switch to SQL Server Management Studio and execute the following query to get lock details:
select resource_type, db_name(resource_database_id) As resource_database_name, resource_description, request_mode, request_type, request_status, request_session_id from sys.dm_tran_locks
You should get an output similar to what is shown in the following screenshot:
Figure 1.39: Reviewing the applied locks
Observe that the IX, X, and Sch-S locks are applied on the WideWorldImporters database during snapshot generation.
Note
The relevant snapshot.exe switches/parameters will be discussed in the Optimizing Snapshot Replication section.
Modifying an Existing Publication
This section explores existing publication properties and how to modify an existing publication so that you can add or remove articles, change agent security settings, and more.
To view publication properties, connect to Object Explorer, expand Replication, and then expand Local Publications.
Right-click on the snapshot (WWI-Snapshot) and select Properties from the context menu:
Figure 1.40: Selecting Properties from the context menu
This opens the Publication Properties window:
Figure 1.41: The Publication Properties | General window
The Publication Properties window has different options on the left-hand side (Select a page) pane to modify or change publication properties. We'll now look at these options in detail.
Articles
The Articles page allows you to add or remove an article to or from the publication. Once you add or remove one, you'll have to generate a snapshot for the changes to take effect:
Figure 1.42: The Articles window
Adding or removing an article is very simple. Uncheck the Show only checked articles in the list option to display all database objects.
Note
When adding a new article, the article properties can be changed if required using the Article Properties dropdown, as shown earlier. The article properties can, however, be changed later if required.
Then, select or remove the objects as required and click on OK to apply the changes.
Filter Rows
The Filter Rows page allows you to add or remove row filters to the tables. This is similar to step 9 of Exercise 1: Creating a Publication:
Figure 1.43: The Filter Rows window
Snapshot
The Snapshot page allows you to do the following:
Modify the snapshot format to Native SQL Server or Character.
Modify the location of the snapshot folder.
Compress the snapshot files in the snapshot folder. This saves storage for large publications. When compression is specified, a single cabinet (compressed) file is generated. However, if an article file size exceeds 2 GB, cab compression can't be used.
Run additional scripts before and after applying the snapshot at the subscriber:
Figure 1.44: The Snapshot window
FTP Snapshot
The FTP Snapshot page allows subscribers to download snapshots using FTP. You'll have to specify the FTP folder as the snapshot folder:
Figure 1.45: The FTP Snapshot window
You'll have to set up the FTP by specifying the connection details so that the snapshot agent can then push and pull snapshot files to and from the specified FTP folder.
Subscription Options
The Subscription Options page lets you control subscriber-level settings:
Figure 1.46: The Subscription Options window
The important settings for snapshot replication are as follows:
Allow pull subscriptions: This determines whether or not to allow pull subscriptions. The default value is True. Pull subscription is the only option available when an FTP snapshot is used.
Allow non-SQL Server Subscribers: This determines whether or not to allow non-SQL Server, that is, Oracle, MySQL, and so on, subscribers. The default value is False.
Replication schema changes: This determines whether or not to replicate schema changes to the published articles. The default value is True.
Publication Access List
The Publication Access List (PAL) page is a list of logins that have permission to create and synchronize subscriptions. Any login that has access to the publication database (WideWorldImporter) and is defined in both the publisher and distributor can be added to PAL:
Figure 1.47: The Publication Access List window
Note
Do not remove the distributor_admin login as it's used by replication.
Agent Security
In the Agent Security page, you can modify the agent security settings, as defined in step 11 of Exercise 1: Creating a Publication:
Figure 1.48: The Agent Security window
To change or modify security settings, click on the Security Settings button and change as required.
Exercise 3: Creating a Subscription
In this exercise, we'll create the subscription at the subscriber end. A subscriber is usually a separate SQL Server instance.
To create a new subscription for the publication, follow these steps:
Connect to Object Explorer in SSMS. Expand Replication | Local Publications.
Right-click on the [WideWorldImporters]: WWI-Snapshot publication and select New Subscriptions:
Figure 1.49: The New Subscriptions option in the Object Explorer
This will open the New Subscription Wizard. Click Next to continue:
Figure 1.50: The New Subscriptions Wizard window
In the Publication window, under the Publisher dropdown, select the publisher server and then the publication:
Figure 1.51: The Publication window
Click on Next to continue.
In the Distribution Agent Location window, select pull subscriptions:
Figure 1.52: The Distribution Agent Location window
Pull subscriptions will create the distribution job at the subscriber server. This option reduces overhead at the distributor, and this becomes more important when the publisher acts as the distributor.
Note
We are using pull subscription as this will cause jobs to be created at the subscriber, making it easy to understand different jobs.
Moreover, if you have a lot of published databases and the publisher is being used as a distributor, it's advised to use pull subscription so as to offload jobs from the publisher, that is, the distributor, to subscribers.
Click Next to continue.
In the Subscribers window, click on Add SQL Server Subscriber to connect to the subscriber server. This is the server where the data will be replicated:
Figure 1.53: The Subscribers window
In the Connect to Server window, enter the subscriber server name and credentials. Click on Connect to continue:
Figure 1.54: The Connect to Server window
You'll be taken back to the Subscribers window:
Figure 1.55: The Subscribers window after connecting to the subscriber server
Under Subscription Database, select WideWorldImporters. Although you can replicate data to a different database name, it's advised that you use the same database name.
If the database doesn't exist at the subscriber, create one.
Click Next to continue.
In the Distribution Agent Security window, you can specify the distribution agent process account and how the distribution agent connects to the distributor and the subscriber. Select the button with three dots:
Figure 1.56: The Distribution Agent Security window
This opens the following window:
Figure 1.57: The Distribution Agent Security properties window
Select Run under the SQL Server Agent service account as the distribution agent process account.
Select By impersonating the process account under Connect to the Distributor and Connect to the Subscriber.
The By impersonating the process account option uses the SQL Server service account to connect to the distributor. However, as discussed earlier, this should not be done in production as the process account has the maximum set of privileges on the SQL Server instance.
The minimum set of permissions required by a Windows account under which the distribution agent runs are as follows.
The account that connects to the distributor should have the following permissions: be a part of the
db_ownerfixed database role on the distribution database, be a member of the publication access list, have read permission on the shared snapshot folder, and have write permission on theC:\Program Files\Microsoft SQL Server\InstanceID\COMfolder for replication LOB data.The account that connects to the subscriber should be a member of the
db_ownerfixed database role on the subscriber database.Click OK to continue.
You'll be taken back to the Distribution Agent Security window:
Figure 1.58: The Distribution Agent Security window after applying the settings
Click Next to continue.
In the Synchronization Schedule window, select Run on demand only under Agent Schedule.
The synchronization schedule specifies the schedule at which the distribution agent will run to replicate the changes:
Figure 1.59: The Synchronization Schedule window
Click Next to continue.
In the Initialize Subscriptions window, check the Initialize box and then select At first synchronization under the Initialize When option:
Figure 1.60: The Initialize Subscriptions window
The initialize process applies the snapshot to the subscriber to bring it in sync with the publisher. If the Initialize checkbox is left unchecked, the subscription isn't initialized. If it's checked, then there are two options available: Immediately and At first synchronization. The Immediately option will start the initialization as soon as the wizard is complete, and the At first synchronization option will start the initialization when the distribution agent runs for the first time.
In the Wizard Actions window, check the Create the subscription(s) option and click on Next to continue:
Figure 1.61: The Wizard Actions window
In the Complete the Wizard window, review the subscription settings and click on Finish to create the subscription:
Figure 1.62: The Complete the Wizard window
This ends the wizard. The wizard creates the subscription and the distributor agent job. At this point, the snapshot is not yet applied to the subscriber as we chose to run the distribution agent on demand.
Now, let's look at the objects that were created as part of creating the subscription.
Exercise 4: Exploring the Distribution Database (Metadata)
Let's look at the changes that are made to the distribution tables after creating the subscription.
Open SSMS and connect to the distribution database on the publisher server. You can execute the following queries to find out how the subscription details are stored:
Subscriber information is stored in the
distribution.dbo.subscriber_infotable. Execute the following query to return the subscriber details:SELECT [publisher] ,[subscriber] ,[type] ,[login] ,[description] ,[security_mode] FROM [distribution].[dbo].[MSsubscriber_info]You should get a similar output to the following:
Figure 1.63: Subscriber information
The publisher and subscriber will be different in your case.
Note
The
MSsubscriber_infotable will be deprecated in a future version of SQL Server.Execute the following query to get all of the information about the articles being replicated:
SELECT [publisher_database_id] ,[publisher_id] ,[publisher_db] ,[publication_id] ,[article_id] ,[subscriber_id] ,[subscriber_db] ,[subscription_type] ,[sync_type] ,[status] ,[subscription_seqno] ,[snapshot_seqno_flag] ,[independent_agent] ,[subscription_time] ,[loopback_detection] ,[agent_id] ,[update_mode] ,[publisher_seqno] ,[ss_cplt_seqno] ,[nosync_type] FROM [distribution].[dbo].[MSsubscriptions]You should get a similar output to the following:
Figure 1.64: Replicated articles
Execute the following query to get the distribution run history:
SELECT [agent_id] ,[runstatus] ,[start_time] ,[time] ,[duration] ,[comments] ,[xact_seqno] ,[current_delivery_rate] ,[current_delivery_latency] ,[delivered_transactions] ,[delivered_commands] ,[average_commands] ,[delivery_rate] ,[delivery_latency] ,[total_delivered_commands] ,[error_id] ,[updateable_row] ,[timestamp] FROM [distribution].[dbo].[MSdistribution_history]You should get a similar output to the following:
Figure 1.65: Distribution run history
Observe that the duration is 0 as the agent hasn't run until now.
Distribution Agent Job
The distribution agent job that was created at the subscriber (pull subscription) runs the distribution.exe process to apply the snapshot that was created by the snapshot agent on the subscriber database.
To view the job, you can open SSMS and connect to the subscriber server using Object Explorer. Then, you can expand the SQL Server Agent node.
You should see a job similar to what is shown in the following screenshot:
Figure 1.66: The distribution agent job
Note
The job name includes the publisher server, published database, and the publication name. This helps in identifying the publication the job is for. The job name also includes the subscriber server and database, as well as the job ID. This helps in identifying the publisher and subscriber when the job is on the distributor server and multiple replications have been configured.
You can double-click on the job to open its Job Properties window:
Figure 1.67: The Job Properties window
The Job Properties window lists out the general information about the job. You can select the Steps page from the Select a page pane on the left-hand side of the Job Properties window:
Figure 1.68: The Steps page
This job has only one step. You can double-click on the step name or select Edit at the bottom of the window to check the step details:
Figure 1.69: The Run agent window
Observe that the Run agent step calls the distributor.exe executable with a set of parameters.
Note
The parameters are self-explanatory. It's advised to go through the parameters and understand what the distributor agent is doing.
The job wasn't run until now as we opted for Run on Demand when configuring the subscription. Close the Job Properties window.
Exercise 5: Running the Job
Now, let's run the job and replicate the data from the publisher to the subscriber. To run the job manually, navigate to the job on the Object Explorer, as mentioned earlier:
Right-click on the job and select Start Job at Step:
Figure 1.70: The Start Job at Step option
Once the job is successful, you'll get a Success message on the Start Jobs window, as shown in the following screenshot:
Figure 1.71: The Start Jobs window
Click Close to close the Start Jobs window.
Right-click on the job under the SQL Server Agent | Jobs node and select View History from the context menu:
Figure 1.72: The View History option
This opens the job's history:
Figure 1.73: The View History window
Observe that the agent has completed successfully. An important thing to observe is the values for the
distribution.exeparameters (highlighted in red). We'll discuss these later in this lesson.The job history is also a good place to start troubleshooting snapshot replication. Errors, if any, show up in the job history, and we can then fix these issues based on the errors.
Note
You can also start/stop snapshot view history by right-clicking on Replication | Local Publications | Publication name (on publisher server) and selecting View Snapshot Agent Status.
Similarly, to start/stop the distribution agent and to view history, right-click on Replication | Local Subscription | Subscription Name and then select View Synchronization Status from the context menu.
In the Object Explorer, navigate to the Databases node and expand the WideWorldImporters database on the
subscriberserver.Observe that it now has the replicated tables:
Figure 1.74: The replicated tables
Note
You can query the MSdistribution_history table to review the distribution run status.
Exercise 6: Distribution Agent Process (distrib.exe)
distrib.exe is the executable that does the actual work of replicating the data from the publisher to the subscriber:
Note
This section discusses the parameters that are relevant for snapshot replication. For a complete list of parameters, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-2017.
First, let's run the distribution agent from the command line (similar to how we ran
snapshot.exe) and get familiar with the common parameters. As we have already applied the snapshot, let's modify theorderstable and generate a new snapshot.Execute the following query to update the
orderstable on thepublisherdatabase:UPDATE [Sales].Orders SET ExpectedDeliveryDate = '2017-12-10' WHERE Customerid = 935 and Year(OrderDate)>2016
The preceding query will update four rows.
Note
If you perform a count operation on the
orderstable on the publisher and subscriber, the count won't match. Remember that we applied theyear(orderdate)>2016filter when creating the publication. This also explains why the year filter is applied on the preceding update query.Generate the snapshot by running the following on the command line:
REM -- Change the variable values as per your environment SET Publisher=WIN2012R2\SQL2016 SET PublisherDB=WideWorldImporters SET Publication=WWI-Snapshot "C:\Program Files\Microsoft SQL Server\130\COM\SNAPSHOT.EXE" -Publisher %Publisher% -PublisherDB %PublisherDB% -Distributor %Publisher% -Publication %Publication% -DistributorSecurityMode 1
Note
You can also copy the code from the
C:\Code\Lesson01\snapshot.txtfile.You should get an output similar to the following screenshot:
Figure 1.75: Generating the snapshot
You can also verify the new snapshot generation by looking into the snapshot folder for the snapshot files dated to the date you ran the command. Moreover, you can query the
MSsnapshot_historytable in thedistributiondatabase for the run status.We have a fresh snapshot now. Run
distrib.exeto apply the snapshot to thesubscriberdatabase:"C:\Program Files\Microsoft SQL Server\130\COM\DISTRIB.EXE" -Publisher WIN2012R2\SQL2016 -PublisherDB [WideWorldImporters] -Publication [WWI-Snapshot] -Distributor [WIN2012R2\SQL2016] -SubscriptionType 1 -Subscriber [WIN2012R2\SQL2014] -SubscriberSecurityMode 1 -SubscriberDB [WideWorldImporters]
Note
You can also copy the code from the
C:\Code\Lesson01\distributor.txtfile.You'll have to replace the publisher and subscriber SQL Server instance name in this command. If you are replicating a database other than
WideWorldImportersand have a different publication name, replace those parameters as well.You should get a similar output to what's shown in the following screenshot:
Figure 1.76: Applying the snapshot
The distribution agent runs and applies the snapshot on the subscriber. Note that the replication agent runs under the security context of the administrator. This is because the command-line console is open as an administrator.
Note
If you run the distributor agent again, you'll get a message saying that there are no replicated transactions available. This is because a snapshot can only be applied once.
Now, query the
orderstable at thesubscriberdatabase to verify the changes made at thepublisherdatabase:SELECT ExpectedDeliveryDate FROM WideWorldImporters.Sales.Orders WHERE Customerid = 935 and YEAR(OrderDate)>2016
You should get the following output.
Figure 1.77: Querying the Orders table
The changes are therefore replicated.
