SQL Server synchronizes updates to remote databases in real time.

SQL Server synchronizes updates to remote databases in real time.

In this case, you must update TableB at the same time in the update table TableA (Database DatabaseA in server ServerA 172.16.8.100) (Database DatabaseB in server ServerB 172.16.8.101 ).

TableA has the same structure as TableB, but the data quantity is not necessarily the same. It is possible that TableC is updating TableB. Because the data is not updated frequently, the trigger Tirgger is used for simplicity. Record some problems:

1. Access a remote database

Create a server linked to server B in ServerA and map accounts. The addmediaserver stored procedure creates a linked server. For details about parameters, see the official documentation. The 1st parameter LNK_ServerA is the custom name; the 2nd Parameter Product Name is not required for SQL Server; the 3rd parameter is the driver type; and the 4th parameter is the data source, here, the SQL Server address is written.

exec sp_addlinkedserver 'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'

After the linked server is configured, the same local account is used to log on to the remote database by default. If the accounts are different, account ing is required. For details about the sp_add1_srvlogin parameter, see the official documentation. 1st parameters are the same as above; 2nd parameters are false: Use the user password provided by the following parameters to log on to the server; 3rd parameters are null so that all local accounts can use the user password to log on to the server, if the 3rd parameters are set to a local SQL Server login user name, only this user can use a remote account to log on to the Server. The last two parameters are the user and password used to log on to the remote Server.

exec sp_addlinkedsrvlogin 'LNK_ServerB_DatabaseB','false',null,'user','password'

If you want to delete the above configuration, you can:

exec sp_droplinkedsrvlogin 'LNK_ServerB_DatabaseB',nullexec sp_dropserver 'LNK_ServerB_DatabaseB','droplogins'

The above configuration can be found in the linkedobjects linkedservserv in the SQL Server Management Studio manager. If all links are normal, you can directly open the database table on the linked Server.

It is worth noting that The above two stored procedures cannot appear in The trigger code, but are run in The server ServerA to complete The configuration in advance. Otherwise, The trigger implicit transaction request will report The error "The procedure 'sys. sp_addmediaserver 'could not be executed within a transaction."

2. Configure distributed transactions

SQL Server triggers use transactions implicitly, and the linked Server is a remote Server. You need to enable Distributed Transaction Processing between the local Server and the remote Server, otherwise, The error "The partner transaction manager has disabled its support for remote/network transactions" is reported. I have enabled the Distributed Transaction Coordinator in both ServerA and ServerB and configured it to support distributed transactions. Both ServerA and ServerB are Windows Server 2012 R2, similar to servers of other versions.

(1) check that the Distributed Transaction Coordinator has been enabled in Services. msc. Other versions of servers are not necessarily installed by default. You need to install windows features to install this feature first.

(2) Find the Component Services in the server management tool Administrative Tools, configure the following in the attribute Security tab of Local DTC, open the relevant Security settings, and then restart the service, it is also said that the server needs to be restarted, but at least 2012 R2 is not required.

(3) configure the firewall. enable both Inbound and Outbound.

3. Processing of database fields text and ntext

In business, a Content field in Table A is of the text type. When synchronizing data to table B, you must replace the Content. If the text type is outdated, Microsoft officially recommends replacing it with (N) VARCHAR (MAX). For more information, see here. We can consider it for future design. Here we will consider processing text.

However, in triggers, both the inserted and deleted tables cannot process the text, ntext, and image types. Here we use a curve to save the country, read records from the database to the temporary table, and then use textptr, patindex, and updatetext to replace strings.

if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp_tablea'))  drop table #temp_tableaselect * into #temp_tablea from TableA where ID = @IDdeclare @s varchar(200),@d varchar(200)select @s='="/_target/',@d='="/_replacement/'declare @p varbinary(16),@postion int,@l intselect @p=textptr(Content),@l=len(@s),@postion=patindex('%'+@s+'%',Content)+1 from #temp_tableawhile @postion>1begin updatetext #temp_tablea.Content @p @postion @l @d select @postion=patindex('%'+@s+'%',Content)+1 from #temp_tableaend

4. perform remote database operations

When we configure a connection server, we can directly access the remote database table, as shown below:

insert into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB ...update LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB set ...

However, simple SQL editors often report syntax errors. To facilitate programming, we hope to use exec sp_executesql for more flexibility. In fact, exec can directly execute SQL statements, but it is more difficult to return values. As follows, Name is returned after TableB is queried through the ID on the remote server. In the sp_executesql stored procedure, the output keyword can be used to define the variable as the return variable, where @ Name output is the return variable, @ ID indicates the input variable.

declare @sql nvarchar(500), @Name nvarchar(50),@ID nvarchar(40)set @SQL=N'select @Name=Name from LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB where ID=@ID'exec sp_executesql @SQL,N'@Name nvarchar(50) output,@ID nvarchar(40)',@Name output,@ID

In addition, exec directly executes the SQL statement, which is essentially the SQL string after splicing. Sometimes it is much more difficult to concatenate variables into the string (several single quotes are required), while sp_executesql is much clearer.

declare @SQL nvarchar(500),@Name nvarchar(50),@Count int,@ID nvarchar(40)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 Name=@Name,Count=@Count where ID=@ID'exec sp_executesql @SQL, N'@Name nvarchar(50),@Count int,@ID nvarchar(40)',@Name,@Count,@ID

The above is a summary of the issues that SQL Server encountered when synchronously updating remote databases in real time. I hope to help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!