SQL SERVER – Faster SQL Server Databases and Applications – Power and Control with SafePeak Caching Options

Update: This blog post is written based on the SafePeak, which is available for free download.

Today, I’d like to examine more closely one of my preferred technologies for accelerating SQL Server databases, SafePeak. Safepeak’s software provides a variety of advanced data caching options, techniques and tools to accelerate the performance and scalability of SQL Server databases and applications.

I’d like to look more closely at some of these options, as some of these capabilities could help you address lagging database and performance on your systems.

To better understand the available options, it is best to start by understanding the difference between the usual “Basic Caching” vs. SafePeak’s “Dynamic Caching”.

Basic Caching

Basic Caching (or the stale and static cache) is an ability to put the results from a query into cache for a certain period of time. It is based on TTL, or Time-to-live, and is designed to stay in cache no matter what happens to the data. For example, although the actual data can be modified due to DML commands (update/insert/delete), the cache will still hold the same obsolete query data. Meaning that with the Basic Caching is really static / stale cache.  As you can tell, this approach has its limitations.

Dynamic Caching

Dynamic Caching (or the non-stale cache) is an ability to put the results from a query into cache while maintaining the cache transaction awareness looking for possible data modifications. The modifications can come as a result of:

  • DML commands (update/insert/delete),
  • indirect modifications due to triggers on other tables,
  • executions of stored procedures with internal DML commands
  • complex cases of stored procedures with multiple levels of internal stored procedures logic.

When data modification commands arrive, the caching system identifies the related cache items and evicts them from cache immediately. In the dynamic caching option the TTL setting still exists, although its importance is reduced, since the main factor for cache invalidation (or cache eviction) become the actual data updates commands.

Now that we have a basic understanding of the differences between “basic” and “dynamic” caching, let’s dive in deeper.

SafePeak: A comprehensive and versatile caching platform

SafePeak comes with a wide range of caching options. Some of SafePeak’s caching options are automated, while others require manual configuration. Together they provide a complete solution for IT and Data managers to reach excellent performance acceleration and application scalability for  a wide range of business cases and applications.

  • Automated caching of SQL Queries: Fully/semi-automated caching of all “read” SQL queries, containing any types of data, including Blobs, XMLs, Texts as well as all other standard data types. SafePeak automatically analyzes the incoming queries, categorizes them into SQL Patterns, identifying directly and indirectly accessed tables, views, functions and stored procedures;
  • Automated caching of Stored Procedures: Fully or semi-automated caching of all read” stored procedures, including procedures with complex sub-procedure logic as well as procedures with complex dynamic SQL code. All procedures are analyzed in advance by SafePeak’s  Metadata-Learning process, their SQL schemas are parsed – resulting with a full understanding of the underlying code, objects dependencies (tables, views, functions, sub-procedures) enabling automated or semi-automated (manually review and activate by a mouse-click) cache activation, with full understanding of the transaction logic for cache real-time invalidation;
  • Transaction aware cache: Automated cache awareness for SQL transactions (SQL and in-procs);
  • Dynamic SQL Caching: Procedures with dynamic SQL are pre-parsed, enabling easy cache configuration, eliminating SQL Server load for parsing time and delivering high response time value even in most complicated use-cases;
  • Fully Automated Caching: SQL Patterns (including SQL queries and stored procedures) that are categorized by SafePeak as “read and deterministic” are automatically activated for caching;
  • Semi-Automated Caching: SQL Patterns categorized as “Read and Non deterministic” are patterns of SQL queries and stored procedures that contain reference to non-deterministic functions, like getdate(). Such SQL Patterns are reviewed by the SafePeak administrator and in usually most of them are activated manually for caching (point and click activation);
  • Fully Dynamic Caching: Automated detection of all dependent tables in each SQL Pattern, with automated real-time eviction of the relevant cache items in the event of “write” commands (a DML or a stored procedure) to one of relevant tables. A default setting;
  • Semi Dynamic Caching: A manual cache configuration option enabling reducing the sensitivity of specific SQL Patterns to “write” commands to certain tables/views. An optimization technique relevant for cases when the query data is either known to be static (like archive order details), or when the application sensitivity to fresh data is not critical and can be stale for short period of time (gaining better performance and reduced load);
  • Scheduled Cache Eviction: A manual cache configuration option enabling scheduling SQL Pattern cache eviction based on certain time(s) during a day. A very useful optimization technique when (for example) certain SQL Patterns can be cached but are time sensitive. Example: “select customers that today is their birthday”, an SQL with getdate() function, which can and should be cached, but the data stays relevant only until 00:00 (midnight);
  • Parsing Exceptions Management: Stored procedures that were not fully parsed by SafePeak (due to too complex dynamic SQL or unfamiliar syntax), are signed as “Dynamic Objects” with highest transaction safety settings (such as: Full global cache eviction, DDL Check = lock cache and check for schema changes, and more). The SafePeak solution points the user to the Dynamic Objects that are important for cache effectiveness, provides easy configuration interface, allowing you to improve cache hits and reduce cache global evictions. Usually this is the first configuration in a deployment;
  • Overriding Settings of Stored Procedures: Override the settings of stored procedures (or other object types) for cache optimization. For example, in case a stored procedure SP1 has an “insert” into table T1, it will not be allowed to be cached. However, it is possible that T1 is just a “logging or instrumentation” table left by developers. By overriding the settings a user can allow caching of the problematic stored procedure;
  • Advanced Cache Warm-Up: Creating an XML-based list of queries and stored procedure (with lists of parameters) for periodically automated pre-fetching and caching. An advanced tool allowing you to handle more rare but very performance sensitive queries pre-fetch them into cache allowing high performance for users’ data access;
  • Configuration Driven by Deep SQL Analytics: All SQL queries are continuously logged and analyzed, providing users with deep SQL Analytics and Performance Monitoring. Reduce troubleshooting from days to minutes with database objects and SQL Patterns heat-map. The performance driven configuration helps you to focus on the most important settings that bring you the highest performance gains. Use of SafePeak SQL Analytics allows continuous performance monitoring and analysis, easy identification of bottlenecks of both real-time and historical data;
  • Cloud Ready: Available for instant deployment on Amazon Web Services (AWS).

