This page describes how to set up a Cloud SQL instance to publish to asubscriber that is external to Cloud SQL or that is withinCloud SQL. External replication in Cloud SQL for SQL Server usestransactional replication, in which Cloud SQL acts as a publisher to asubscriber.

Transactional replication supports publishing several types of objects, asdocumentedby Microsoft. Additionally, in Cloud SQL, limitations on this feature aresimilar to thosedocumentedby Microsoft.

Some of the supported objects are the following:

• Tables
• Stored Procedures
• Views
• Index views
• User Defined Functions

Although examples of the publication process are on this page, seePublish Data and Database Objectsin the Microsoft documentation for details. Consider usingSQL Server Management Studio (SSMS)in your publication process, as your available options may be more apparent inSSMS.

Also seeAbout replication in Cloud SQL.

Implementing transactional replication

A Cloud SQL instance can act as a publisher and distributor for anexternal subscriber, through transactional replication.

To set up transactional replication, you can:

• Use stored procedures provided by Cloud SQL. These are prefixed by:gcloudsql_transrepl_
• Refine replication using stored procedures provided by Microsoft

Limitations and prerequisites

As you plan for transactional replication, review this section.

Limitations

• Instances that use high availability (HA) lack a consistent, outgoing IP address. Such instances cannot be publishers if they utilize public IP connectivity. Thus, if an instance uses HA, it must use Private IP connectivity.
• For defining an article, seeDefine an Article in the Microsoft documentation, including for the limitations and restrictions.
• The stored procedures provided by Cloud SQL support push subscription only.
• External replication can't be configured on an instance configured withreplication.

Prerequisites

Bi-directional network connectivity must be set up between a Cloud SQLinstance and the subscriber instance. The subscriber can be external as in, forexample, an on-premises subscriber, or the subscriber can be internal toCloud SQL.

For Cloud SQL instances that use public IPs, Cloud SQL uses a different IPaddress on its egress and ingress paths. The replica must allow-list the primaryinstance's outgoing IP address, which you can retrieve using thegcloudcommand:

gcloud sql instances describe [PRIMARY_NAME] --format="default(ipAddresses)"

To use Cloud SQL private IP with an on-premises instance, you must set upprivate services access. This requires peering between the Cloud SQL VPCand customer's VPC over a custom IP range that needs to be advertised.

When connecting from on-premises, the on-premises firewall must allow inbound andoutbound connections. Specifically, the on-premises firewall must allow suchconnections on port 1433 to the private services access subnet range that'sused for the specific Google Cloud service (in this case, for Cloud SQL).Consider allowing a subnet range rather than a specific IP for each instancecreated.

For related information, see the following:

• Configure private IP(which includes a link for setting up private services access)
• private services access
• Configuring private services access

Permissions and roles

The following sections cover permissions and roles.

Packaged permissions

The stored procedures provided by Cloud SQL include the permissions neededfor transactional replication. These are wrapper stored procedures thatsometimes call one or more Microsoft stored procedures. See theMicrosoft documentationfor information about the Microsoft stored procedures.

Required role

The accounts utilized (including for the log reader agent) need adb_ownerrole, as in the case of thesqlserver user. For necessary information, reviewtheReplication Agent Security Model.

Using stored procedures for publishing, distributing, and subscribing

This section contains suggested steps for setting up transactional replication.

Thesqlserver user has stored procedures to set up your Cloud SQLinstance to act as a publisher. For reference information, seeCloud SQL stored procedures.

Preparing for transactional replication with a test database

Before setting up transactional replication for a production database, you canset up your instance as a publisher of test database objects. On this page, thetest database is called thepub_demo database.

Connect to your Cloud SQL instance using thesqlserver user and create adatabase for testing purposes. For example:

Create Database pub_demo;GOUSE pub_demo;CREATE TABLE Employee(employeeId INT primary key);INSERT INTO Employee([employeeId]) VALUES (1);INSERT INTO Employee([employeeId]) VALUES (2);-- Add procedureCREATE OR ALTER PROCEDURE dbo.p_GetDateASSELECT getdate()-- Add viewCREATE OR ALTER VIEW dbo.v_GetDbsASSELECT name from sys.databases-- FunctionCREATE OR ALTER FUNCTION dbo.fn_ListDbFiles(@id int)RETURNS TABLEASRETURN(select * from sys.master_files where database_id = @id)

Set up the distribution database

For the distribution database, you can usemsdb.dbo.gcloudsql_transrepl_setup_distribution, which is a wrapper storedprocedure for these Microsoft stored procedures:

