January 24, 2018 at 11:56 am
Are the changes only happening on one side or are changes happening in both databases and they need to be synchronized? If changes are happening on both sides then this is a more complex problem.
If changes are only being made in one source database and need to propagate to the other every 4 hours, and if there is a last_modified date time, then a simpler solution may be to use regular SQL Statements such as INSERT/UPDATE and compare last_modified. I have a process that works like that running every hour for a handful of tables.
January 24, 2018 at 12:06 pm
Chris Harshman - Wednesday, January 24, 2018 11:56 AMAre the changes only happening on one side or are changes happening in both databases and they need to be synchronized? If changes are happening on both sides then this is a more complex problem.
If changes are only being made in one source database and need to propagate to the other every 4 hours, and if there is a last_modified date time, then a simpler solution may be to use regular SQL Statements such as INSERT/UPDATE and compare last_modified. I have a process that works like that running every hour for a handful of tables.
It is only on the source that changes are happening. However, our prime concern now is to not lock the tables during the synchronization process as they can't afford to have even slightest of downtime. Is what you proposing take this into consideration and if yes then can you IM the script (in case if you don't mind to share your solution)
January 24, 2018 at 2:52 pm
ffarouqi - Wednesday, January 24, 2018 12:06 PMIt is only on the source that changes are happening. However, our prime concern now is to not lock the tables during the synchronization process as they can't afford to have even slightest of downtime. Is what you proposing take this into consideration and if yes then can you IM the script (in case if you don't mind to share your solution)
I'm not sure how you could avoid locks on either side. Using UPDATE and INSERT commands would always put a read lock on the source and would definitely lock the target table to do the writes. I just proposed this as easier to setup and maintain than replication. Below is a script that I use to generate these UPSERT scripts, you'd just need to change the @SchemaName, @TableName, @SourceDatabase parameters and the name of your LastChangeDateTime column:DECLARE
@SchemaName nvarchar(128),
@TableName nvarchar(128),
@SourceDatabase nvarchar(128),
@ObjectID int,
@HasIdentity bit,
@MaxColumnID int
SET NOCOUNT ON
SET @SchemaName = N'dbo'
SET @TableName = N'AgentStatusLookup'
SET @SourceDatabase = N'[Instance].[SourceDB]'
/* generate upsert script */
-- get table's object_id for further reference
SELECT @ObjectID = o.object_id
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = @SchemaName
AND o.name = @TableName
AND type = 'U'
-- if table has an identity column, we'll want to use INSERT_IDENTITY
SELECT @HasIdentity = CASE WHEN EXISTS(SELECT NULL FROM sys.columns WHERE object_id = @ObjectID AND is_identity = 1) THEN 1 ELSE 0 END
-- get the list of primary key fields
DECLARE @PKColumns TABLE (index_column_id int, column_name nvarchar(128), column_id int)
INSERT INTO @PKColumns (index_column_id, column_name, column_id)
SELECT ic.index_column_id, c.name, c.column_id
FROM sys.columns c
INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE ic.object_id = @ObjectID
AND i.is_primary_key = 1
-- last column in table, to simplify script writing
SELECT @MaxColumnID = MAX(column_id)
FROM sys.columns
WHERE object_id = @ObjectID
-- create the actual merge script
DECLARE @sqllines TABLE (sqlcmd nvarchar(255), sort int);
INSERT INTO @sqllines (sqlcmd, sort)
SELECT CASE WHEN @HasIdentity = 1 THEN N'SET IDENTITY_INSERT [' + @SchemaName + N'].[' + @TableName + N'] ON;' ELSE N'' END AS sql, 1
UNION ALL
SELECT N'', 2
UNION ALL
SELECT N'UPDATE t SET', 3
UNION ALL
SELECT N' [' + c.name + N'] = s.[' + c.name + CASE WHEN c.column_id = @MaxColumnID THEN N']' ELSE N'],' END,
100 + c.column_id AS sort
FROM sys.columns c
WHERE c.object_id = @ObjectID
AND c.column_id NOT IN (SELECT column_id FROM @PKColumns)
UNION ALL
SELECT N' FROM [' + @SchemaName + N'].[' + @TableName + N'] AS t', 999
UNION ALL
SELECT N' INNER JOIN ' + @SourceDatabase + N'.[' + @SchemaName + N'].[' + @TableName + N'] AS s', 1000
UNION ALL
SELECT CASE WHEN index_column_id = 1
THEN N' ON t.[' + column_name + N'] = s.[' + column_name + N']'
ELSE N' AND t.[' + column_name + N'] = s.[' + column_name + N']'
END, 1000 + index_column_id
FROM @PKColumns
UNION ALL
SELECT N' AND s.LastChangeDateTime > t.LastChangeDateTime;', 1998
UNION ALL
SELECT N'', 1999
UNION ALL
SELECT N'INSERT INTO [' + @SchemaName + N'].[' + @TableName + N'] (', 2000
UNION ALL
SELECT N' [' + c.name + CASE WHEN c.column_id = @MaxColumnID THEN N'])' ELSE N'],' END,
2000 + c.column_id AS sort
FROM sys.columns c
WHERE c.object_id = @ObjectID
UNION ALL
SELECT N'SELECT ', 3000
UNION ALL
SELECT N' s.[' + c.name + CASE WHEN c.column_id = @MaxColumnID THEN N']' ELSE N'],' END,
3000 + c.column_id AS sort
FROM sys.columns c
WHERE c.object_id = @ObjectID
UNION ALL
SELECT N' FROM [' + @SchemaName + N'].[' + @TableName + N'] AS t', 3999
UNION ALL
SELECT N' LEFT OUTER JOIN ' + @SourceDatabase + N'.[' + @SchemaName + N'].[' + @TableName + N'] AS s', 4000
UNION ALL
SELECT CASE WHEN index_column_id = 1
THEN N' ON t.[' + column_name + N'] = s.[' + column_name + N']'
ELSE N' AND t.[' + column_name + N'] = s.[' + column_name + N']'
END, 4000 + index_column_id
FROM @PKColumns
UNION ALL
SELECT N' WHERE s.[' + column_name + '] IS NULL;', 4999
FROM @PKColumns WHERE index_column_id = 1
UNION ALL
SELECT N'', 5000
UNION ALL
SELECT CASE WHEN @HasIdentity = 1 THEN N'SET IDENTITY_INSERT [' + @SchemaName + N'].[' + @TableName + N'] OFF;' ELSE N'' END, 5001
ORDER BY 2;
DECLARE @sqlcmd nvarchar(4000);
SET @sqlcmd = N''
SELECT @sqlcmd += sqlcmd + NCHAR(10)
FROM @sqllines
ORDER BY sort;
PRINT @sqlcmd;
January 24, 2018 at 8:18 pm
I can absolutely guarantee you that triggers and TSQL to process records can be crafted to have as low of an overhead as you desire. I have done this on systems with TB of data and with thousands of DML actions per second.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply