Content resolution for synchronizing two SQL Server databases

1. Publisher, Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder
My computer
Control Panel
Management tools
Computer Management
Users and Groups
Right-click User
New user
Create a user who is logged on to Windows in the group of Administrators

2. At the publisher, create a new shared directory as the repository for the published snapshot files:

My computer
D: Create a new directory, called: PUB
Right key to this new directory
Property sharing
Select "Share this Folder"
Set specific user rights by Newlay with permissions to ensure that the user created in the first step has all permissions on the folder
Are you sure

3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)

Start Program management tool Services
Right-SQLServerAgent
Property Login Select "This account"
Enter or select the Windows logon user name created in the first step
Enter the user's password in the password

4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)

Enterprise Manager
Right-key SQL instance Properties
Security Authentication
Select SQL Server and Windows
Are you sure

5. Registering with each other at the Publisher and Subscriber
Enterprise Manager
Right-click SQL Server Group
New SQL Server Registration ...
Next available server, enter the name of the remote server you want to register to add
Next connection use, select the second SQL Server authentication
Next enter user name and password
Next select the SQL Server group, or you can create a new group
Next complete

6. Register a server alias for a computer name that can only be used for IP
(at the end of the connection configuration, for example, at the Subscriber, the server name is entered in the IP of the publisher)
Start Program Microsoft SQL Server Client Network Utility
Alias add
Network library Select "TCP/IP" server alias enter SQL Server name
Enter the SQL Server IP address in the connection parameter server name
If you have modified the SQL port, deselect "Dynamic decision port" and enter the corresponding port number

SQL Database Tutorial Synchronous replication

I. Establish a publishing and distribution server

[Welcome to the Configure Publishing and Distribution Wizard]->[Select Distributor]

->[makes "@servername" its own distributor, SQL Server creates distribution databases and logs]

->[make a snapshot folder]-> [custom Configuration]-> [No, use the following default configuration]-> [finish]

Once the above steps are complete, a distribion library and a

A distributor_admin administrator-level user (we can modify the password arbitrarily)

Four new jobs were added to the server:
[Agent History cleanup: distribution]
[Distribution cleanup: distribution]
[Replication agent Check]
[Reinitialization of subscriptions with data validation failures]

SQL Server Enterprise Manager has one more Replication Monitor, the current machine can be published, distributed, subscribed.

We are again in SQL Server Enterprise Manager [Copy]-> right-click to select
->[Configure Publishing, subscribers, and distribution], you can see a similar figure:

We can at the Publisher and Distributor Properties window
-> [Publisher]-> [Add]-> [OK]
-> [Publish Database]-> [affairs]/[merge]-> [OK]
-> [subscriber]-> [Add]-> [OK]
Add other SQL Server servers on your network as publications or subscribers.
Add an option for a publisher:

The new JIN001 Publisher I built here is connected to the administrator-level database user test.
Admin link to Publisher to enter an optional box for the password, the default is selected,
You need to enter the password for the distributor_admin user when you establish and distribute a link to the server Fengyu/fengyu at the new JIN001 publisher
Admin link to Publisher to enter the optional box for the password, you can also not select the
That is, you don't need a password to establish a link to the Distributor (which, of course, is not secure and can be used in a test environment).

Second, another publisher on the newly established network (for example, JIN001) Select a Distributor

[Welcome to the Configure Publishing and Distribution Wizard]->[Select Distributor]

-> Use the following servers (the selected server must already be configured as a distributor)-> [selected servers] (for example, Fengyu/fengyu)

-> [Next]-> [input distributor (for example, Fengyu/fengyu) distributor_admin user password two times]

-> [Next]-> [custom Configuration]-> [No, use the following default configuration]

-> [Next]-> [complete]-> [OK]

To establish a database replication publishing process:

[Copy]-> [publish content]-> right click-> [New Publication]

-> [Next]-> [select Publication database]-> [select a database to publish]

-> [Next]-> [select publication type]-> [transactional release] [merge publication]
-> [Next]-> [specify subscriber type]-> [server running SQL Server 2000]

-> [Next]-> [specify items]-> [only tables with primary keys can be published in transactional publications]-> [select a table with primary key to publish]

