SQL Server provides multiple technologies that allow DBAs to horizontally scale their workloads between multiple databases to avoid lock contention or to scale them horizontally between servers to spread resource utilization. These technologies include database snapshots, replication, and AlwaysOn Availability Groups. This chapter discusses the considerations for these technologies and demonstrates how to implement them.

Database Snapshots

A database snapshot is a point-in-time view of a database that never changes after it is generated. It works using copy-on-write technology; this means that if a page is modified in the source database, the original version of the page is copied to an NTFS sparse file, which is used by the database snapshot. A sparse file is a file that starts off empty, with no disk space allocated. As pages are updated in the source database and these pages are copied to the sparse file, it grows to accommodate them. This process is illustrated in Figure 16-1.

If a user runs a query against the database snapshot, SQL Server checks to see if the required pages exist in the database snapshot. Any pages that do exist are returned from the database snapshot, whereas any other pages are retrieved from the source database, as illustrated in Figure 16-2. In this example, to satisfy the query, SQL Server needs to return Page 1:100 and Page 1:101. Page 1:100 has been modified in the source database since the snapshot was taken. Therefore, the original version of the page has been copied to the sparse file and SQL Server retrieves it from there. Page 1:101, on the other hand, has not been modified in the source database since the snapshot was created. Therefore it does not exist in the sparse file, and SQL Server retrieves it from the source database.

If your data-tier application is suffering from contention caused by locking, then you can scale-out reporting to a database snapshot. It is important to note, however, that because a database snapshot must reside on the same instance as the source database, it does not help overcome resource utilization issues. In fact, the opposite is true. Because any modified pages must be copied to the sparse file, the IO overhead increases. The memory footprint also increases, since pages are duplicated in the buffer cache for each database.

In addition to the resource overhead of database snapshots, another issue you encounter when you use them to reduce contention for reporting is that data becomes stale as pages in the source database are modified. To overcome this, you can create a metadata-driven script to periodically refresh the snapshot. This is demonstrated in Chapter 17.

The issue of data becoming stale can also be an advantage however, because it gives you two benefits: first, it means that you can use snapshots for historic reporting purposes; and second, it means that you can use database snapshots to recover data after user error has occurred. Be warned, however, that these snapshots provide no resilience against IO errors or database failures, and you cannot use them to replace database backups.

Implementing Database Snapshots

Before demonstrating how to create a database snapshot, we first create the Chapter16 database, which we use for demonstrations throughout this chapter. The script in Listing 16-1 creates this database and populates it with data.

CREATE DATABASE Chapter16 ;
GO

USE Chapter16
GO

CREATE TABLE Customers
(
ID                  INT            PRIMARY KEY        IDENTITY,
FirstName           NVARCHAR(30),
LastName            NVARCHAR(30),
CreditCardNumber    VARBINARY(8000)
) ;
GO

--Populate the table

DECLARE @Numbers TABLE
(
         Number        INT
)

;WITH CTE(Number)
AS
(
         SELECT 1 Number
         UNION ALL
         SELECT Number + 1
         FROM CTE
         WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE ;

DECLARE @Names TABLE
(
         FirstName       VARCHAR(30),
         LastName        VARCHAR(30)
) ;

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’),
                 (’Margaret’, ’Jones’) ;

INSERT INTO Customers(Firstname, LastName, CreditCardNumber)
SELECT  FirstName, LastName, CreditCardNumber FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName
                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName
                ,(SELECT CONVERT(VARBINARY(8000)
                ,(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                  FROM @Numbers
                  WHERE Number BETWEEN 10 AND 99
                  ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99
                          ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99
                          ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99
                          ORDER BY NEWID()))) CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b
) d ;

To create a database snapshot on the Chapter16 database, we use the CREATE DATABASE syntax, adding the AS SNAPSHOT OF clause, as demonstrated in Listing 16-2. The number of files must match the number of files of the source database, and the snapshot must be created with a unique name. The .ss file extension is standard, but not mandatory. I have known some DBAs to use an .ndf extension if they cannot gain an antivirus exception for an additional file extension. I recommend using the .ss extension if possible, however, because this clearly identifies the file as being associated with a snapshot.

CREATE DATABASE Chapter16_ss_0630
ON PRIMARY
( NAME = N’Chapter16’, FILENAME = N’F:MSSQLDATAChapter16_ss_0630.ss’ )
AS SNAPSHOT OF Chapter16 ;

The fact that each database snapshot must have a unique name can cause an issue for connecting applications if you plan to use multiple snapshots; this is because the applications do not know the name of the database to which they should connect. You can resolve this issue by programmatically pointing applications to the latest database snapshot. You can find an example of how to do this in Listing 16-3. This script creates and runs a procedure that returns all data from the Contacts table. It first dynamically checks the name of the most recent snapshot that is based on the Chapter16 database, which means that the data will always be returned from the most recent snapshot.

USE Chapter16
GO

CREATE PROCEDURE dbo.usp_Dynamic_Snapshot_Query
AS
BEGIN
         DECLARE @LatestSnashot NVARCHAR(128)
         DECLARE @SQL NVARCHAR(MAX)

         SET @LatestSnashot = (
            SELECT TOP 1 name from sys.databases
            WHERE source_database_id = DB_ID(’Chapter16’)
            ORDER BY create_date DESC ) ;

         SET @SQL = ’SELECT * FROM ’ + @LatestSnashot + ’.dbo.Customers’ ;

         EXEC(@SQL) ;
END

EXEC dbo.usp_Dynamic_Snapshot_Query ;

Recovering Data from a Snapshot

If user error leads to data loss, then a database snapshot can allow a DBA to recover data without needing to restore a database from a backup, which can reduce the RTO for resolving the issue. Imagine that a user accidently truncates the Contacts table in the Chapter16 database; we can recover this data by reinserting it from the snapshot, as demonstrated in Listing 16-4.

--Truncate the table

TRUNCATE TABLE Chapter16.dbo.Customers ;

--Allow Identity values to be re-inserted

SET IDENTITY_INSERT Chapter16.dbo.Customers ON ;

--Insert the data