• sp_adddistributor
• sp_adddistributiondb
• sp_adddistpublisher

For example:

EXEC msdb.dbo.gcloudsql_transrepl_setup_distribution @login='sqlserver', @password='<password>'

Enable a database for publication

To enable or disable the publishing option of a database, you can usemsdb.dbo.gcloudsql_transrepl_replicationdboption. This stored procedureapplies to the publishing option for the publisher that usessp_replicationdboption.

For example:

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption @db='pub_demo', @value='true'

Add a log reader agent

You can set up a log reader agent for a database that usessp_addlogreader_agent.

For example:

EXEC msdb.dbo.gcloudsql_transrepl_addlogreader_agent @db='pub_demo', @login='sqlserver', @password='<password>'

Create the publication for the database

You can usemsdb.dbo.gcloudsql_transrepl_addpublication to create atransactional publication for the database you specify. This stored procedurewrapssp_addpublication.

For example:

EXEC msdb.dbo.gcloudsql_transrepl_addpublication @db='pub_demo', @publication='pub1'

Create a snapshot agent for the specified publication

To create a snapshot agent for the publisher database, you can usemsdb.dbo.gcloudsql_transrepl_addpublication_snapshot, which wrapssp_addpublication_snapshot.

For example:

EXEC msdb.dbo.gcloudsql_transrepl_addpublication_snapshot @db='pub_demo',  @publication='pub1', @login='sqlserver', @password='<password>'

Create an article and add it to the publication

You can create an article from within the publisher database and add it to thepublication. As thesqlserver user, utilizesp_addarticle.

You also may add articles using SSMS. For more information, seeAdd Articles to and Drop Articles from a Publication.

For example:

USE pub_demo;GOEXEC sp_addarticle @publication = 'pub1',                   @article = 'csql_dbo.employee',                   @source_owner = 'dbo',                   @source_object = 'Employee',                   @description = N'cloudsql_article_table',                   @schema_option = 0x000000000903409D,                   @identityrangemanagementoption = N'manual',                   @destination_table = 'Employee',                   @destination_owner = 'dbo';-- add functionuse [pub_demo]exec sp_addarticle  @publication = N'pub1',                   @article = N'fn_ListDbFiles',                   @source_owner = N'dbo',                   @source_object = N'fn_ListDbFiles',                   @type = N'func schema only',                   @description = N'',                   @creation_script = N'',                   @pre_creation_cmd = N'drop',                   @schema_option = 0x0000000008000001,                   @destination_table = N'fn_ListDbFiles',                   @destination_owner = N'dbo',                   @status = 16-- add procedureuse [pub_demo]exec sp_addarticle  @publication = N'pub1',                   @article = N'p_GetDate',                   @source_owner = N'dbo',                   @source_object = N'p_GetDate',                   @type = N'proc schema only',                   @description = N'',                   @creation_script = N'',                   @pre_creation_cmd = N'drop',                   @schema_option = 0x0000000008000001,                   @destination_table = N'p_GetDate',                   @destination_owner = N'dbo',                   @status = 16-- add viewuse [pub_demo]exec sp_addarticle  @publication = N'pub1',                   @article = N'v_GetDbs',                   @source_owner = N'dbo',                   @source_object = N'v_GetDbs',                   @type = N'view schema only',                   @description = N'',                   @creation_script = N'',                   @pre_creation_cmd = N'drop',                   @schema_option = 0x0000000008000001,                   @destination_table = N'v_GetDbs',                   @destination_owner = N'dbo',                   @status = 16

Add the subscription to the publication

From within the database, you can add the subscription to the publication. Asthesqlserver user, set the subscriber status by usingsp_addsubscription.

For example:

Use pub_demo;GOEXEC sp_addsubscription @publication ='pub1',                        @subscriber = N'10.10.100.1,1433',                        @destination_db = pub_demo,                        @subscription_type = N'Push',                        @sync_type = N'automatic',                        @article = N'all',                        @update_mode = N'read only',                        @subscriber_type = 0

Connect to the subscriber and create a subscription database

You can connect to the subscriber and create a subscription database forreplicated data to populate.

For example:

 Create Database pub_demo

Add a new scheduled agent job to synchronize the push subscription

You can add a new scheduled agent job to synchronize the push subscription tothe publication. For example, on the publisher database, run a command similarto the one below. This command usesmsdb.dbo.gcloudsql_transrepl_addpushsubscription_agent, a wrapper storedprocedure forsp_addpushsubscription_agent:

EXEC msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent@db='pub_demo',@publication = 'pub1',@subscriber_db= 'pub_demo',@subscriber_login='sqlserver',@subscriber_password='<password>',@subscriber='11.11.111.1,1433'

Start a publication snapshot agent job

You can start a publication snapshot agent job, as follows:

USE pub_demo;EXEC sp_startpublication_snapshot@publication = 'pub1'

Grant access to an account to use the Replication Monitor

Usemsdb.dbo.gcloudsql_transrepl_addmonitoraccess to:

• Provide access to the Replication Monitor in SSMS
• Query tables in the distribution database

Thus, this stored procedure lets you use the SELECT statement on thedistribution database's replication-related tables, such as theMSrepl_errorstable:

EXEC msdb.dbo.gcloudsql_transrepl_addmonitoraccess@login = 'sqlserver'

Change a distribution database property

You can change theheartbeat_interval. Use themsdb.dbo.gcloudsql_transrepl_changedistributor_property procedure, which wrapssp_changedistributor_property.

For more information, see thedocumentationforsp_changedistributor_property. Also see that documentation for moreinformation about theheartbeat_interval value.

For example:

EXEC msdb.dbo.gcloudsql_transrepl_changedistributor_property@property = N'heartbeat_interval',@value = 90

Use the Replication Monitor

Right-click the replication node in SSMS and chooseLaunch ReplicationMonitor.

If you click the Agents tab, you should see a view similar to the following:

Using stored procedures for removing replication

This section contains suggested steps for removing transactional replication.

Drop the subscription

To drop the subscription, use thesp_dropsubscription stored procedure.

Here is an example of the commands to drop the subscription:

USE  pub_demo;GOEXEC sp_dropsubscription          @publication = 'csql_pub_pub_demo',          @article     = N'all',          @subscriber  = N'11.11.111.1,1433'

EXEC msdb.dbo.gcloudsql_transrepl_dropsubscriber @subscriber  = N'11.11.111.1,1433'

EXEC msdb.dbo.gcloudsql_transrepl_droppublication  @db = 'pub_demo', @publication='pub1'

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption@db='pub_demo',@value=N'false'

EXEC msdb.dbo.gcloudsql_transrepl_remove_distribution

Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation.

This database is not enabled for publication.

USE <database_name>;GO<Run stored proc>

select * from [cloudsql_distribution]..msrepl_errorsselect * from [cloudsql_distribution]..MSreplication_monitordata

Microsoft has moreexamplesabout looking up errors using the Replication Monitor. For example, thesqlserver user may lack access to a command.

Replication agent jobs

After replication is set up, the new SQL agent jobs aren't visible in SSMS tothesqlserver user. However, you can view them using the following:

USE msdbselect * from dbo.sysjobs

Publishers missing in the Replication Monitor

You can use theReplication monitorto view the status of replication and to troubleshoot replication issues.

For example, when you set up replication and your publisher Cloud SQLinstance uses an IP address, SSMS may be unable to find the publisher. This isbecause it does not know the mapping between the hostname and the IP address.

The Replication Monitor contains an empty Publications tab:

As a workaround, you cancreate aliasesin theSQL Server Configuration Managerbetween the SQL Server hostname of the publisher and the IP address used toconnect from SSMS:

1. Start the SQL Server Configuration Manager.

   

2. Find theAliases node and select it.

   

3. Right-click the box underAlias name to create a new alias. The sameprocedure applies for both a 32-bit alias and a 64-bit alias:

   

4. Retrieve the actual hostname of your publisher instance using this query:

   

5. In the alias window, enter these fields before selectingOK:

   Alias Name: Provide the server name from the query in Step 4.

   Port No: Provide port 1433.

   Protocol: Leave the value as the default of TCP/IP.

   Server: Provide the IP address of the Publisher instance.

   

6. Connect using the new alias, and launch the Replication Monitor:

   

The publishing information should be similar to the following:

For more information about troubleshooting replication, seeTroubleshooter: Find errors with SQL Server transactional replication.

Estimating the size of articles needed for replication

When you use a Cloud SQL instance as a publisher, an initial snapshot ofthe articles to be generated is necessary to begin replication. This snapshot isstored locally. Depending on the number of articles, their size, and the typeof data, the storage requirements may increase. Thesp_spaceused storedprocedure provides only a rough estimate of the disk space needed for anarticle.

The snapshot includes files that store schemas and data.

What's next

• Learnabout replication in Cloud SQL.