Introduction
Adventure Works Cycles must develop a reporting solution to manage various aspects of company operations. To minimize the impact of reporting operations in the environment, a dedicated reporting database is being created, which will be synchronized with production using transactional replication.
Initializing a Subscription
Task Supporting Information
Create a SQL Server Scripts project named AWReplication.
1. Start SQL Server Management Studio.
2. Create a new SQL Server Scripts project named AWReplication in the
D:\Labfiles\Starter folder.
Create a publication on MIAMI named AWTables that contains all tables in AdventureWorks.
1. Use the New Publication Wizard to create a new transactional publication named
AWTables. Use the following information to help you:
• MIAMI should be configured as both the publisher and the distributor.
• The publication should contain all tables in the AdventureWorks database.
• Do not create an initial snapshot.
• The snapshot agent should use the MIAMI\Student account with the password Pa$$w0rd.
• Generate a script for the publication and add it to the project.
Configure the AWTables publication for initialization from backup.
1. View the properties of the AWTables publication.
2. In the Subscription Options category, set the Allow initialization from backup files property to True.
Back up the AdventureWorks database on MIAMI.
1. Use Object Explorer to back up the AdventureWorks database on MIAMI, generating a script file for the action.
2. Add the script file you generated to the project.
Restore the AdventureWorks database on MIAMI\SQLINSTANCE2.
1. Use Object Explorer to restore the AdventureWorks database to
MIAMI\SQLINSTANCE2. You must move the database files as part of the restore process. Generate a script file for the restore action.
2. Add the script file you generated to the project
MCT USE ONLY. STUDENT USE PROHIBITED
Initialize a subscription on
MIAMI\SQLINSTANCE2 from the backup of AdventureWorks.
1. Add a new query to the project, connecting to MIAMI.
2. Execute the sp_addsubscription stored procedure in the AdventureWorks database with the following options:
• @publication = 'AWTables'
• @subscriber = 'MIAMI\SQLINSTANCE2'
• @destination_db = 'AdventureWorks'
• @subscription_type = 'push'
• @sync_type = 'initialize with backup'
• @backupdevicetype = 'location of the backup file'
Establish a baseline latency measurement by using a tracer token.
1. Use Replication Monitor to view the AWTables publication.
2. Add a tracer token and view the latency measurement for the AWTables publication.
Procedure Answer Key
Creating a SQL Server Scripts project named AWReplication
You must perform the following steps to create a SQL Server Scripts project named AWReplication.
1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the values in the following table and then click Connect.
Property Value
Server type Database Engine
Server name MIAMI
Authentication Windows Authentication
3. On the File menu, point to New and then click Project.
4. In the New Project dialog box, specify the values in the following table and then click OK.
Property Value
Name AWReplication Location D:\Labsfiles\Starter
MCT USE ONLY. STUDENT USE PROHIBITED
Create directory for solution
Cleared
Creating a publication on MIAMI named AWTables that contains all tables in AdventureWorks You must perform the following steps to create a publication on MIAMI named AWTables that contains all tables in AdventureWorks.
1. If Object Explorer is not visible, click Object Explorer on the View menu.
2. In Object Explorer, expand Replication, right-click Local Publications, and then click New Publication.
5. On the New Publication Wizard page, click Next.
6. On the Distributor page, click Next to use MIAMI as its own distributor.
7. On the Snapshot Folder page, click Next to use the default snapshot folder location.
8. On the Publication Database page, click AdventureWorks to choose it as the publication database and then click Next.
9. On the Publication Type page, click Transactional publication and then click Next.
10. On the Articles page, select Tables, and then click Next.
11. Click Next on the Filter Table Rows page.
12. On the Snapshot Agent page, leave all check boxes unselected and click Next.
13. On the Agent Security page, click Security Settings.
14. Enter MIAMI\Student as the Process account. Enter Pa$$w0rd in the Password and Confirm Password boxes. Leave By impersonating the process account selected and then click OK.
15. Click Next on the Agent Security page.
16. On the Wizard Actions page, select the Create the publication and Generate a script file with steps to create the publication check boxes and then click Next.
17. On the Script File Properties page, change the File name to
D:\Labfiles\Starter\AWReplication\PublicationScript.sql and then click Next.
18. On the Complete the Wizard page, enter AWTables as the Publication name and then click Finish.
19. On the Creating Publication page, wait until all actions have been completed, check that there are no errors, and then click Close.
20. On the Project menu, click Add Existing Item.
21. Add D:\Labfiles\Starter\AWReplication\PublicationScript.sql. When prompted, connect to MIAMI by using Windows authentication.
22. On the File menu click Save All.
Configuring the AWTables publication for initialization from backup
You must perform the following steps to configure the AWTables publication for initialization from backup.
1. In Object Explorer, expand the Local Publications folder. If the AWTables publication is not listed, right-click the folder and click Refresh.
MCT USE ONLY. STUDENT USE PROHIBITED
2. Right-click [AdventureWorks]:AWTables, and click Properties.
3. In the Select a Page pane, click Subscription Options.
4. Set the Allow initialization from backup files property to True, and then click OK.
Backing up the AdventureWorks database on MIAMI
You must perform the following steps to back up the AdventureWorks database on MIAMI 1. In Object Explorer, expand the Databases folder.
2. Right-click AdventureWorks, point to Tasks, and then click Back Up.
3. Click Full as the Backup type.
4. Note the default backup destination in the Destination section.
5. Click the arrow for the Script button and then click Script Action to File. Save the file as D:\Labfiles\Starter\AWReplication\Backup.sql.
6. Click OK to back up the database. When the backup is complete, click OK to close the message box.
7. On the Project menu, click Add Existing Item.
8. Add D:\Labfiles\Starter\AWReplication\Backup.sql. When prompted, connect to MIAMI by using Windows authentication.
9. On the File menu, click Save All.
Restore the AdventureWorks database on MIAMI\SQLINSTANCE2.
You must perform the following steps to restore the AdventureWorks database on MIAMI\SQLINSTANCE2.
1. In Object Explorer, click the Connect button on the toolbar and then click Database Engine.
2. In the Connect to Server dialog box, specify the values in the following table and then click Connect.
Property Value
Server type Database Engine
Server name MIAMI\SQLINSTANCE2
Authentication Windows Authentication
3. In Object Explorer, right-click the Databases folder for MIAMI\SQLINSTANCE2 and then click Restore Database.
4. In the To database box, type AdventureWorks.
5. Select From device and then click the ellipsis button (...).
6. Click Add, select C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak, and then click OK.
7. Click OK in the Specify Backup dialog box.
8. Select the AdventureWorks – Full Database Backup check box in the list of backup sets.
9. Click Options in the Select a Page pane.
MCT USE ONLY. STUDENT USE PROHIBITED
10. Change the Restore As value for AdventureWorks_Data to C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\AdventureWorks_Data.mdf.
11. Change the Restore As value for AdventureWorks_Log to C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\AdventureWorks_Log.ldf.
12. In the Restore Database dialog box, click the arrow for the Script button and then click Script Action to File. Save the file as D:\Labfiles\Starter\AWReplication\Restore.sql.
13. Click OK to restore the database. When the restoration is complete, click OK to close the message box.
14. On the Project menu, click Add Existing Item.
15. Add D:\Labfiles\Starter\AWReplication\Restore.sql. When prompted, connect to MIAMI\SQLINSTANCE2 by using Windows authentication.
16. On the File menu, click Save All.
Initializing a subscription on MIAMI\SQLINSTANCE2 from the backup of AdventureWorks
You must perform the following steps to initialize a subscription on MIAMI\SQLINSTANCE2 from the backup of AdventureWorks.
1. On the Project menu, click New Query. When prompted, connect to MIAMI by using Windows authentication.
2. In Solution Explorer, rename the new query file to SubscriptionScript.sql.
3. Type the following Transact-SQL code in the new query window:
USE AdventureWorks GO
EXEC sp_addsubscription
@publication='AWTables',
@subscriber = 'MIAMI\SQLINSTANCE2', @destination_db = 'AdventureWorks', @subscription_type='push',
@sync_type = 'initialize with backup', @backupdevicetype = 'disk',
@backupdevicename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
4. Click the Execute button on the toolbar, and then click Save All on the File menu.
5. In Object Explorer, expand the Replication and Local Subscriptions folder for the MIAMI\SQLINSTANCE2 server and verify that the subscription has been created.
Establishing a baseline latency measurement by using a tracer token
You must perform the following steps to establish a baseline latency measurement by using a tracer token.
1. In Object Explorer, right-click the Replication folder under the MIAMI server and click Launch Replication Monitor.
2. In Replication Monitor, expand MIAMI and click the [AdventureWorks]:AWTables publication.
3. On the Tracer Tokens tab, click Insert Tracer. Note the time that the tracer was inserted and the latency measurements that it recorded.
4. Close Replication Monitor and SQL Server Management Studio.
MCT USE ONLY. STUDENT USE PROHIBITED
Discussion
Q Why do you want to establish a performance baseline?
A The most fundamental question that is asked in a production environment is: “How long will it take me to be synchronized.” The amount of time to synchronize is dependent upon knowing the amount of data that needs to be sent along with the amount of time it takes for a transaction to reach the subscriber. Replication Monitor will display the number of commands that are pending transfer. A tracer token is used to measure the time required for a transaction to move from the publisher to the subscriber. By
creating a tracer token after the subscription is created, you will establish the amount of time it takes for a transaction to move from the publisher to the subscriber. Replication Monitor can then use this information along with the number of transactions waiting to be sent to answer the question of how long it will take to be synchronized.