SQL Server real-time synchronization updates problems with remote databases

Tag: Server database trigger record account

In this case, you need to update the TableB (the library ServerB in Server 172.16.8.101 databaseb) at the same time as the Update table TableA (library databasea in Server ServerA 172.16.8.100).

TableA is the same as the TABLEB structure, but the amount of data is not necessarily the same, it should be possible to TableC also update TableB. Because the data is not updated frequently, it is easy to think of using the trigger Tirgger. Take note of some of the problems encountered:

1. Accessing an offsite database

Create a linked server in ServerA that points to ServerB, and do the account mapping. Addlinkedserver stored procedure to create a linked server, see the official documentation for parameter details. The 1th parameter Lnk_servera is a custom name, the 2nd parameter product name, if SQL Server is not provided, the 3rd parameter is the driver type; The 4th parameter is the data source, where the SQL Server server address is written

exec ' Lnk_serverb_databaseb ',' ','sqlncli','172.16.8.101' 

After the linked server is configured, the remote database is logged on by default using the same local account, and account mapping is required if the account is different. See the official documentation for sp_addlinkedsrvlogin parameter details. The 1th parameter is the same as the 2nd parameter false is the user password provided by the following parameters to login; The 3rd parameter null enables all local accounts to log on to the linked server using the following user password, if the 3rd parameter is set to a local SQL Server login user name, Then only this user can log on to the linked server using the remote account, and the last two are the users and passwords that log on to the remote server.

exec ' Lnk_serverb_databaseb ','false',null,'user',' Password '

If you want to remove the above configuration, you can

exec ' Lnk_serverb_databaseb ',nullexec'lnk_serverb_databaseb','  Droplogins'

The above configuration in SQL Server Management Studio manager under Server Objects LinkedServers can be queried, if all links are normal, you can open the linked server directly on the library table

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; Background-image:none; border-bottom-width:0px; padding-top:0px; padding-left:0px; padding-right:0px; border-top-width:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/91/8A/ Wkiol1j2zt3xmljpaaadt-jvqwk308.png "width=" height= "218"/>

It is important to note that the above two stored procedures can not appear in the trigger code, but in advance in the server ServerA to run the complete configuration, or trigger implicit transaction requirements will be error "the procedure ' Sys.sp_addlinkedserver ' cannot be Executed within a transaction. "

2. Configuring Distributed Transactions

SQL Server triggers are implicitly use transactions, and the linked server is a remote server that requires distributed transactions to be turned on between the local server and the remote server, otherwise it will be reported that the partner transaction manager has disabled its Support for remote/network transactions "error. I open the Distributed Transaction Coordinator in both ServerA and ServerB, and are configured appropriately to support distributed transactions. ServerA and ServerB are Windows Server R2, and other version servers are similar.

(1) First in Services.msc confirmed that distributed Transaction Coordinator has been turned on, other versions of the server does not have to be installed by default, you need to install Windows features the way advanced line the installation of this feature.

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; Background-image:none; border-bottom-width:0px; padding-top:0px; padding-left:0px; padding-right:0px; border-top-width:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/91/8A/wKioL1j2zT3g7AwdAAAhq0ac_ Fo901.png "width=" 767 "height=" "/>"

(2) Locate Component Services in the Server Management tools Administrative Tools, configure the Security tab in the Local DTC, open the relevant safety settings, restart the service after completion, and document the need to restart the server , but at least R2.

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; Background-image:none; border-bottom-width:0px; padding-top:0px; padding-left:0px; padding-right:0px; border-top-width:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/91/8B/ Wkiom1j2zt6xjynzaabv8cmf-6e316.png "width=" 645 "height=" 385 "/>

(3) Configure firewall, inbound and outbound are open

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; Background-image:none; border-bottom-width:0px; padding-top:0px; padding-left:0px; padding-right:0px; border-top-width:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/91/8B/ Wkiom1j2zt7gjfwgaabjsv8sjsa762.png "width=" 712 "height=" 247 "/>

3. Database field text, ntext processing

In business, there is a content field in table TableA that is ntext type, and you need to do some substitution processing when synchronizing to TableB. For the text and ntext types is an obsolete type, Microsoft is officially recommended to use (N) VARCHAR (MAX) replacement, can be consulted here. Future design can be considered, here we consider the processing of ntext.

But in the trigger, the inserted and deleted tables are not allowed to handle the Text/ntext/image type, here we use a curve to the salvation of the method, from the database to read the records into the temporary table, String substitution processing is then done through the TEXTPTR and PATINDEX functions and the UPDATETEXT command

if exists(Select *  fromTempdb.. sysobjectswhereId=object_id('tempdb: #temp_tablea'))     Drop Table#temp_tableaSelect *  into#temp_tablea fromTableAwhereId= @IDDeclare @s varchar( $),@d varchar( $)Select @s='= "/_target/',@d='= "/_replacement/'Declare @p varbinary( -),@postion int,@l intSelect @p=textptr(Content),@l=Len(@s),@postion=Patindex('%'+@s+'%', Content)-1  from#temp_tablea while @postion>0begin    UPDATETEXT#temp_tablea. Content@p @postion @l @d    Select @postion=Patindex('%'+@s+'%', Content)-1  from#temp_tableaEnd

Note that the above code is problematic for the text type when it is processed in Chinese, and because text stores non-unicode data, Patidex interprets the medium character as 1 characters, while the UPDATETEXT command interprets the medium character as 2 characters. SQL Server 2005 or later can be replaced by:

Update Set Content=cast(replace(castasnvarchar(max)),@ S,@dastext)

4. Performing Remote database operations

When we configure the linked server, we can directly access the remote database table, as follows

Insert  into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB ... Update Set ...

But the simple SQL editor tends to bug the syntax, and for the convenience of programming, we want to get more flexibility through the exec sp_executesql approach. In fact, exec can execute the SQL statement directly, but it is more difficult to have a return value. as follows, the return Name,sp_executesql stored procedure from the remote server through ID query table TableB can use the OUTPUT keyword to define the variable as the return variable, where @name output is the return variable, @ID is the passed variable.

Declare @sql nvarchar( -),@Name nvarchar( -),@ID nvarchar( +)Set @SQL=N'Select @Name =name from LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB where [email protected]'execsp_executesql@SQLN'@Name nvarchar () output, @ID nvarchar (+)',@NameOutput@ID

In addition exec executes the SQL statement directly, essentially executes the concatenation of the SQL string, sometimes it is difficult to stitch the variable into the string (in the end need a few single quotes), and sp_executesql is much clearer

Declare @SQL nvarchar( -),@Name nvarchar( -),@Count int,@ID nvarchar( +)Set @Name=N'Cat'Set @Count=0Set @ID=N'{00000000-0000-0000-0000-000000000000}'Set @SQL=N'Update TableA Set name=" "+@Name+" ", count='+@Count+'where id=" "+@ID+" '"exec(@SQL)Set @SQL=N'update TableA Set [email protected],[email protected] where [email protected]'execsp_executesql@SQLN'@Name nvarchar, @Count int, @ID nvarchar (+)',@Name,@Count,@ID

SQL Server real-time synchronization updates problems with remote databases