->[a unique identifier field [ROWGUID] for a table in a merge publication that adds a uniqueness index and a ROWGUIDCOL property, and the default value is newid ()]

(Adding a new column will: cause an INSERT statement without a column list to fail, increase the size of the table, and increase the time required to generate the first snapshot)

->[Select a table to publish]

-> [Next]-> [select Publication name and description]->

-> [Next]-> [properties of custom Publication]-> [No, create publication according to specified method]

-> [Next]-> [finish]-> [Close]

There are many useful options in publishing properties: Set subscription expiration (e.g. 24 hours)

Set project properties for publishing tables:

The General window can specify the name of the publication destination table, unlike the original table name.

The following figure is the column of the command and Snapshot window

(SQL Server database replication technology is actually using the insert,update,delete operation to redo transactional operations at the Publisher at the Subscriber

Looking at the documentation requires that the publication database be set to a full recovery model and that the transaction is not lost

But I myself in the test found that the publication database is a simple recovery model, every 10 seconds to generate some large transactions, 10 minutes to shrink the database log,
The jobs on both the publish and Subscriber are paused, and no transaction changes have been lost since the resume was paused

Publishing tables can do data filtering, such as selecting only some of the columns in a table:

For example, to select only certain records that meet the criteria in the table, we can write the filtered SQL statements manually:

Publish the subscription options for the table, and you can establish a push subscription:

A new job was added at the publisher after the publication was successfully established: [Fail-through-subscribe cleanup]

Two new jobs were added to the Distributor:
[jin001-dack-dack-5] Type [REPL snapshot]
[jin001-dack-3] Type [REPL Log Reader]

The name of the blue word above will use a different number depending on the name of the publisher, the name of the publication, and several releases.

The Repl snapshot job is a prerequisite for SQL Server replication, which builds the published table structure, data, indexes, constraints, and so on to the publisher's OS directory files
(Generated when there is a subscription, when the subscription request is initialized or a schedule is scheduled to be generated)

The REPL Log reader is always running when transactional replication. (Can be run according to scheduled schedule when merging replication)

The process of establishing a database replication subscription:

[Copy]-> [subscribe]-> Select-> [new Pull subscription]

-> [Next]-> [find publication]-> [view publications made by registered Servers]

-> [Next]-> [select Publication]-> [select the database publication name that has been established at the publisher]

-> [Next]-> [Specify synchronization Agent login]-> [when an agent connects to a proxy server: Using SQL Server Authentication]
(Enter the distributor_admin username and password at the publisher)

-> [Next]-> [select destination Database]-> [select the database name in which the subscription is created]/[You can also create a new library name]

-> [Next]-> [Allow anonymous subscriptions]-> [Yes, generate anonymous subscriptions]

-> [Next]-> [Initialize subscription]-> [Yes, initialize schema and data]

-> [Next]-> [snapshot delivery]-> [use snapshot files from the default snapshot folder for this publication]
(Subscribers need to be able to access the publisher's Repldata folder, and if there is a problem, you can set up network shares and share permissions manually)

-> [Next]-> [snapshot delivery]-> [use snapshot files from the default snapshot folder for this publication]

-> [Next]-> [set up Distribution Agent Scheduler]-> [use the following schedule]-> [change]-> [For example, schedule every five minutes]

-> [Next]-> [launch required services]-> [This subscription requires running the SQLServerAgent service at the publisher]

-> [Next]-> [complete]-> [OK]

When a subscription is successfully established, a new category is added to the Subscriber (repl-distribution) job (the category is [repl-merge] when merge replication)
It's going to run the database on the schedule we gave you. Synchronous Replication Jobs

Third, the SQL Server replication configuration, may appear abnormal situation of the experimental log:

1. Publisher broken network, SQL Server service shutdown, restart, shutdown, the replication has been set to no significant impact

Distribution and subscriptions receive transactional information that is not replicated during the break

2. Distributor broken Network, SQL Server service shutdown, reboot, shutdown, there are some impact on the replication has been set up

During a break, the publisher's transactions are queued up
(if the option to delete expired subscriptions is set for a longer period of time, the transaction log for the busy publishing database may expand faster),

The Subscriber will retry the server because it cannot access the publisher
We can set the number of retries and retry intervals (the maximum number of retries is 9999, if you retry every minute, you can support about 6.9 days without errors)

The Distributor SQL Server service starts, and after the network is connected, the heap jobs on the publisher will be in chronological order to the subscribing machine:

It takes a long time (actually a insert,update,delete statement that generates all the transactions, executed at the subscriber)
We spent 7 minutes and 28 seconds on the 58 transaction 100,228 commands performed on an ordinary PC.

3. Subscriber broken Network, SQL Server service shutdown, restart, shutdown, the replication has been set up more impact, may need to be reinitialized

Our experimental Environment (subscriber) from 18:46 to the unexpected downtime, the next day 8:40 after the start of the restart,
The replicated copy starts to work again after 8:40, and the stack job at the Publisher will work in chronological order on the subscription machine
However, there is a snapshot error prompt in Replication Manager, the snapshot may need to be restarted, and replication may need to be restarted.
(The machines in our lab environment did not perform snapshot initialization, and the replication was still successful)

Iv. Delete the published and scheduled reading can be deleted directly with the Delete button

We'd better always delete the first reading, then delete the publication, and finally disable the order of the publication to operate.

If you want to completely delete the replication settings above SQL Server, you can do this:

[Copy]-> right-select [Disable publication]-> [Welcome to disable Publishing and Distribution Wizard]

-> [Next]-> [Disable publishing]-> [to disable publishing on @servername]

-> [Next]-> [Completing the Disable Publishing and Distribution Wizard]-> [complete]

We can also use T-SQL command to complete the replication and subscription creation and deletion, select the already set up the publication and subscription, by the right button
You can [generate SQL scripts]. (Here is not detailed, the following recommended site has more detailed content)

When you try to delete or change a table, the following error occurs
Server:msg 3724, level, state 2, line 1
Cannot drop the table object_name because it is being used for replication.

The typical scenario is that the table was used for replication, but then the copy was deleted

Treatment methods:
Copy code code as follows:
SELECT * from sysobjects where replinfo >0
sp_configure allow updates, 1
Go
Reconfigure with override
Go
BEGIN TRANSACTION
Update sysobjects Set replinfo = 0 where replinfo >0
Commit TRANSACTION
Go
ROLLBACK TRANSACTION
Go
sp_configure allow updates, 0
Go
Reconfigure with override
Go

Questions:
After the merge replication configuration is complete, if the synchronization agent stops. I want to restart the synchronization agent for merge replication in my program. What command or stored procedure would you like to use?
Solution:
Sp_start_job
Instructs the SQL Server agent to execute the job immediately.

Example
The following example starts a job named Nightly Backup.

Use msdb
EXEC sp_start_job @job_name = Nightly Backup

2, log restore function 、、、

Copy code code as follows:
Description
The following code demonstrates how to use the log Restore feature to feed data changes in the primary database to the standby database in a timely manner
The data for the standby database can be used for queries at any time, but cannot be updated (the standby database is read-only).

First, create a demo database (the primary database)
CREATE DATABASE Db_test
On
(NAME = Db_test_data,
FILENAME = c:db_test.mdf)
LOG on
(NAME = Db_test_log,
FILENAME = c:db_test.ldf)
Go

To back up a database
BACKUP DATABASE db_test to Disk=c:test_data.bak with FORMAT
Go

Restore the database to an alternate database (demonstrating synchronization between the primary database and this standby database)
RESTORE DATABASE Db_test_bak from Disk=c:test_data.bak
With Replace,standby=c:db_test_bak.ldf
, move Db_test_data to C:db_test_data.mdf
, move Db_test_log to C:db_test_log.ldf
Go

Start the SQL Agent service
EXEC Master.. xp_cmdshell net start Sqlserveragent,no_output
Go

Create a job that synchronizes between the primary server data trainer and the standby server database
DECLARE @jogid uniqueidentifier
EXEC msdb.. Sp_add_job
@job_id = @jogid OUTPUT,
@job_name = N Data Synchronous processing

To create a synchronous processing step
EXEC msdb.. Sp_add_jobstep
@job_id = @jogid,
@step_name = N Data synchronization,
@subsystem = TSQL,
@command = N
Log backups in the primary database
BACKUP LOG db_test to Disk=c:test_log.bak with FORMAT

Log backups of the primary database in the standby database (application of the latest changes in the master database)
The restore job of the primary database backup and the standby database should be established on both the primary and standby servers, and the backup files should be placed on both the primary server and the shared directory accessible by the standby.
RESTORE LOG Db_test_bak from Disk=c:test_log.bak with Standby=c:test_log.ldf,
@retry_attempts = 5,
@retry_interval = 5

Create a schedule (execute once per minute)
EXEC msdb.. Sp_add_jobschedule
@job_id = @jogid,
@name = n time schedule,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type =0x4,
@freq_subday_interval = 1,
@freq_recurrence_factor =1

Add Target server
EXEC Msdb.dbo.sp_add_jobserver
@job_id = @jogid,
@server_name = N (local)
Go

With this process, the synchronization relationship between the primary and standby databases is set to complete
Let's start testing to see if synchronization is possible

To create a test table in the primary database
CREATE TABLE Db_test.dbo.TB_test (ID int)
Go

Wait for 1分钟30秒 (because the synchronization time interval is set to 1 minutes, so you have to delay to see the effect)
WAITFOR DELAY 00:01:30
Go

Check the standby database to see if synchronization is successful
SELECT * from Db_test_bak.dbo.TB_test

Results:
Id
-

(The number of rows affected is 0 rows)

Test successful
Go

Finally delete all the tests
DROP DATABASE Db_test,db_test_bak
EXEC msdb.. Sp_delete_job @job_name =n Data Synchronous processing
Go

===========================================================

Server file processing instructions
Database synchronization established in this manner, when the primary database is unavailable (for example, primary database corruption or downtime maintenance)
You can use the following two methods to make an alternate database available.

1. If the primary database is corrupted and the most recent log cannot be backed up, you can use the following statement to make the standby database writable (missing all data since the most recent log restore).
RESTORE LOG Db_test_bak with RECOVERY

2. If the primary database can back up the most recent logs, you can use the following statement.
Back up the latest transaction log for the primary database first
BACKUP LOG db_test to Disk=c:test_log.bak with FORMAT
Restore the most recent transaction log in the standby database and make the standby database readable and writable (upgrade to the primary database)
RESTORE LOG Db_test_bak from Disk=c:test_log.bak

To put it simply:
1. Your SQL service should log on using the specified Windows user, not the Local system account
2. Users who log on to the SQL service require all permissions on the shared directory
3. If your computer is not joined to the domain, you must also ensure that the source and destination server's SQL Service settings login user is the same (username and password are the same)

Network backup is mainly the issue of permission settings, refer to the following backup file share directory permissions to set the way to solve the directory share permissions on it

The following assumption assumes that the database on server A is backed up to the shared directory permissions setting on the B server (two servers should be within the LAN, allowing directory share access):

1. Machine a,b Create a Windows user with the same name, set the user group to administrators, and set the same password as a valid access user for the Backup folder folder, action:
My computer
Control Panel
Management tools
Computer Management
Users and Groups
Right-click User
New user
Create a user who is logged on to Windows in the group of Administrators

2. On the B machine, create a new shared directory, as a repository of backup files, operation:
My Computer D: Create a new directory named: BAK
Right key to this new directory
Property sharing
Select "Share this Folder"
Set specific user rights by Newlay with permissions to ensure that the user created in the first step has all permissions on the folder
Are you sure

3. Set up the MSSQLServer and SQLServerAgent services to start the user
Start Program management tool Services
Right-MSSQLServer
Property Login Select "This account"
Enter or select the Windows logon user name created in the first step
Enter the user's password in the password
Are you sure
The same method sets the SQLServerAgent

4. Complete the mapping of B machine Bak catalogue on a machine

5. The following statement is executed in the Query Analyzer to verify the success:
EXEC master.. xp_cmdshell DIR-Mapped disk character

Backup schedule on 6.A server

Note: Creating a new user is just to have the startup account of the MSSQLServer service with the same name as the valid access to the shared directory and the password is the same, so that you can pass validation (so you could also replace it with another valid user, with the same username and password and sufficient permissions)