SQL Server Distributed database Technology (LINKEDSERVER,CT,SSB)

SQL Server custom data synchronization for business functions

Driven by different business requirements, the modular split of the database will face some special business logic processing requirements. For example, data synchronization requirements at the database level. During the synchronization process, there may be some more complex business logic judgments. Provides a brief introduction to the data synchronization capabilities provided by several SQL Servers.

1. Linked Services ( Linked Server )

By linking the database, you can implement data access and update operations between different instances. Typically used with the OPENQUERY rowset function to avoid interference with distributed transactions. It is not recommended to use linked services directly for remote Data update operations, as this requires transaction management to the distributed database. Distributed transactions for SQL Server require the management and coordination of different servers, or resource and transaction tuning between different database instances, through the Windows DTC (distributed Transaction Controller, distributed transaction Controllers). Its performance with ordinary transaction management into a geometric doubling of growth.

10-4, the page is the configuration interface of the linked service and can be configured via the Linked Servers visualization page in SSMs's server objects.

Figure 11-4 Linked services for a database

Provider is the type of adapter used when a linked server connects to a database, 11-4, the left section, which lists the types of adapters that are already on several toes. example, a database connection is made using an adapter of type SQLOLEDB.

The Security page is used to configure validation information for the linked server, which includes the authentication methods for the 4 modes shown in 11-5.

Not being made

When this authentication mode is selected, the linked service will use the login user of the local service and the remote service to log in the user's mapping configuration list of accounts. , when this mode is selected, the permission configuration for the SA account of the remote service is simulated locally only when the sa login is used.

Without using a Security context

Selecting this mode will not use authentication mode, it only takes advantage of the SQL Server service's login account for authentication, which requires the local service and remote service to use the same login account when the Windows service starts.

Using Current security context

When using this mode, both local and remote services are required to have the same account and password, which is usually used when Windows integrated authentication is configured.

Using This security context

When this mode is used, the remote service is logged on using the user and password configured below.

The brief steps for these configurations are briefly described in this section, and for more detailed information, refer to SQL Server Books Online (http://technet.microsoft.com/zh-cn/library/ff772782.aspx).

Figure 11-5 Linked server Security Configuration

1. Change Tracking ( Change Tracking )

Change tracking is a lightweight data modification recording feature added by SQL Server 2008, which is a reduced version of the data Change capture feature. It can record the primary key of the modified data in the corresponding view, and then access the view through the system function to obtain the corresponding change data. By changing the record of the data, the complex business logic can be processed incrementally, and then the data results are saved to the target database.

The sample code, as shown in Listing 11-1, turns on change tracking as configured by the table, but before you configure change tracking for the table, you need to turn on the change Tracking option for the database, and when the database change tracking option is turned on, the change tracking record is retained for 2 days by default and the option to turn on automatic cleanup is turned on.

Change tracking can be traced to a specific field change configuration, as shown in code listing 11-1, the track_columns_updated configuration, when the on state is selected, the Change Tracking Modified field is recorded, and when off, it is not logged.

Use master

GO

ALTER DATABASE [adventureworks2008r2] SET change_tracking = on (change_retention = 2 Days,auto_cleanup = ON);

GO

Use ADVENTUREWORKS2008R2

GO

ALTER TABLE person.businessentity ENABLE change_tracking with (track_columns_updated = on);

GO

?

UPDATE TOP (person.businessentity)

SET modifieddate=modifieddate;

GO

?

SELECT *

From changetable (changes person.businessentity,0) as O;

GO

?

Code Listing 11-1 setting change tracking

Figure 11-6 Change tracking query results

As shown in execution result 11-6 in Listing 11-1, the change history of the corresponding table can be obtained using changetable, and the change history will record the primary key of the original table, as shown in the BusinessEntityID field in 11-6.

For more information about change tracking, you can refer to SQL Server Books Online.

1. Service Broker

Service Broker is a SQL Server-brought Message Queuing mechanism that enables communication between data instances and instances through Service Broker, as well as messaging mechanisms for DB instances and applications.

At the same time, Service Broker is implemented by the queue mechanism, which can guarantee the order of execution of messages, and for data synchronization with transactional requirements, Service Broker will be an ideal data synchronization implementation.

As shown in Listing 11-2, the synchronization configuration of Service Broker under the same instance is configured. The configuration consists of creating a message type (msg type), creating a message rule (contract), queuing (queue), and service (Server). As shown in the hierarchy 11-7, first, the message is stored in the queue, each queue requires a unique service counterpart, and the service becomes the corresponding identity. When communicating between services and services, you need to specify the same, mutually identifiable message rules that specify the type of the corresponding message traffic. The process of its work, as shown in 11-8.

Figure 11-7 Component composition of Service broker

Figure 11-8 How Service Broker Works

Before a queue transfer is required, a session (conversation) needs to be opened, the corresponding service identity is logged through the session, and the identity is sent from the source service to the target service. Once the session ID is found, the corresponding queue can be found.

After the session is opened, the message is sent, and for the DB instance, it is only possible to write the message to the send queue. After the message is written to the send queue, subsequent work is given to Service Broker for processing.

When a message enters the send queue, Service Broker locates the target service based on the service identity of the session record, splits the message into multiple message fragments, sends the message to the target service, and writes the message to the destination queue after the service receives all the messages. After writing to the destination queue, the message delivery is over. The follow-up work will be handed over to the application.

The application needs to invoke the command that receives the message, take the message out of the receive queue, and perform a series of subsequent business work.

For Service Broker, refer to SQL Server Books Online (http://technet.microsoft.com/zh-cn/library/ms166104 (v=sql.105). aspx).

The following code is a script for Service Broker configuration and testing under the same instance, and you can refer to the code and refer to how Service Broker works in conjunction with how it works in 11-8.

Use master

Go

ALTER DATABASE ADVENTUREWORKS2008R2 set Enable_broker with rollback immediate;

Go

?

Use ADVENTUREWORKS2008R2

Go

?

Create message type Receivemsgtype validation = none;

Create message type Sendmsgtype validation = none;

?

Go

Create Contract Samplecontract (Sendmsgtype sent by Initiator,receivemsgtype sent by Target,fraudendofstream sent by Initi Ator);

Go

?

Create Queue Sampletargetque;

Create service Sampletargetsrv on queue sampletargetque (samplecontract);

Go

?

Create Queue Sampleinitque;

Create service Sampleinitsrv on queue sampleinitque (samplecontract);

Go

?

/********************************send test********************************************

Declare @handle uniqueidentifier, @msg varchar (8000) = ' This is a test message! ';

?

Begin dialog Conversation @handle

From service Sampleinitsrv

To service ' Sampletargetsrv '

On contract samplecontract

with encryption = off;

?

Send on Conversation @handle

Message type Sendmsgtype (@msg)

?

**********************************************************************************/

?

/*****************************receive test*************************************************

DECLARE @receivemsg varchar (8000), @Handle uniqueidentifier;

WAITFOR (Receive Top (1) @Handle = Conversation_handle,

[email protected] = Message_body

???????????? From Sampletargetque), timeout 1000

End conversation @handle;

Select @receivemsg;

?

**********************************************************************************/

Code Listing 11-2 Service Broker configuration under the same instance

SQL Server Distributed database Technology (LINKEDSERVER,CT,SSB)