As you can see, there are many options to configure SafePeak’s SQL Server database and application acceleration caching technology to best fit a lot of situations. If you’re not familiar with their technology, they offer free-trial software you can download that comes with a free “help session” to help get you started.

You can access the free trial here. Also, SafePeak is available to use on Amazon Cloud.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

MySQL – Scalability on Amazon RDS: Scale out to multiple RDS instances

Today, I’d like to discuss getting better MySQL scalability on Amazon RDS.

The question of the day: “What can you do when a MySQL database needs to scale write-intensive workloads beyond the capabilities of the largest available machine on Amazon RDS?”

Let’s take a look.

In a typical EC2/RDS set-up, users connect to app servers from their mobile devices and tablets, computers, browsers, etc.  Then app servers connect to an RDS instance (web/cloud services) and in some cases they might leverage some read-only replicas.

 

Figure 1. A typical RDS instance is a single-instance database, with read replicas.  This is not very good at handling high write-based throughput.

As your application becomes more popular you can expect an increasing number of users, more transactions, and more accumulated data.  User interactions can become more challenging as the application adds more sophisticated capabilities. The result of all this positive activity: your MySQL database will inevitably begin to experience scalability pressures.

What can you do?

Broadly speaking, there are four options available to improve MySQL scalability on RDS.

1. Larger RDS Instances – If you’re not already using the maximum available RDS instance, you can always scale up – to larger hardware.  Bigger CPUs, more compute power, more memory et cetera. But the largest available RDS instance is still limited.  And they get expensive.

“High-Memory Quadruple Extra Large DB Instance”:

  • 68 GB of memory
  • 26 ECUs (8 virtual cores with 3.25 ECUs each)
  • 64-bit platform
  • High I/O Capacity
  • Provisioned IOPS Optimized: 1000Mbps

2. Provisioned IOPs – You can get provisioned IOPs and higher throughput on the I/O level.

However, there is a hard limit with a maximum instance size and maximum number of provisioned IOPs you can buy from Amazon and you simply cannot scale beyond these hardware specifications.

