This page describes how to set up a Cloud SQL instance to publish to a subscriber that is external to Cloud SQL or that is within Cloud SQL. External replication in Cloud SQL for SQL Server uses transactional replication, in which Cloud SQL acts as a publisher to a subscriber.
Transactional replication supports publishing several types of objects, as documented by Microsoft. Additionally, in Cloud SQL, limitations on this feature are similar to those documented by 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, see Publish Data and Database Objects in the Microsoft documentation for details. Consider using SQL Server Management Studio (SSMS) in your publication process, as your available options may be more apparent in SSMS.
Also see About replication in Cloud SQL.
Implementing transactional replication
A Cloud SQL instance can act as a publisher and distributor for an external 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, see Define an Article in the Microsoft documentation, including for the limitations and restrictions.
The stored procedures provided by Cloud SQL support push subscription only.
Prerequisites
Bi-directional network connectivity must be set up between a Cloud SQL instance and the subscriber instance. The subscriber can be external as in, for example, an on-prem subscriber, or the subscriber can be internal to Cloud SQL.
For Cloud SQL instances that use public IPs, Cloud SQL uses a different IP
address on its egress and ingress paths. The replica must allow-list the primary
instance's outgoing IP address, which you can retrieve using the gcloud
command:
gcloud sql instances describe [PRIMARY_NAME] --format="default(ipAddresses)"
To use Cloud SQL private IP with an on-prem instance, you must set up private services access. This requires peering between the Cloud SQL VPC and customer's VPC over a custom IP range that needs to be advertised.
When connecting from on-prem, the on-prem firewall must allow inbound and outbound connections. Specifically, the on-prem firewall must allow such connections on port 1433 to the private services access subnet range that's used for the specific Google Cloud service (in this case, for Cloud SQL). Consider allowing a subnet range rather than a specific IP for each instance created.
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
Packaged permissions
The stored procedures provided by Cloud SQL include the permissions needed for transactional replication. These are wrapper stored procedures that sometimes call one or more Microsoft stored procedures. See the Microsoft documentation for information about the Microsoft stored procedures.
Required role
The accounts utilized (including for the log reader agent) need a db_owner
role, as in the case of the sqlserver user. For necessary information, review
the
Replication Agent Security Model.
Using stored procedures for publishing, distributing, and subscribing
This section contains suggested steps for setting up transactional replication.
The sqlserver user has stored procedures to set up your Cloud SQL
instance to act as a publisher. For reference information, see
Cloud SQL stored procedures.
Preparing for transactional replication with a test database
Before setting up transactional replication for a production database, you can
set up your instance as a publisher of test database objects. On this page, the
test database is called the pub_demo database.
Connect to your Cloud SQL instance using the sqlserver user and create a
database for testing purposes. For example:
Create Database pub_demo;
GO
USE pub_demo;
CREATE TABLE Employee(employeeId INT primary key);
INSERT INTO Employee([employeeId]) VALUES (1);
INSERT INTO Employee([employeeId]) VALUES (2);
-- Add procedure
CREATE OR ALTER PROCEDURE dbo.p_GetDate
AS
SELECT getdate()
-- Add view
CREATE OR ALTER VIEW dbo.v_GetDbs
AS
SELECT name from sys.databases
-- Function
CREATE OR ALTER FUNCTION dbo.fn_ListDbFiles(@id int)
RETURNS TABLE
AS
RETURN
(
select * from sys.master_files where database_id = @id
)
Set up the distribution database
For the distribution database, you can use
msdb.dbo.gcloudsql_transrepl_setup_distribution, which is a wrapper stored
procedure for these Microsoft stored procedures:
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 use
msdb.dbo.gcloudsql_transrepl_replicationdboption. This stored procedure
applies to the publishing option for the publisher that uses
sp_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 uses sp_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 use msdb.dbo.gcloudsql_transrepl_addpublication to create a
transactional publication for the database you specify. This stored procedure
wraps
sp_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 use
msdb.dbo.gcloudsql_transrepl_addpublication_snapshot, which wraps
sp_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 the
publication. As the sqlserver user, utilize
sp_addarticle.
You also may add articles using SSMS. For more information, see Add Articles to and Drop Articles from a Publication.
For example:
USE pub_demo;
GO
EXEC 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 function
use [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 procedure
use [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 view
use [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. As
the sqlserver user, set the subscriber status by using
sp_addsubscription.
For example:
Use pub_demo;
GO
EXEC 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 for replicated 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 to
the publication. For example, on the publisher database, run a command similar
to the one below. This command uses
msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent, a wrapper stored
procedure for
sp_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
Use msdb.dbo.gcloudsql_transrepl_addmonitoraccess to:
- Provide access to the Replication Monitor in SSMS
- Query tables in the distribution database
Thus, this stored procedure enables you to use the SELECT statement on the distribution database's replication-related tables, such as the MSrepl_errors table:
EXEC msdb.dbo.gcloudsql_transrepl_addmonitoraccess
@login = 'sqlserver'
Change a distribution database property
You can change the heartbeat_interval. Use the
msdb.dbo.gcloudsql_transrepl_changedistributor_property procedure, which wraps
sp_changedistributor_property.
For more information, see the
documentation
for sp_changedistributor_property. Also see that documentation for more
information about the heartbeat_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 choose Launch Replication Monitor.
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 the sp_dropsubscription stored procedure.
Here is an example of the commands to drop the subscription:
USE pub_demo;
GO
EXEC sp_dropsubscription
@publication = 'csql_pub_pub_demo',
@article = N'all',
@subscriber = N'11.11.111.1,1433'
Drop the subscriber
To drop the subscriber, use the msdb.dbo.gcloudsql_transrepl_dropsubscriber
stored procedure:
EXEC msdb.dbo.gcloudsql_transrepl_dropsubscriber
@subscriber = N'11.11.111.1,1433'
Drop the publication
To drop the publication, use the msdb.dbo.gcloudsql_transrepl_droppublication
stored procedure:
EXEC msdb.dbo.gcloudsql_transrepl_droppublication
@db = 'pub_demo', @publication='pub1'
Disable the publication database
To disable the publication database, use the
msdb.dbo.gcloudsql_transrepl_replicationdboption stored procedure:
EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption
@db='pub_demo',
@value=N'false'
Remove the distribution database
To remove the distribution database, use the
msdb.dbo.gcloudsql_transrepl_remove_distribution stored procedure:
EXEC msdb.dbo.gcloudsql_transrepl_remove_distribution
Tips and steps for troubleshooting issues
Running stored procedures from the right database
You may get the following error when running sp_addarticle,
sp_addsubscription, or sp_startpublication_snapshot:
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation.
Additionally, you may get the following error when running
sp_dropsubscription:
This database is not enabled for publication.
If these errors occur, note that you must run those stored procedures from within the database for which they apply. You can use the following to confirm they are run from the correct database:
USE <database_name>;
GO
<Run stored proc>
Replication
Replication errors are provided in SQL error logs and elsewhere. You can query some tables in the distribution database directly for replication errors. For example:
select * from [cloudsql_distribution]..msrepl_errors
select * from [cloudsql_distribution]..MSreplication_monitordata
Microsoft has more examples
here
about looking up errors using the Replication Monitor. For example, the
sqlserver 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 to
the sqlserver user. However, you can view them using the following:
USE msdb
select * from dbo.sysjobs
Publishers missing in the Replication Monitor
You can use the Replication monitor to view the status of replication and to troubleshoot replication issues.
For example, when you set up replication and your publisher Cloud SQL instance uses an IP address, SSMS may be unable to find the publisher. This is because 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 can create aliases in the SQL Server Configuration Manager between the SQL Server hostname of the publisher and the IP address used to connect from SSMS:
Start the SQL Server Configuration Manager.
Find the Aliases node and select it.
Right-click the box under Alias name to create a new alias. The same procedure applies for both a 32-bit alias and a 64-bit alias:
Retrieve the actual hostname of your publisher instance using this query:
In the alias window, enter these fields before selecting OK:
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.
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, see Troubleshooter: 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 of
the articles to be generated is necessary to begin replication. This snapshot is
stored locally. Depending on the number of articles, their size, and the type
of data, the storage requirements may increase. The sp_spaceused stored
procedure provides only a rough estimate of the disk space needed for an
article.
The snapshot includes files that store schemas and data.