Replication under multiple instances of SQL Server

Original: Replication under multiple instances of SQL Server

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Build Step (Procedure)
4. Precautions (Attention)

two. Background (contexts)

There is a database task in Server A that requires some of the table partial fields to be published and subscribed to Server B's Tasksiteinfo database, but a server is a bit special because it has a named instance in addition to the default instance: TZR06\SQLSERVER2008R2, If it is the default instance to not have too many problems, now because there is a named instance in the process of creating a publication subscription, there are some exceptions, so this is done as a record;

three. Build Step (Procedure)

(i) Environmental information

System environment: Windows Server R2 + SQL server R2

Publisher: 192.168.100.6,1433, server name: tzr06

Publisher named instance: SQLSERVER2008R2

Publish database: Task

Distribution server: Same machine as publisher

Subscriber: 192.168.100.8,1433, server name: tzr08

Subscription database: Tasksubscribe

Database account Number: Replicationuser/replicationpassword

(ii) Construction process

As mentioned above, the publisher has a default instance and a named instance, the default instance of the database port is 1433, then I disable it, and then set the port of the named instance to 1433, so this need to use the alias to implement the publication subscription.

A. Publisher configuration

First, create the same account and password (Replicationuser/replicationpassword) on the publication database and the subscription database, and set the security object for the task database, and set up such an account so that it is separate from the account that the program connects to the database. Can do the authority on the control, easy to troubleshoot problems;

--Create a Publisher account password Use [Master]GOCREATELOGIN[Replicationuser]  withPASSWORD=N'Replicationpassword', Default_database=[Master], check_expiration=OFF, Check_policy=OFFGOEXECMaster.. Sp_addsrvrolemember@loginame =N'Replicationuser',@rolename =N'sysadmin'GO Use [Task]GOCREATE USER [Replicationuser]  forLOGIN[Replicationuser]GO Use [Task]GOALTER USER [Replicationuser]  withDefault_schema=[dbo]GO

(Figure1: Account password)

In the E-disk directory to create a folder: E:\ReplData, and set this folder for the shared directory, shared users for bfadmin;

(Figure2: Folder permissions)

Need to set the SQL Server Agent login account for the above folder access user bfadmin;

(Figure3:sql Server Agent login account)

(Figure4: Test network share)

(FIGURE5: Distribution server)

If you set the snapshot folder path to: E:\ReplData, even if your publisher is the Distributor itself, if the Subscriber is another machine, The subscription agent will not be able to access the snapshot file in the request (pull) Subscription (if there is no such limit for push subscriptions), unless your Publisher, distributor, and Subscriber are the same machine; you should set the snapshot folder path to: \\tzr06\ Repldata;

(Figure6: Snapshot folder)

(Figure7: Select Publish database)

(Figure8: Transactional release)

(Figure9: Select object)

(Figure10: Select object)

(FIGURE11: Select object)

(Figure12: Initialize subscription)

(Figure13: Security Setting)

Use the Replicationuser account created above as the account number and password to connect to the publisher;

(FIGURE14: Set account password)

(FIGURE15: Publish option)

(Figure16: Publication name)

(Figure17: Viewing replication)

B. Subscriber Configuration

After creating the publisher (the Distributor is also created together), you are ready to create the subscriber, following the specific steps for subscriber settings:

--Create subscriber account password Use [Master]GOCREATELOGIN[Replicationuser]  withPASSWORD=N'Replicationpassword', Default_database=[Master], check_expiration=OFF, Check_policy=OFFGOEXECMaster.. Sp_addsrvrolemember@loginame =N'Replicationuser',@rolename =N'sysadmin'GO Use [Tasksiteinfo]GOCREATE USER [Replicationuser]  forLOGIN[Replicationuser]GO Use [Tasksiteinfo]GOALTER USER [Replicationuser]  withDefault_schema=[dbo]GO