3. Leverage Read Replicas – If your application permits, you can leverage read replicas to offload some reads from the master databases. But there are a limited number of replicas you can utilize and Amazon generally requires some modifications to your existing application.

And read-replicas don’t help with write-intensive applications.

4. Multiple Database Instances – Amazon offers a fourth option:

You can implement partitioning,thereby spreading your data across multiple database Instances” (Link)

However, Amazon does not offer any guidance or facilities to help you with this. “Multiple database instances” is not an RDS feature.  And Amazon doesn’t explain how to implement this idea.

In fact, when asked, this is the response on an Amazon forum:

Q: Is there any documents that describe the partition DB across multiple RDS?
I need to use DB with more 1TB but exist a limitation during the create process, but I read in the any FAQ that you need to partition database, but I don’t find any documents that describe it.

A: “DB partitioning/sharding is not an official feature of Amazon RDS or MySQL, but a technique to scale out database by using multiple database instances. The appropriate way to split data depends on the characteristics of the application or data set. Therefore, there is no concrete and specific guidance.”

So now what?

The answer is to scale out with ScaleBase.

Amazon RDS with ScaleBase: What you get – MySQL Scalability!

ScaleBase is specifically designed to scale out a single MySQL RDS instance into multiple MySQL instances.

Critically, this is accomplished with no changes to your application code.  Your application continues to “see” one database.   ScaleBase does all the work of managing and enforcing an optimized data distribution policy to create multiple MySQL instances.

With ScaleBase, data distribution, transactions, concurrency control, and two-phase commit are all 100% transparent and 100% ACID-compliant, so applications, services and tooling continue to interact with your distributed RDS as if it were a single MySQL instance.

The result: now you can cost-effectively leverage multiple MySQL RDS instance to scale out write-intensive workloads to an unlimited number of users, transactions, and data.

Amazon RDS with ScaleBase: What you keep – Everything!

And how does this change your Amazon environment?

1. Keep your application, unchanged – There is no change your application development life-cycle at all.  You still use your existing development tools, frameworks and libraries.  Application quality assurance and testing cycles stay the same. And, critically, you stay with an ACID-compliant MySQL environment.

2. Keep your RDS value-added services – The value-added services that you rely on are all still available. Amazon will continue to handle database maintenance and updates for you. You can still leverage High Availability via Multi A-Z.  And, if it benefits youra application throughput, you can still use read replicas.

3. Keep your RDS administration – Finally the RDS monitoring and provisioning tools you rely on still work as they did before.

With your one large MySQL instance, now split into multiple instances, you can actually use less expensive, smallersmaller available RDS hardware and continue to see better database performance.

Conclusion

Amazon RDS is a tremendous service, but it doesn’t offer solutions to scale beyond a single MySQL instance. Larger RDS instances get more expensive.  And when you max-out on the available hardware, you’re stuck.  Amazon recommends scaling out your single instance into multiple instances for transaction-intensive apps, but offers no services or guidance to help you. This is where ScaleBase comes in to save the day.

It gives you a simple and effective way to create multiple MySQL RDS instances, while removing all the complexities typically caused by “DIY” sharding andwith no changes to your applications .

With ScaleBase you continue to leverage the AWS/RDS ecosystem: commodity hardware and value added services like read replicas, multi A-Z, maintenance/updates and administration with monitoring tools and provisioning.

SCALEBASE ON AMAZON

If you’re curious to try ScaleBase on Amazon, it can be found here – Download NOW.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3

This is the third part of the series Incremental Statistics. Here is the index of the complete series.


In earlier two parts we have seen what is incremental statistics and its simple example. In this blog post we will be discussing about DMV, which will list all the statistics which are enabled for Incremental Updates.

SELECT  OBJECT_NAME(sys.stats.OBJECT_ID) AS TableName,
sys.columns.name AS ColumnName,
sys.stats.name AS StatisticsName
FROM   sys.stats
INNER JOIN sys.stats_columns ON sys.stats.OBJECT_ID = sys.stats_columns.OBJECT_ID
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN sys.columns ON sys.stats.OBJECT_ID = sys.columns.OBJECT_ID
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE   sys.stats.is_incremental = 1