INSERT INTO Chapter16.dbo.Customers(ID, FirstName, LastName, CreditCardNumber)
SELECT *
         FROM Chapter16_ss_0630.dbo.Customers ;

--Turn off IDENTITY_INSERT

SET IDENTITY_INSERT Chapter16.dbo.Customers OFF ;

If a large portion of the source database has been damaged by user error, then instead of fixing each data issue individually, it may be quicker to recover the entire database from the snapshot. You can do this using the RESTORE command with the FROM DATABASE_SNAPSHOT syntax, as demonstrated in Listing 16-5.

USE Master
GO

RESTORE DATABASE Chapter16
         FROM DATABASE_SNAPSHOT = ’Chapter16_ss_0630’ ;

Replication

SQL Server provides a suite of replication technologies, which you can use to disperse data between instances. You can use replication for many purposes, including offloading reporting, integrating data from multiple sites, supporting data warehousing, and exchanging data with mobile users.

Replication Concepts

Replication draws its terminology from the publishing industry. The components of a Replication topology are described in Table 16-1.

Figure 16-3 illustrates how the replication components fit together within a Replication topology. In this example, two subscribers each receiving the same publication and the distributor has been separated from the publisher. This is known as a remote distributor. If the publisher and distributor shared an instance, then it is known as a local distributor.

Types of Replication

SQL Server offers three broad types of replication: snapshot, transactional, and merge. These replication technologies are introduced in the following sections.

Snapshot

Snapshot replication works by taking a complete copy of all articles at the point when synchronizations occurs; therefore, it does not need to track data changes between synchronizations. If you have defined a filter on the article, then only the filtered data is copied. This means that snapshot replication has no overhead, except when synchronization is occurring. When synchronization does occur, however, the resource overhead can be very high if there is a large amount of data to replicate.

The Snapshot Agent creates a system view and system stored procedure for each article in the publication. It uses these objects to generate the contents of the articles. It also creates schema files, which it applies to the subscription database before it uses BCP (Bulk Copy Program) to bulk copy the data.

Because of the resource utilization profile, snapshot replication is most suited to situations in which the dataset being replicated is small and changes infrequently, or in cases in which many changes happen in a short time period. (An example of this may include a price list, which is updated monthly.) Additionally, snapshot replication is the default mechanism you use to perform the initial synchronization for transactional and merge replication.

When you are using snapshot replication, the Snapshot Agent runs on the publisher to generate the publication. The Distribution Agent (which runs either on the distributor or on each subscriber) then applies the publication to the subscribers. Snapshot replication always works in a single direction only, meaning that the subscribers can never update the publisher.

Transactional

Transactional replication works by reading transactions from the transaction log on the publisher and then sending these transactions to be reapplied on the subscribers. The Log Reader Agent, which runs at the publisher, reads the transactions, and a VLF is not truncated until all log records marked for replication have been processed. This means that if there is a long period between synchronizations and many data modifications occur, there is a risk that your transaction log will grow or even run out of space. After the transactions have been read from the log, the Distribution Agent applies the transactions to the subscribers. This agent runs at the distributor in a push subscription model or at each of the subscribers in a pull subscription model. Synchronization is scheduled by SQL Server Agent jobs, which are configured to run the replication agents, and you can configure synchronization so it happens continuously or periodically, depending on your requirements. The initial data synchronization is performed using the Snapshot Agent by default.

Transactional replication is normally used in server-to-server scenarios where there is a high volume of data modifications at the publisher and there is a reliable network connection between the publisher and subscriber. A global data warehouse is an example of this, having subsets of data replicated to regional data warehouses.

Standard transactional replication always works in a single direction only, which means that it is not possible for the subscribers to update the publisher. SQL Server also offers peer-to-peer transactional replication, however. In a Peer-to-Peer topology, each server acts as a publisher and a subscriber to the other servers in the topology. This means that changes you make on any server are replicated to all other servers in the topology.

Because all servers can accept updates, it is possible for conflicts to occur. For this reason, peer-to-peer replication is most suitable when each peer accepts updates on a differ partition of data. If a conflict does occur, you can configure SQL Server to apply the transaction with the highest OriginatorID (a unique integer that is assigned to each node in the topology), or you can choose to resolve the conflict manually, which is the recommended approach.

Merge

Merge replication allows you to update both the publisher and the subscribers. This is a good choice for client-server scenarios, such as mobile sales persons who can enter orders on their laptops and then have them sync with the main sales database. It can also be useful in some server-server scenarios—for example, regional data warehouses that are updated via ETL processes and then rolled up into a global data warehouse.

Merge replication works by maintaining a rowguid on every table that is an article within the publication. If the table does not have a uniquiidentifier column with the ROWGUID property set, then merge replication adds one. When a data modification is made to a table, a trigger fires, which maintains a series of change-tracking tables. When the Merge Agent runs, it applies only the latest version of the row. This means that resource utilization is high for tracking changes that occur, but the tradeoff is that merge replication has the lowest overhead for actually synchronizing the changes.

Because the subscribers, as well as the publisher, can be updated, there is a risk that conflicts between rows will occur. You manage these using conflict resolvers. Merge replication offers 12 conflict resolvers out of the box, including earliest wins, latest wins, and subscriber always wins. You can also program your own COM-based conflict resolvers or choose to resolve conflicts manually.

Because you can use merge replication in client-server scenarios, it offers you a technology called Web synchronization for updating subscribers. When you use Web synchronization, after extracting the changes, the Merge Agent makes an HTTPS request to IIS and sends the data changes to the subscribers in the form of an XML message. Replication Listener and Merge Replication Reconciler, which are processes running on the subscriber, process the data changes, after sending any data modifications made at the subscriber back to the publisher.

Implementing Transactional Replication

The most appropriate type of replication for scaling workloads is standard transactional replication. We discuss how to implement this technology in the following sections.

Implementing the Distributor and Publisher with SSMS

To begin configuring transaction replication for the Chapter16 database, we select New Publication from the context menu of Replication | Local Publications in Object Explorer, within the PROSQLADMIN instance. This causes the New Publication Wizard to be invoked. After passing through the welcome screen, we see the Distributor page of the wizard, as illustrated in Figure 16-4. On this page, we can choose to use the publisher instance as the distributor or specify a different instance. If you specify a different instance, then you must already have configured the instance as a distributor, but in our case, we use the same instance, thereby configuring a local distributor.