After creating the database account, we then create the subscription, as mentioned above on the publisher has a named instance, so this is to follow TZR06\SQLSERVER2008R2 to set the server name, but during the connection process, the following error occurred:

(Figure18: Find publisher error message)

Using the above configuration at the Subscriber to use "Connect to the server" can also be unable to log on to the publisher, the firewall's inbound rules have been added to allow port 1433, and at the publisher using the Netstat view port, there is also listening, why can not connect it? Later in the "Connect server" Join 1433 is possible to log in, as shown in:

(Figure19: Log on to the publisher)

Using the same method, the Publisher cannot be found, and a new error message appears, as shown in:

(FIGURE20: Find publisher error message)

Since the need to add port number, then we try to use aliases, in the 64-bit operating system, you need to set both 32-bit and 64-bit network configuration, set the alias is: TZR06

(FIGURE21: Alias parameter value)

(figure22:32-bit aliases)

(figure23:64-bit aliases)

(Figure24: Find publisher error message)

Is there a TZR06 conflict? The modified alias is: Tzr06task

(Figure25: Modify 32-bit and 64-bit aliases)

(Figure26: Find publisher error message)

When creating a publication at the publisher, if the SQL Server DB instance name does not match the server name, the above error occurs, so the following SQL statement is executed at the publisher:

/*returns information about the properties of a server instance Windows Server and instance information associated with the specified SQL Server instance*/SELECT CONVERT(Sysname, serverproperty ('servername'));/*returns the name of the local server running SQL Server if the default instance is connected, the @ @SERVERNAME only returns servername If a named instance is connected, the string returned by the @ @SERVERNAME function is servername\ The format of the InstanceName identifies the instance name*/SELECT @ @SERVERNAME  as 'Server Name'

The above results are:

(Figure27: Find publisher error message)

If the two values are different, that can be modified in the following way:

/*Workarounds for SQL Server database instance name inconsistency with server name*/IFSERVERPROPERTY ('SERVERNAME')<>@ @SERVERNAME  BEGIN    DECLARE @serverSYSNAMESET @server=@ @SERVERNAME    EXECSp_dropserver@server=@server      SET @server=CAST(Serverproperty ('SERVERNAME') asSYSNAME)EXECsp_addserver@server=@server,@local='LOCAL'END

Later asked Gao Wenjia, suddenly thought: "At the Distributor and subscribers to set the alias, alias should be consistent with the server instance name to" continue to try, modify the alias: tzr06\sqlserver2008r2

(Figure28: Modify 32-bit and 64-bit aliases)

(Figure29: Select publish)

(Figure30: Pull subscription)

(Figure31: Select subscription database)

(FIGURE32: Distribution Agent Security)

(Figure33: Set account password)

(Figure34: Synchronization Schedule)

(Figure35: Initialize)

(Figure36: Create subscription)

(Figure37: Local subscription)

four. Precautions (Attention)

1. If the snapshot file that you set up at the publisher at the beginning is a local path, such as set to E:\ReplData, the following error may occur:

(Figure38: The system cannot find the path specified)

This time you re-publish the subscription is not the default path can be set, can be modified? I didn't find a place to set it, but I can modify it in a different way, and modify the snapshot path in the publication properties:

(Figure39: Default folder)

(Figure40: Set folder)

To modify the snapshot folder for subscription properties at the Subscriber:

(Figure41: Alternate folder)

2. At the subscriber also need to set the SQL Server Agent login account for the above folder access user Bfadmin, or the following error will appear:

(Figure42: Error message)

You need to restart the SQL Server Agent service after setting up your account

(Figure43: Subscriber SQL Server Agent settings)

3. The subscriber cannot be "reinitialized" at the publisher, reporting the following error message, even if it is set at the publisher:

(Figure44: Error message)

The above error has not been resolved temporarily, but the copy of the named instance has been successful, although successful, but still want to recommend that you try not to install multiple instances in the production environment, to avoid unnecessary problems;