If you run above script in the example displayed, in part 1 and part 2 you will get resultset as following.

When you execute the above script, it will list all the statistics in your database which are enabled for Incremental Update. The script is very simple and effective. If you have any further improved script, I request you to post in the comment section and I will post that on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2

This is the second part of the series Incremental Statistics. Here is the index of the complete series.


In part 1 we have understood what is incremental statistics and now in this second part we will see a simple example of incremental statistics. This blog post is heavily inspired from my friend Balmukund’s must read blog post. If you have partitioned table and lots of data, this feature can be specifically very useful.

Prerequisite

Here are two things you must know before you start with the demonstrations.

AdventureWorks – For the demonstration purpose I have installed AdventureWorks 2012 as an AdventureWorks 2014 in this demonstration.

Partitions – You should know how partition works with databases.

Setup Script

Here is the setup script for creating Partition Function, Scheme, and the Table. We will populate the table based on the SalesOrderDetails table from AdventureWorks.

-- Use Database
USE AdventureWorks2014
GO
-- Create Partition Function
CREATE PARTITION FUNCTION IncrStatFn (INT) AS RANGE LEFT FOR VALUES
(44000, 54000, 64000, 74000)
GO
-- Create Partition Scheme
CREATE PARTITION SCHEME IncrStatSch AS PARTITION [IncrStatFn] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
-- Create Table Incremental_Statistics
CREATE TABLE [IncrStatTab](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL)
ON IncrStatSch(SalesOrderID)
GO
-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID < 54000
GO

Check Details

Now we will check details in the partition table IncrStatSch.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

You will notice that only a few of the partition are filled up with data and remaining all the partitions are empty.

Now we will create statistics on the Table on the column SalesOrderID.

However, here we will keep adding one more keyword which is INCREMENTAL = ON. Please note this is the new keyword and feature added in SQL Server 2014. It did not exist in earlier versions.

-- Create Statistics
CREATE STATISTICS IncrStat
ON [IncrStatTab] (SalesOrderID)
WITH FULLSCAN, INCREMENTAL = ON
GO

Now we have successfully created statistics let us check the statistical histogram of the table.

Now let us once again populate the table with more data. This time the data are entered into a different partition than earlier populated partition.

-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID > 54000
GO

Let us check the status of the partition once again with following script.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

Statistics Update

Now here has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data.

However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same.

-- Update Statistics Manually
UPDATE STATISTICS IncrStatTab (IncrStat)
WITH RESAMPLE ON PARTITIONS(3, 4)
GO

Now let us check the statistics once again.

-- Show Statistics
DBCC SHOW_STATISTICS('IncrStatTab', IncrStat)
WITH HISTOGRAM
GO

Upon examining statistics histogram, you will notice that now the distribution has changed and there is way more rows in the histogram.

Summary

The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions. In earlier version to update statistics one has to do FULLSCAN on the entire table which was wasting too many resources. With the new feature in SQL Server 2014, now only those partitions which are significantly changed can be specified in the script to update statistics.

Cleanup

You can clean up the database by executing following scripts.

-- Clean up
DROP TABLE [IncrStatTab]
DROP PARTITION SCHEME [IncrStatSch]
DROP PARTITION FUNCTION [IncrStatFn]
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1

This is the first part of the series Incremental Statistics. Here is the index of the complete series.


Statistics are considered one of the most important aspects of SQL Server Performance Tuning. You might have often heard the phrase, with related to performance tuning.

“Update Statistics before you take any other steps to tune performance”.

Honestly, I have said above statement many times and many times, I have personally updated statistics before I start to do any performance tuning exercise. You may agree or disagree to the point, but there is no denial that Statistics play an extremely vital role in the performance tuning.

SQL Server 2014 has a new feature called Incremental Statistics. I have been playing with this feature for quite a while and I find that very interesting. After spending some time with this feature, I decided to write about this subject over here.

New in SQL Server 2014 – Incremental Statistics

Well, it seems like lots of people wants to start using SQL Server 2014’s new feature of Incremetnal Statistics. However, let us understand what actually this feature does and how it can help. I will try to simplify this feature first before I start working on the demo code.