Because our instance is not currently configured for SQL Server Agent service to start automatically, we now see the SQL Server Agent Start page, which warns us of this situation. This is because replication agents rely on SQL Server Agent to be scheduled and run. We choose the option for SQL Server Agent to be configured to start automatically, as shown in Figure 16-5.

On the Snapshot Folder page of the wizard, we select the location the Snapshot Agent will use to store the initial data for synchronization. This can be a local folder or a network share, but if you specify a local folder, then pull subscriptions are not supported, since the subscribers are unable to access it. In our case, we use a network share. The Snapshot Folder page is illustrated in Figure 16-6.

On the Publication Database page, shown in Figure 16-7, we select the database that contains the objects that we wish to use as articles in our publication. All articles within a publication must reside in the same database, so we can only select one database on this screen. To replicate articles from multiple databases, we must have multiple publications.

On the Publication Type page, shown in Figure 16-8, we select the type of replication we wish to use for the publication—in our case, transactional.

On the Articles page of the wizard, illustrated in Figure 16-9, we select the objects that we wish to include in our publication. All tables that you wish to publish must have a primary key, or you are not able to select them. Within a table, you can also select individual columns to replicate, if you need to.

The Article Properties button allows us to alter the properties, either for the selected article or for all articles within the Article Properties dialog box, which is shown in Figure 16-10. You should usually leave most properties as the default unless you have a specific reason to change them. However, you should pay particular attention to some properties.

You use the Action If Name Is In Use property to determine the behavior if a table with the same name already exists in the subscriber database. The possible options are as follows:

• Keep the existing object unchanged.
• Drop the existing object and create a new one.
• Delete data. If the article has a row filter, delete only the data that matches the filter.
• Truncate all data in the existing object.

The Copy Permissions property demines if object-level permissions are copied to the subscriber. This is important, since you may or may not want to configure the permissions the same as they are for the publisher depending on how you are using the environment.

On the Filter Table Rows page of the wizard, shown in Figure 16-11, we can use the Add, Edit, and Delete buttons to manage filters. Filters essentially add a WHERE clause to the article so that you can limit the number of rows that are replicated. You’ll find this is especially useful for partitioning the data across multiple subscribers.

Figure 16-12 illustrates the Add Filter dialog box. In our case, we are creating a filter so that only customers with an ID > 500 are replicated. The ways you can use this in production scenarios include filtering based on region, account status, and so on.

On the Snapshot Agent page, illustrated in Figure 16-13, you can configure the initial snapshot to be created immediately, as we have done here, or you can schedule the Snapshot Agent to run at a specific time.

On the Agent Security page, illustrated in Figure 16-14, you are invited to configure the accounts that are used for running each of the replication agents. At a minimum, the account that runs the Snapshot Agent should have the following permissions:

• Be a member of the db_owner database role in the distribution database
• Have read, write, and modify permissions on the share that contains the snapshot

  

  Figure 16-14. The Agent Security page

At a minimum, the account that runs the Log Reader Agent must have the following permissions:

• Be a member of the db_owner database role in the distribution database

When you create the subscription, choose the sync_type. This configuration choice affects the Log Reader account’s required permissions in the following ways:

• Automatic—No additional permissions required
• Anything else—sysadmin on the distributor

Clicking the Security Settings button for the Snapshot Agent causes the Snapshot Agent Security dialog box to display, as shown in Figure 16-15. In this dialog box, you can choose to run the Snapshot Agent under the context of the Server Agent service account, or you can specify a different Windows account to use. To follow the principle of least privilege, you should use a separate account. It is also possible to specify a different account from which to make connections to the publisher instance. In our case though, we specify that the same account should make all the connections.

Back on the Agent Security page, you can elect to either specify the details for the account that will run the Log Reader Agent, or you can clone the information you have already entered for the account that will run the Snapshot Agent.

On the Wizard Actions page, you can elect to either generate the publication, as we have done, script the generation of the publication, or both. This page is illustrated in Figure 16-16.

Finally, on the Complete The Wizard page, shown in Figure 16-17, a summary of the actions that the wizard will perform displays. You also need to enter a name for the publication on this screen.

Implementing the Distributor and Publisher with T-SQL

It is important that, as a DBA, you master the ability to script a Replication topology, because at times, you need to tear down and rebuild the environment, such as when you are performing a major code deployment. Also, not all options are available when you are adding replication from the wizard. To perform the same actions via T-SQL, we first need to configure the instance as a distributor. The first step in this process is to register and mark the instance as a distributor. You can do this using the sp_adddistributor system stored procedure, which accepts the parameters described in Table 16-2.

We can run the procedure in our scenario by using the command in Listing 16-6.

USE MASTER
GO

EXEC sp_adddistributor @distributor = N’WIN-KIAGK4GN1MJPROSQLADMIN’, @password = NULL ;

Our next step is to create the Distribution database. We can perform this task by running the sp_adddistributiondb system stored procedure. This procedure accepts the parameters detailed in Table 16-3.

We can use the command in Listing 16-7 to run the sp_adddistributiondb stored procedure for our configuration. Before running the script, be sure to change the file paths to match your own configuration.

EXEC sp_adddistributiondb @database = N’Distribution’
        , @data_folder = N’C:MSSQLData’
        , @log_folder = N’C:MSSQLData’
        , @log_file_size = 2
        , @min_distretention = 0
        , @max_distretention = 72
        , @history_retention = 48
        , @security_mode = 1 ;
GO

We now need to register our instance as a publisher within the Distribution database. We can achieve this using the sp_adddistpublisher system stored procedure. This procedure accepts the parameters described in Table 16-4.

For our environment, we can run the sp_adddistpublisher by using the command in Listing 16-8.

EXEC sp_adddistpublisher
        @publisher = N’WIN-KIAGK4GN1MJPROSQLADMIN’
        , @distribution_db = N’Distribution’
        , @working_directory = N’\WIN-KIAGK4GN1MJReplData’
        , @thirdparty_flag = 0 ;

