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: .sch files 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

Do not select any other objects for now.

Select the BuyingGroups table 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 Columnstore index 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 Orders table will have data for all the years. This example, however, applies the filter on the Orders table 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.

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

The minimum permissions required by the Windows account under which the snapshot agent process runs are db_owner rights on the distribution database, db_owner rights 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.

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

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.

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.

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_owner fixed 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 the C:\Program Files\Microsoft SQL Server\InstanceID\COM folder for replication LOB data.

The account that connects to the subscriber should be a member of the db_owner fixed 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.

Subscriber information is stored in the distribution.dbo.subscriber_info table. 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.

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.

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.exe parameters (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.

In the Object Explorer, navigate to the Databases node and expand the WideWorldImporters database on the subscriber server.

Observe that it now has the replicated tables:

Figure 1.74: The replicated tables

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 the orders table and generate a new snapshot.

Execute the following query to update the orders table on the publisher database:

UPDATE [Sales].Orders SET ExpectedDeliveryDate = '2017-12-10' WHERE 
  Customerid = 935 and Year(OrderDate)>2016

The preceding query will update four rows.

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

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_history table in the distribution database for the run status.

We have a fresh snapshot now. Run distrib.exe to apply the snapshot to the subscriber database:

"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]

You'll have to replace the publisher and subscriber SQL Server instance name in this command. If you are replicating a database other than WideWorldImporters and 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.

Now, query the orders table at the subscriber database to verify the changes made at the publisher database:

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