Code for all versions of SQL Server

Here is the code which you can execute on all versions of SQL Server and it will update the statistics of your table. The keyword which you should pay attention is WITH FULLSCAN. It will scan the entire table and build brand new statistics for you which your SQL Server Performance Tuning engine can use for better estimation of your execution plan.

UPDATE STATISTICS TableName(StatisticsName) WITH FULLSCAN

Who should learn about this? Why?

If you are using partitions in your database, you should consider about implementing this feature. Otherwise, this feature is pretty much not applicable to you. Well, if you are using single partition and your table data is in a single place, you still have to update your statistics the same way you have been doing.

If you are using multiple partitions, this may be a very useful feature for you. In most cases, users have multiple partitions because they have lots of data in their table. Each partition will have data which belongs to itself. Now it is very common that each partition are populated separately in SQL Server.

Real World Example

For example, if your table contains data which is related to sales, you will have plenty of entries in your table. It will be a good idea to divide the partition into multiple filegroups for example, you can divide this table into 3 semesters or 4 quarters or even 12 months. Let us assume that we have divided our table into 12 different partitions. Now for the month of January, our first partition will be populated and for the month of February our second partition will be populated. Now assume, that you have plenty of the data in your first and second partition. Now the month of March has just started and your third partition has started to populate. Due to some reason, if you want to update your statistics, what will you do?

In SQL Server 2012 and earlier version

You will just use the code of WITH FULLSCAN and update the entire table. That means even though you have only data in third partition you will still update the entire table. This will be VERY resource intensive process as you will be updating the statistics of the partition 1 and 2 where data has not changed at all.

In SQL Server 2014

You will just update the partition of Partition 3. There is a special syntax where you can now specify which partition you want to update now. The impact of this is that it is smartly merging the new data with old statistics and update the entire statistics without doing FULLSCAN of your entire table. This has a huge impact on performance.

Remember that the new feature in SQL Server 2014 does not change anything besides the capability to update a single partition. However, there is one feature which is indeed attractive. Previously, when table data were changed 20% at that time, statistics update were triggered. However, now the same threshold is applicable to a single partition. That means if your partition faces 20% data, change it will also trigger partition level statistics update which, when merged to your final statistics will give you better performance.

In summary

If you are not using a partition, this feature is not applicable to you.

If you are using a partition, this feature can be very helpful to you.

Tomorrow:

We will see working code of SQL Server 2014 Incremental Statistics.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026

[Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white paper, I was corrected by SQL Experts immediately for my error in judgement and incorrect information. I accepted my mistakes and corrected it. I just shared this story with my good friend Tim Radney and he was very kind to give me guidance on this subject. I asked him if he can write further on this topic and help people understand this complex subject in simple words.

Linchpin People are database coaches and wellness experts for a data driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words what should be the good value for Page Life Expectancy (PLE).


When troubleshooting SQL Server performance issues one of the top places to look is memory.  One of the more common methods to check for memory pressure is to check to see the memory counter ‘Page Life Expectancy’ (PLE).  The value returned is represented in seconds.  A very old and dated recommendation is that this value be 300 or higher which is only 5 minutes.  With the standard servers in place today, this value is too low.

What is the proper value to monitor for your server, you may ask, well it really depends on the amount of memory allocated to your instance of SQL.  When working with my clients the value I start with is taking (max memory in GB) / 4 * 300.  This would be the minimum value I would expect to see.  On a server with 32GB allocated to SQL this value would be (32/4)*300 = 2400 seconds or 40 minutes.

For a server with 32 GB of ram, maintaining a PLE value of 2400 or higher is a good value, but what is equally important is to know what the baseline value is for the instance.  What is the average value the server has during certain times during the day?  If you capture this value and alarm/alert when you have big dips then you will be more equipped to get an early detection of a potential issue.  Things like improper indexed queries, large data pulls, etc.

I blogged awhile back and included the query that I run to retrieve the Page Life Expectancy.  http://timradney.com/2013/03/08/what-is-page-life-expectancy-ple-in-sql-server/

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Synchronize Data Exclusively with T-SQL

UPDATE: This review and exercise is based on dbForge Studio.

Database developers often face the task to synchronize users’ data. Currently, to solve this task, there was developed quite a big number of various utilities. In this article, we will review the process of creating the new functionality to synchronize data exclusively with T-SQL.

Let’s split the synchronization process with T-SQL onto the sub-tasks:

  1. Retrieving the table structure of a master database
  2. Preparing data stored in the tables of the master database and saving it at the public locations
  3. Turning off relations and triggers between the tables of a target database for synchronization time
  4. Synchronization of data between tables
  5. Turning on relations and triggers between the tables of the target database

To make the process of creating the synchronization functionality comparatively easy to understand, let’s discuss some restrictions:

  1. Synchronization will be elapsing within one server, syntax will be supported on the level of MS SQL Server 2005 or higher
  2. The schemes between the synchronized databases are identical
  3. Only system data types are supported
  4. It’s possible to synchronize any amount of tables at a time
  5. The structure of tables is not essential

1) Retrieving the table structure of the master database