We now need to enable the Chapter16 database for replication. We do this using the sp_replicationdboption system stored procedure, which accepts the parameters detailed in Table 16-5.

The sp_replicationdboption procedure can be run to achieve our goals by using the script in Listing 16-9.

USE Chapter16
GO

EXEC sp_replicationdboption
        @dbname = N’Chapter16’
        , @optname = ’publish’
        , @value = ’true’ ;

Our next step is to configure the Log Reader Agent. We can do this using the sys.sp_addlogreader_agent system stored procedure. This procedure accepts the parameters detailed in Table 16-6.

For the purpose of configuring our environment, we can use the command in Listing 16-10 to execute the sp_addlogreader_agent procedure.

EXEC Chapter16.sys.sp_addlogreader_agent
        @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = ’Pa$$w0rd’
        , @publisher_security_mode = 1
        , @job_name = NULL ;

Our next step in configuring replication is to add the publisher. You can perform this task using the sp_addpublication system stored procedure. sp_addpublication accepts the parameters described in Table 16-7.

To add the publication to our topology using the sp_addpublication procedure, we could run the command in Listing 16-11.

USE Chapter16
GO

EXEC sp_addpublication
        @publication = ’Chapter16_Customers_Filtered’
        , @description = ’Transactional publication of database ’’Chapter16’’ from Publisher ’’WIN-KIAGK4GN1MJPROSQLADMIN’’.’
        , @sync_method = N’concurrent’
        , @retention = 0
        , @allow_push = N’true’
        , @allow_pull = N’true’
        , @allow_anonymous = N’true’
        , @enabled_for_internet = N’false’
        , @snapshot_in_defaultfolder = N’true’
        , @allow_subscription_copy = N’false’
        , @repl_freq = N’continuous’
        , @status = N’active’
        , @independent_agent = N’true’
        , @immediate_sync = N’true’
        , @allow_sync_tran = N’false’
        , @allow_dts = N’false’
        , @replicate_ddl = 1
        , @allow_initialize_from_backup = N’false’
        , @enabled_for_p2p = N’false’
        , @enabled_for_het_sub = N’false’ ;

Our next task is to configure the Snapshot Agent for the publication. We can perform this task by using the sp_addpublication_snapshot system stored procedure. This procedure accepts the parameters detailed in Table 16-8.

Table 16-9 describes how to calculate the correct value for @frequency_interval based on the value specified for @frequency_type.

To use the sp_addpublication_snapshot procedure to create the Snapshot Agent for our environment, we can use the command in Listing 16-12.

EXEC sp_addpublication_snapshot
        @publication = ’Chapter16_Customers_Filtered’
        , @frequency_type = 1
        , @frequency_interval = 1
        , @frequency_subday = 8
        , @frequency_subday_interval = 1
        , @active_start_time_of_day = 0
        , @active_end_time_of_day = 235959
        , @active_start_date = 0
        , @active_end_date = 0
        , @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = ’Pa$$w0rd’
        , @publisher_security_mode = 1 ;

Our next step is to add the Customers article to our publication. To do this, we use the sp_addarticle system stored procedure. This procedure accepts the parameters described in Table 16-10.

When you use CALL syntax to update a subscriber, all values for inserted and updated columns are passed. When you use SCALL, which is valid for updates, only values for updated columns are passed, along with the original primary key values. When you use XCALL syntax, which you can use for updates or deletes, both the old value and the new value for every column are passed. When you use MCALL syntax, which is valid for updates, the update values are passed, along with the original primary key values.

Possible values for the article type are described in Table 16-11.

To use sp_addarticle to create an article for the Customers table with the same filter that we created through the GUI, we can use the command in Listing 16-13.

EXEC sp_addarticle
        @publication = ’Chapter16_Customers_Filtered’
        , @article = ’Customers’
        , @source_owner = ’dbo’
        , @source_object = ’Customers’
        , @type = ’logbased’
        , @pre_creation_cmd = ’drop’
        , @schema_option = NULL
        , @identityrangemanagementoption = ’manual’
        , @destination_table = ’Customers’
        , @destination_owner = ’dbo’
        , @ins_cmd = ’CALL sp_MSins_dboCustomers’
        , @del_cmd = ’CALL sp_MSdel_dboCustomers’
        , @upd_cmd = ’SCALL sp_MSupd_dboCustomers’
        , @filter_clause = ’ID > 500’ ;

Our next step is to add the filter to the article, which we do by using the sp_articlefilter system stored procedure. This procedure accepts the parameters detailed in Table 16-12.

To use the sp_articlefilter to add the filter to our Chapter16 article, we can use the command in Listing 16-14.

EXEC sp_articlefilter
        @publication = ’Chapter16_Customers_Filtered’
        , @article = ’Customers’
        , @filter_name = ’FLTR_Customers_1__54’
        , @filter_clause = ’ID > 500’
        , @force_invalidate_snapshot = 1
        , @force_reinit_subscription = 1 ;

Finally, we need to create the view that we use to synchronize the article. We can achieve this with the sp_articleview system stored procedure. This procedure accepts the parameters detailed in Table 16-13.

We can use the sp_articleview procedure to create a view for our article by using the command demonstrated in Listing 16-15.

EXEC sp_articleview
        @publication = ’Chapter16_Customers_Filtered’
        , @article = ’Customers’
        , @view_name = ’SYNC_Customers_1__54’
        , @filter_clause = ’ID > 500’
        , @force_invalidate_snapshot = 1
        , @force_reinit_subscription = 1 ;

Implementing the Subscriber with SSMS

Now that the PROSQLADMIN instance is configured as a distributor and publisher and our publication has been created, we need to configure our PROSQLADMIN2 instance as a subscriber. We can perform this task from either the publisher or from the subscriber. From the subscriber, we perform this task by connecting to the PROSQLADMIN2 instance and then by drilling through replication and selecting New Subscription from the context menu of Local Subscriptions. This causes the New Subscription Wizard to be invoked. After passing through the Welcome page of this wizard, you are presented with the Publication page, as illustrated in Figure 16-18. On this page, you use the Publisher drop-down box to connect to the instance that is configured as the publisher, and then you select the appropriate publication from the Databases And Publications area of the screen.