The list of tables can be obtained with the following query:
SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.tables t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

 

It’s important to remember that some system views may contain an excessive amount of connections and calls of system functions, that will negatively affect the run-time performance of the query, such as sys.tables:

SELECT *
FROM sys.objects$ o
LEFT JOIN sys.sysidxstats lob ON lob.id = o.[object_id] AND lob.indid <= 1
LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.[object_id] AND ds.class = 8 AND ds.depsubid <= 1
LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.[object_id] AND rfs.class = 42 AND rfs.depsubid = 0
LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
WHERE o.[type] = 'U '

Therefore, you have to use as simple system views as possible. In our case, you can use sys.objects:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '

To make the query not to return excessive rows, we need to preliminary filter out the tables containing no data:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o
WHERE o.[type] = 'U '
AND EXISTS(
SELECT 1
FROM sys.partitions p
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
)
t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

The list of columns containing only system data types can be obtained in the following way:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name = '[' + c.name + ']'
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id]
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
WHERE o.[type] = 'U '

Next, to minimize the amount of data that we pass for comparison, it is suggested not to take into account column values, that have default values and can’t be empty. So, the query will look as follows:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name =
CASE WHEN c2.[object_id] IS NULL
THEN '[' + c.name + ']'
ELSE '[' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')'
END
FROM
sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id]
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND
c.is_nullable = 0
WHERE o.[type] = 'U '
AND c.is_computed = 0

2) Retrieving the data stored in the tables of the master database and saving it in public locations

First of all, we need some interim buffer, into which we will load data from a master database. The tempdb system database is public for any user database on the server, therefore, we can choose it.

Every time synchronization runs, we will check whether the table exists and create it if needed with the following query:

IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE
tempdb.dbo.MergeTemp
(
[object_name] NVARCHAR(300) NOT NULL
,
create_date DATETIME NOT NULL DEFAULT GETDATE()
,
data XML NULL
,
CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY]
)
END

Then, it’s necessary to decide how to store data from the master database. The main difficulty within this task is that tables may have different structures. The most obvious option is to create a temporary table for each synchronized table and upload data into it. However, such option may be not optimal because we will have to execute many DDL commands.

Therefore, we suggest to solve this task by means of XML, that can describe almost any database entity. The disadvantage of the suggested method is that not quite a correct XML data structure may be generated for some data types. You can retrieve a list of such data types with the following query:

SELECT t.name
FROM sys.types t
WHERE t.user_type_id != t.system_type_id

 

Let’s write a query to create XML with dynamic structure:

DECLARE
@Data NVARCHAR(4000)
,
@ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name]
, [sql] = '
INSERT INTO tempdb.dbo.MergeTemp([object_name], data)
SELECT '''
+ quota_object_name + ''', (SELECT ' + object_columns + '
FROM '
+ quota_object_name + ' t
FOR XML AUTO, ROOT('''
+ [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+
CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id]
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '')
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id != c.system_type_id OR t.name = 'xml')
AND
c.[object_id] = o.[object_id]
)
)
t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id]
) data
OPEN [tables]
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC
sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE
[tables]
DEALLOCATE [tables]

Where, the following query will be generated for each table:

INSERT INTO tempdb.dbo.MergeTemp ([object_name], data)
SELECT <TABLE name>,
(
SELECT <columns list>
FROM <TABLE name> t
FOR XML AUTO, ROOT('<table name>')
)

After such query is executed, an XML with table data will be generated and written into the MergeTemp table.

3) Disabling relations and triggers between the tables of the master database for the time of synchronization

At the next stage, we will insert data into tables, therefore it’s preferably to turn off foreign keys and triggers. If you do not, various errors may appear at the inserting stage.

Here is a script to turn off/on foreign keys for tables:

DECLARE
@FKeysOff NVARCHAR(MAX)
,
@FKeysOn NVARCHAR(MAX)
;
WITH fkeys AS
(
SELECT DISTINCT o.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.foreign_keys fk ON o.[object_id] = fk.parent_object_id
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
)
SELECT
@FKeysOff = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' NOCHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
@FKeysOn = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' CHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
EXEC sys.sp_executesql @FKeysOff
EXEC sys.sp_executesql @FKeysOn

This query seems cumbrous and it doesn’t take into account more complicated relations between tables. In case when the database contains relatively few tables, the following query (turning off all table triggers and foreign keys in the database) may be used:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL '

4) Data synchronization between tables

We have already access to data from the master database stored in XML.

With the following query we can find the unique identifier of the database object we are going to synchronize:

SELECT
o.[object_id]
, mt.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT

It’s important to note that the object identifier (object_id) is unique in the database and may be changed when the schema is modified (e.g., adding a new column). That’s why, initially, we were wrote the name of the object instead of its identifier. To synchronize tables, we need to parse data from XML.

As an option, for this, you can use the OPENXML construction:

DECLARE @IDoc INT
EXEC
sys.sp_xml_preparedocument @IDoc OUTPUT, @XML
SELECT [<column name>]
FROM OPENXML(@IDoc, '<table name>/t')
WITH ([<column name>] [<column datetype>]
EXEC sys.sp_xml_removedocument @IDoc

 

However, this approach is less effective when parsing significantly large XML structures. Therefore, it was decided to use the construction described below:

Here is an example of a query, that will generate dynamic SQL by XML parsing and synchronization with the target table:

DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
,
@SQL NVARCHAR(MAX)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
o.[object_id]
, mt.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
OPEN package
FETCH NEXT FROM package INTO @ObjectID, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '''
+ @ObjName + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = '
+ CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT '
+ @ObjName + ' ON;
DELETE FROM '
+ @ObjName + '
INSERT INTO '
+ @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c
WHERE c.[object_id] = @ObjectID
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT '
+ STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM
sys.columns c
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND
c.is_nullable = 0
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '') + '
FROM @XML.nodes('''
+ s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO @ObjectID, @ObjName
END
CLOSE
package
DEALLOCATE package

The following script will build such query for each synchronized table:

DECLARE @XML XML
SELECT
@XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '<table name>'
)
IF EXISTS(
SELECT 1
FROM sys.columns c
WHERE c.[object_id] = <table_id>
AND
c.is_identity = 1
) SET IDENTITY_INSERT <TABLE name> ON;
DELETE FROM <TABLE name>
INSERT INTO <TABLE name> (<columns list>)
SELECT [<column name>] = r.c.value('@<column name>', '<column datetype>')
FROM @XML.nodes('<table name>/t') r(c)

Synchronization can be performed in three steps:

  1. Inserting new data
  2. Updating existing data by a key field
  3. Deleting non-existing data in the master database

The provided sequence of actions can be organized through separate operators: INSERT/UPDATE/DELETE; or using a MERGE construction that was implemented in MS SQL Server 2008. However, in this case, SQL code will have quite a complicated structure, since we will have to take into account many factors (e.g., insert data in accordance to the order of the relations between tables). In case if the database has a more simple structure, an easier approach can be used.

If all the tables of the database or a specific isolated schema are synchronized, there is a possibility to directly remove all the records from the tables and insert data from the master database. On one hand, such approach can be the most cost-effective one in terms of implementation, if a database with simple logic is synchronized. On the other hand, this way of synchronization may lead to damages in the integrity and validity of the data in the database.

5) Enabling relations and triggers between the tables of the target database

After inserting data into required tables, it’s necessary to enable foreign keys and triggers in order not to damage the cascade integrity and internal business logic. This can be performed as follows:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
'

When executing this command, the server will check whether the inserted data is correct. Therefore, if an error arises on this step, the problem may initially due to damaging of the cascade integrity.

The resulting version of the script:

USE AdventureWorks2008R2
SET NOCOUNT ON;
SET XACT_ABORT ON;
RAISERROR('Start...', 0, 1) WITH NOWAIT
DECLARE
@SQL NVARCHAR(MAX)
,
@Time DATETIME
, @Text NVARCHAR(300)
,
@SessionUID UNIQUEIDENTIFIER
SELECT
@SQL = ''
, @Time = GETDATE()
,
@SessionUID = NEWID()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Creating temporary table...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE
tempdb.dbo.MergeTemp
(
id UNIQUEIDENTIFIER NOT NULL
,
[object_name] NVARCHAR(300) NOT NULL
,
create_date DATETIME NOT NULL DEFAULT GETDATE()
,
data XML NULL
,
CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY]
)
END
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate SQL queries...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE
@Data NVARCHAR(4000)
,
@ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name]
, [sql] = '
INSERT INTO tempdb.dbo.MergeTemp(id, [object_name], data)
SELECT '''
+ CAST(@SessionUID AS VARCHAR(36)) + ''', ''' + quota_object_name + ''', (SELECT ' + object_columns + '
FROM '
+ quota_object_name + ' t
FOR XML AUTO, ROOT('''
+ [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+
CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id]
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '')
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id!= c.system_type_id OR t.name = 'xml')
AND
c.[object_id] = o.[object_id]
)
)
t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id]
-- You can specify sync all db tables or specified schema/tables
-- WHERE s.name = 'Sales'
-- OR o.name IN ('Address', 'AddressType')
) data
OPEN [tables]
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate XML for ' + @ObjectName
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE
[tables]
DEALLOCATE [tables]
PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT
PRINT REPLICATE('-', 80)
USE AdventureWorks2008R2_Live
RAISERROR('Start...', 0, 1) WITH NOWAIT
SELECT @Time = GETDATE()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Get similar objects in both databases...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE @MergeTemp TABLE
(
[object_name] NVARCHAR(300)
,
[object_id] INT
, data XML
)
INSERT INTO @MergeTemp
(
[object_name]
, [object_id]
, daata
)
SELECT
mt.[object_name]
, o.[object_id]
, mt.data
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U'
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
WHERE mt.id = @SessionUID
IF NOT EXISTS(
SELECT 1
FROM @MergeTemp
) RAISERROR('Have nothing to import...', 16, 1)
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn off foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL
'
DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
mt.[object_id]
, mt.[object_name]
FROM @MergeTemp mt
OPEN package
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Processing ' + @ObjName + '...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
SELECT @SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '''
+ @ObjName + ''' AND id = ''' + CAST(@SessionUID AS VARCHAR(36)) + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = '
+ CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT '
+ @ObjName + ' ON;
DELETE FROM '
+ @ObjName + '
INSERT INTO '
+ @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT '
+ STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 WITH (NOLOCK) ON c.default_object_id = c2.[object_id]
AND b.name IN ('bit', 'char', 'varchar', 'nchar', 'nvarchar')
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '') + '
FROM @XML.nodes('''
+ s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
END
CLOSE
package
DEALLOCATE package
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn on foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
'
PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT

Conclusion:

As we can see, even for databases with a simple structure, the synchronization process is quite a bigtask that includes many different actions.

As you have noticed early in the article, manual data synchronization is not quite easy task. Thus, to save time and efforts it is highly recommended to use special tools for synchronization. For instance, the following screen-shot demonstrates how you can visualize compared data before synchronization by using comparison feature of dbForge Studio for SQL Server.

The upper grid shows the database objects differences and the bottom grid displays data differences. To synchronize source and target databases you can automatically generate the synchronization script. Then you can view this script, save it, or execute it immediately to update the target database.  So the process can be handled in an easier way.

Reference: Pinal Dave (http://blog.SQLAuthority.com)