On the Distribution Agent Location page, you choose if you want to use push subscriptions or pull subscriptions. The appropriate choice here depends on your topology. If you have many subscribers, then you may choose to implement a remote distributor. If this is the case, then it is likely that you will use push subscriptions so that the server configured as the distributor has the impact of agents running. If you have many subscribers and you are using a local distributor, however, then it is likely that you will use pull subscriptions so that you can spread the cost of the agents between the subscribers. In our case, we have a local distributor, but we also only have a single subscriber, so from a performance perspective, it is an obvious choice of which server is most equipped to deal with the workload. We also have to consider security when we place the distribution agent, however; we discuss this later in this section. For this demonstration, we use push subscriptions. The Distribution Agent Location page is illustrated in Figure 16-19.

On the Subscribers page, we can select the name of our subscription database from the drop-down list. Because our subscription database doesn’t already exist, however, we select New Database, as shown in Figure 16-20, which causes the New Database dialog box to be invoked.

On the General page of the New Database dialog box, shown in Figure 16-21, you need to enter appropriate settings for the subscription database based upon its planned usage. If you need to, you can configure many of the database properties on the Options page.

On the Distribution Agent Security page, illustrated in Figure 16-22, click the ellipses to invoke the Distribution Agent Security dialog box.

In the Distribution Agent Security dialog box, illustrated in Figure 16-23, specify the details of the account that runs the Distribution Agent. When you are using push subscription, at a minimum, the account that runs the Distribution Agent should have the following permissions:

• Be a member of the db_owner role on the Distribution database
• Be a member of the publication access list (We discuss configuring publication access later in this chapter.)
• Have read permissions on the share where the snapshot is located

  

  Figure 16-23. The Distribution Agent Security dialog box

The account that is used to connect to the subscriber must have the following permissions:

• Be a member of the db_owner role in the subscription database
• Have permissions to view server state on the subscribers (This only applies if you plan to use multiple subscription streams, which we discussed later in this chapter.)

When you are using pull subscriptions, at a minimum, the account that runs the distribution agent needs the following permissions:

• Be a member of the db_owner role on the subscription database
• Be a member of the publication access list (We discuss configuring publication access later in this chapter.)
• Have read permissions on the share where the snapshot is located
• Have permissions to view server state on the subscriber (This only applies if you plan to use multiple subscription streams; we discuss this later in this chapter.)

In the first section of the dialog box, you select if you want to impersonate the SQL Server service account or specify a different account on which to run the agent. To enforce the principle of least privilege, you should use a different account. In the second section of the dialog box, you specify how the Distribution Agent connects to the distributor. If you are using push subscriptions, then the agent must use the account that runs the Distribution Agent. In the third section of the dialog box, you specify how the Distribution Agent connects to the subscriber. If you are using pull subscriptions, then you must use the same account that is running the Distribution Agent.

On the Synchronization Schedule page, you define a schedule for the Distribution Agent to run. You can choose to run the agent continuously, run the agent only on demand, or define a new server agent schedule on which to run the Distribution Agent, as illustrated in Figure 16-24. We choose to run the agent continuously.

On the Initialize Subscriptions page, depicted in Figure 16-25, you choose if you want the subscription to be initialized immediately, or if you want to wait for the first synchronization, and then initialize it from the snapshot at that point. For this demonstration, we initialize the subscription immediately.

On the Wizard Actions page, you need to choose between whether you want to create the subscription immediately or if you want to script the process. We choose to create the subscription immediately. This page is shown in Figure 16-26.

Finally, on the Complete The Wizard page, illustrated in Figure 16-27, you are given a summary of the actions that the wizard performs.

Implementing the Subscriber with T-SQL

When you are implementing the subscriber with T-SQL, as opposed to with the GUI, your first task is to add the subscription to the publication and set its status. We perform this task by using the sp_addsubscriber system stored procedure. This procedure accepts the parameters describer in Table 16-14.

To use the sp_addsubscription procedure to add the subscriber as we did through the GUI, we can use the command in Listing 16-16. This command should be run on the publisher. We do not specify the details for the SQL Server Agent job, because we configure this job in the next step.

USE Chapter16
GO

EXEC sp_addsubscription
        @publication = N’Chapter16_Customers_Filtered’
        , @subscriber = N’WIN-KIAGK4GN1MJPROSQLADMIN2’
        , @destination_db = N’Chapter16_Sub’
        , @subscription_type = N’Push’
        , @sync_type = N’automatic’
        , @article = N’all’ ;

Finally, we need to configure the SQL Server Agent job that runs the Distribution Agent. We can do this using the sp_addpushsubscription_agent system stored procedure, which accepts the parameters detailed in Table 16-15.

To use the sp_addpushsubscription_agent procedure to configure the SQL Server Agent job, which runs the Distribution Agent in our environment, we can use the command in Listing 16-17.

EXEC sp_addpushsubscription_agent
        @publication = ’Chapter16_Customers_Filtered’
        , @subscriber = N’WIN-KIAGK4GN1MJPROSQLADMIN2’
        , @subscriber_db = ’Chapter16_Sub’
        , @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = Pa$$w0rd
        , @subscriber_security_mode = 1
        , @frequency_type = 64
        , @frequency_interval = 0
        , @frequency_subday = 0
        , @frequency_subday_interval = 0
        , @active_start_time_of_day = 0
        , @active_end_time_of_day = 235959
        , @active_start_date = 20150223
        , @active_end_date = 99991231
        , @enabled_for_syncmgr = N’False’ ;

Replicating to Memory-Optimized Tables

Transactional replication is the only form of replication that supports memory-optimized tables. It does not allow memory-optimized tables to be used as articles in the publication, but it does allow data to be replicated to memory-optimized tables in the subscription database.

In order to demonstrate the configuration of this functionality, we first create a copy of the Chapter16 database, called Chapter16MemOpt, and populate the table with the data from the Chapter16 database. We then create a new publication with an independent agent and generate the snapshot. These tasks are performed in Listing 6-18.

--Create the database

CREATE DATABASE Chapter16MemOpt ;
GO

USE Chapter16MemOpt
GO

--Create and populate the table

CREATE TABLE dbo.Customers
(
        ID            INT          IDENTITY        PRIMARY KEY
        , FirstName   NVARCHAR(30)
        , LastName    NVARCHAR(30)
) ;
GO

INSERT INTO Chapter16MemOpt.dbo.Customers
SELECT
        FirstName
        , LastName
FROM Chapter16.dbo.Customers ;
GO

--Add the Publication

EXEC sp_replicationdboption
        @dbname = N’Chapter16MemOpt’
        , @optname = N’publish’
        , @value = N’true’ ;
GO

EXEC Chapter16MemOpt.sys.sp_addlogreader_agent
        @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = Pa$$w0rd
        , @publisher_security_mode = 1
        , @job_name = NULL ;
GO

EXEC sp_addpublication
        @publication = N’Chapter16_MemOpt’
        , @description = N’Transactional publication of database ’’Chapter16MemOpt’’ from Publisher ’’WIN-KIAGK4GN1MJPROSQLADMIN’’.’
        , @sync_method = N’concurrent’
        , @retention = 0
        , @allow_push = N’true’
        , @allow_pull = N’true’
        , @allow_anonymous = N’true’
        , @enabled_for_internet = N’false’
        , @snapshot_in_defaultfolder = N’true’
        , @allow_subscription_copy = N’false’
        , @repl_freq = N’continuous’
        , @status = N’active’
        , @independent_agent = N’true’
        , @immediate_sync = N’true’
        , @allow_sync_tran = N’false’
        , @allow_dts = N’false’
        , @replicate_ddl = 1
        , @allow_initialize_from_backup = N’false’
        , @enabled_for_p2p = N’false’
        , @enabled_for_het_sub = N’false’ ;
GO

EXEC sp_addpublication_snapshot
        @publication = N’Chapter16_MemOpt’
        , @frequency_type = 1
        , @frequency_interval = 1
        , @frequency_subday = 8
        , @frequency_subday_interval = 1
        , @active_start_time_of_day = 0
        , @active_end_time_of_day = 235959
        , @active_start_date = 0
        , @active_end_date = 0
        , @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = Pa$$w0rd
        , @publisher_security_mode = 1 ;
GO

EXEC sp_addarticle
        @publication = N’Chapter16_MemOpt’
        , @article = N’Customers’
        , @source_owner = N’dbo’
        , @source_object = N’Customers’
        , @type = N’logbased’
        , @pre_creation_cmd = N’drop’
        , @schema_option = 0x000000000803509F
        , @identityrangemanagementoption = N’manual’
        , @destination_table = N’Customers’
        , @destination_owner = N’dbo’
        , @ins_cmd = N’CALL sp_MSins_dboCustomers’
        , @del_cmd = N’CALL sp_MSdel_dboCustomers’
        , @upd_cmd = N’SCALL sp_MSupd_dboCustomers’ ;
GO

EXEC sp_startpublication_snapshot @publication = N’Chapter16_MemOpt’ ;

Instead of immediately adding the subscriber, we first need to perform some manual configuration. This involves manually creating the subscription database and then modifying the .SCH file for the article. The script in Listing 16-19 creates the database when run on the subscriber. In the script, we configure the database collation to be Kana sensitive and Width sensitive, as well as Case and Accent sensitive. We also configure the database to default to the SNAPSHOT isolation level for transactions involving memory-optimized tables. Transaction isolation levels are discussed in Chapter 18. The script also manually creates the table in the subscription database.

CREATE DATABASE Chapter16MmemOpt_Sub
ON PRIMARY ( NAME = PRIMARY, FILENAME = [C:MSSQLDATAChapter16MemOpt_Sub.mdf]),
FILEGROUP MemoryOptimized
      CONTAINS MEMORY_OPTIMIZED_DATA ( NAME = MemoryOptimized, FILENAME = [C:MSSQLDATASub])
LOG ON ( NAME = [Chapter16MemOpt_Sub_Log], FILENAME = [C:MSSQLLOGSub.ldf])
COLLATE Latin1_General_CS_AS_KS_WS;

ALTER DATABASE Chapter16MmemOpt_Sub SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO

USE Chapter16MemOpt_Sub
GO

CREATE TABLE dbo.Customers(
         ID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
                              PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 2000),
         FirstName nvarchar(30) COLLATE Latin1_General_BIN2 NULL,
         LastName nvarchar(30) COLLATE Latin1_General_BIN2 NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;

We now manually synchronize the data between the publication and subscription databases. It is important to ensure that no further data changes are made in the publication database from this point until replication is fully configured and synchronizing the data. The script in Listing 16-20 uses BCP to synchronize the data, so run it from the command line.

BCP Chapter16MemOpt.dbo.Customers out c:Customers.dat -S WIN-KIAGK4GN1MJPROSQLADMIN -T -c

BCP Chapter16MmemOpt_Sub.dbo.Customers in C:Customers.dat -S WIN-KIAGK4GN1MJPROSQLADMIN2 -T -c

Our next task is to reconfigure the article in order to avoid schema changes. This is because we are not able to use a snapshot to regenerate the schema. We can achieve this using the sp_changearticle system stored procedure, which accepts the parameters detailed in Table 16-16.

Therefore, to use the sp_changearticle procedure to stop schema changes from being made to our article, we can use the command in Listing 16-21.

USE Chapter16MemOpt
GO

EXEC sp_changearticle
    @publication = N’Chapter16_MemOpt’,
    @article = N’Customers’,
    @property = N’schema_option’,
    @value = 0,
    @force_invalidate_snapshot = 1,
    @force_reinit_subscription = 1;
GO

Finally, we can create a subscription, which is configured to not be synchronized with a snapshot, along with the SQL Server Agent job to run the independent Distribution Agent. We do this using the sp_addsubscription and sp_addpushsubscription_agent system stored procedures as demonstrated in Listing 16-22.

USE Chapter16MemOpt
GO

EXEC sp_addsubscription
    @publication = N’Chapter16_MemOpt’,
    @subscriber = N’WIN-KIAGK4GN1MJPROSQLADMIN2’,
    @destination_db = N’Chapter16MemOpt_Sub’,
    @subscription_type = N’Push’,
    @sync_type = N’replication support only’,
    @article = N’all’,
    @update_mode = N’read only’,
    @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent
        @publication = ’Chapter16_MemOpt’
        , @subscriber = N’WIN-KIAGK4GN1MJPROSQLADMIN2’
        , @subscriber_db = ’Chapter16MemOpt_Sub’
        , @job_login = N’PROSQLADMINSQLAdmin’
        , @job_password = Pa$$w0rd
        , @subscriber_security_mode = 1
        , @frequency_type = 64
        , @frequency_interval = 0
        , @frequency_subday = 0
        , @frequency_subday_interval = 0
        , @active_start_time_of_day = 0
        , @active_end_time_of_day = 235959
        , @active_start_date = 20150223
        , @active_end_date = 99991231
        , @enabled_for_syncmgr = N’False’ ;

Configuring Replication

After replication has been set up, a DBA may need to perform various maintenance activities. Two of the most common tasks are modifying the permissions of the PAL (publication access list) and optimizing the performance of replication. These topics are covered in the following sections.

Modifying the PAL

The PAL is used to control access security to the publication. When agents connect to the publication, their credentials are compared to the PAL to ensure they have the correct permissions. The benefit of the PAL is that it abstracts security from the publication database and prevents client applications from needing to modify it directly.

To view the PAL of the Chapter16_Customers_Filtered publication in SSMS and add a login called ReplicationAdmin, you must drill through Replication | Local Publishers and select Properties from the context menu of the Chapter16_Customers_Filtered publication. This causes the Properties dialog box to be invoked, and you should navigate to the Publication Access List page, which is illustrated in Figure 16-28.

You can now use the Add button to display a list of logins that do not have current access to the publication. You should select the appropriate login from the list to add it to the PAL, as shown in Figure 16-29.

To view the PAL via T-SQL, you can use the sp_help_publication_access system stored procedure. This parameter accepts the parameters describer in Table 16-17.

We can run this stored procedure, passing in the name of the publication, to retrieve a list of logins with access, as demonstrated in Listing 16-23.

EXEC sp_help_publication_access @publication = ’Chapter16_Customers_Filtered’

To add the ReplicationAdmin login to the PAL using T-SQL, we can use the sp_grant_publication_access system stored procedure, passing the name of the publication and the name of the login, which are the only two parameters accepted by this procedure. This is demonstrated in Listing 16-24.

EXEC sp_grant_publication_access
        @publication = ’Chapter16_Customers_Filtered’,
        @Login = ’DistributionAdmin’

Optimizing Performance

Out of the box, the performance of transaction replication is not always optimal. You can view the current performance of your topology by using Replication Monitor. To invoke Replication Monitor, select Launch Replication Monitor from the Replication context menu in Object Explorer.

In Replication Monitor, drill through the publisher and select the appropriate publication from the list to display the All Subscriptions tab, illustrated in Figure 16-30. In this tab, the status of each subscription displays, along with the average latency and the current performance level, based on predefined thresholds. The date and time of the last synchronization also display.

To check the latency for a single packet between the publication and distribution database, and between the distribution database and the subscription database, you can insert a tracer token by navigating to the Tracer Tokens tab and selecting Insert Tracer, as shown in Figure 16-31. This causes a special token to be sent through the topology, and the time it takes to reach each component is recorded.

To configure the latency threshold, as well alerts, navigate to the Warnings tab, as illustrated in Figure 16-32. Here, you can alter the thresholds of the default warnings, or select the Alert check box, to create a SQL Server Agent alert.

SQL Server Agent alerts can be configured by using the Configure Alerts button and then by selecting the appropriate alert from the list, as illustrated in Figure 16-33.

The Alerts Properties dialog box is now invoked, prepopulated with relevant information. Figure 16-34 illustrates the General page of the Alert Properties dialog box, which is prepopulated with an appropriate name and the correct error number.

On the Response page of the dialog box, shown in Figure 16-35, you are able to configure a SQL Server Agent job, which is fired in response to the failure. You can also configure which operators are notified of the condition. In this case, they are notified of the agent failure.

On the Options page, illustrated in Figure 16-36, you can specify if you want the error message to be included in the text of the alert; you can specify any other information that you want to appear in the text. You can also configure a delay between invocations of the response to avoid multiple alerts from being triggered for the same event.

For troubleshooting performance-related issues, it can help to turn on verbose logging for the Distribution Agent. To do this, select Agent Profile from the subscriber’s context menu in the All Subscriptions tab of Replication Monitor before you select VerboseHistoryAgentProfile from the Agent Profile dialog box, as illustrated in Figure 16-37.

Now, after restarting the Distribution Agent, take a look at the Distributor To Subscriber History in Replication Monitor; this exposes a row that gives you the statistics since the agent last started. Hovering over the row shows the complete message, similar to what appears in the illustration in Figure 16-38.

If you discover that the performance of your Transactional Replication topology is poor, you can improve performance in various ways. Two of the most effective ways are to optimize the Log Reader Agent by increasing the size of batches that the agent reads, and to optimize the Distribution Agent by increasing the size of batches that are committed and parallelizing the update of the subscribers.

By default, the Log Reader Agent reads 500 transactions at a time. If your publication has a high transactional throughput, but only a smaller subset of those transactions are being replicated, then you can improve the performance of the Log Reader Agent by increasing the batch size of transactions read. You can do this by configuring the -ReadBatchSize property.

You can change the -ReadBatchSize of the Log Reader Agent by adding the switch to the command that runs the Log Reader Agent in the second job step of the SQL Server Agent job that runs the Log Reader Agent. If we want to change the -ReadBatchSize of our Log Reader Agent to 1000, we change the command being executed to be the same as the command in Listing 16-25.

-Publisher [WIN-KIAGK4GN1MJPROSQLADMIN] -PublisherDB [Chapter16] -Distributor [WIN-KIAGK4GN1MJPROSQLADMIN] -DistributorSecurityMode 1  -Continuous -ReadBatchSize 1000

Applying a set of transactions on the subscriber has a fixed overhead. Therefore, if you increase the number of transactions being committed in a single batch, you can improve the overall performance of the synchronization. By default, 100 transactions are committed as a single batch. We can increase this value by using the -CommitBatchSize property of the Distribution Agent.

You can change the -CommitBatchSize of the Distribution Agent. To do so, we add the -CommitBatchSize switch to the command in the second job step of the SQL Server Agent job that runs the Distribution Agent. If we want to change the –CommitBatchSize switch of the Distribution Agent that we are using to synchronize the Chapter16_Customers_Filtered publication so that 250 transactions are grouped together, we can change the command being executed to match the command in Listing 16-26.

-Subscriber [WIN-KIAGK4GN1MJPROSQLADMIN2] -SubscriberDB [Chapter16_Sub] -Publisher [WIN-KIAGK4GN1MJPROSQLADMIN] -Distributor [WIN-KIAGK4GN1MJPROSQLADMIN] -DistributorSecurityMode 1 -Publication [Chapter16_Customers_Filtered] -PublisherDB [Chapter16]    -Continuous -CommitBatchSize 250

We can further improve the performance of the Distribution Agent by allowing the subscriptions to be updated in parallel. By default, only a single connection is made to the subscriber, in order to apply changes. If we allow multiple connections, however, then we can greatly increase throughput.

To enable parallel updates, we need to set the -SubscriptionStreams parameter of the Distribution Agent. If we want to allow four parallel connections, for example, we can update the command run by the second job step of the SQL Server Agent job running the Distribution Agent to mirror the command in Listing 16-27.

-Subscriber [WIN-KIAGK4GN1MJPROSQLADMIN2] -SubscriberDB [Chapter16_Sub] -Publisher [WIN-KIAGK4GN1MJPROSQLADMIN] -Distributor [WIN-KIAGK4GN1MJPROSQLADMIN] -DistributorSecurityMode 1 -Publication [Chapter16_Customers_Filtered] -PublisherDB [Chapter16]    -Continuous -CommitBatchSize 250 -SubscriptionStreams 4

Adding AlwaysOn Readable Secondary Replicas

It can be very useful to add readable secondary replicas to an AlwaysOn Availability Group topology in order to implement vertically scaled reporting. When you use this strategy, the databases are kept synchronized, with variable, but typically low latency, using log streaming. The additional advantage of readable secondary replicas from SQL Server 2014 onward is that they stay online, even if the primary replica is offline. The limitation here, however, is that users must connect directly to the instance, as opposed to the Availability Group Listener.

You can further improve read performance in readable secondary replicas by using temporary statistics, which you can also use to optimize read-only workloads. Also, snapshot isolation is also used exclusively on readable secondary replicas, even if other isolation levels or locking hints are explicitly requested. This helps avoid contention, but it also means that TempDB should be suitably scaled and on a fast disk array.

The main risk of using readable secondary replicas is that implementing snapshot isolation on the secondary replica can actually cause deleted records not to be cleaned up on the primary replica. This is because the ghost record cleanup task only remove rows from the primary once they are no longer required at the secondary. In this scenario, log truncation is also delayed on the primary replica. This means that you potentially risk having to kill long-running queries that are being satisfied against the readable secondary. This issue can also occur if the secondary replica becomes disconnected from the primary. Therefore, there is a risk that you may need to remove the secondary replica from the Availability Group and subsequently re-add it.

To make a secondary replica readable, you need to perform three tasks. First configure the secondary replica to allow read-only connections. Second, specify a read-only URL for reporting. The Availability Group Listener then directs appropriate traffic to this URL. The final task is to update the read-only routing list on the primary replica. These tasks are performed by the script in Listing 16-28.

--Configure the ASYNCDR Replica to allow read-only connections

ALTER AVAILABILITY GROUP App1
 MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)) ;

--Configure the read-only URL for the ASYNCDR Replica

ALTER AVAILABILITY GROUP App1
 MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://CLUSTERNODE3.PROSQLADMIN.com:1433’)) ;

--Configure the read-only routing list on the Primary Replica

ALTER AVAILABILITY GROUP App1
MODIFY REPLICA ON N’CLUSTERNODE1PRIMARYREPLICA’ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(’CLUSTERNODE3ASYNCDR’))) ;

Summary

Database snapshots use copy-on-write technology to create a point-in-time copy of a database. The snapshot must exist on the same instance as the source database, so although you cannot use them to distribute load between servers, you can use them to reduce contention between read and write operations.

Snapshots can be used to recover data that has been lost due to human error as well as for reporting purposes. You can either copy the data back to the source database, or you can restore the source database from a snapshot, as long as it is the only snapshot linked with the source database. Snapshots do not offer a protection against failure or corruption, however, and they are not a suitable alternative to backups.

Replication is a suite of technologies, offered by SQL Server, that allow you to distribute data between systems. For the purpose of scaling workloads, transactional replication is the most appropriate choice. Transactional replication can be implemented by configuring a distributor, which will hold replication metadata and potentially take the weight off the synchronization; a publisher, which hosts the data that is synchronized; and subscribers, which are the targets for synchronization. Replication is a complex technology, and as a DBA, you should understand how to implement it using T-SQL, as well as the GUI, since you will encounter situations in which you need to tear down and rebuild replication. Replication also exposes the RMO (Replication Management Objects) API, which is a replication programming interface for .NET.

You can configure readable secondary replicas within an AlwaysOn Availability Group topology; these allow for vertical scaling of read-only workloads. From SQL Server 2014 on, readable secondary replicas stay online even if the primary replica goes offline. The caveat here is that connections must be made directly to the instance.

In order to implement readable secondary replicas, you must configure read-only routing. This involves giving the secondary a URL for read-only reporting and then updating the read-only routing list on the primary replica. The risk of using this strategy for scale-out reporting is that long-running transactions at the secondary, or the secondary becoming disconnected, can lead to log truncation delays and delays in ghost records being cleaned up.