Thursday, October 20, 2016

Syncing QA data with Production

Applies to: xSQL Data Compare v7.0.0 - v9.0.0

Nowadays, Quality Assurance (QA) is a very important part of the development process for any company that strives to offer a reliable product, to satisfy its clients, and be competitive in the market. And let’s not forget the “side benefit” of reduction of life cycle costs.

One of the main aspects of QA are the data and databases it uses. These data are usually kept in a separate environment from the production and, to have the best possible quality assurance process, data from the production environment are copied to the QA environment. For this task there is a choice between two main options:
  • Backing up and restoring the live database. Although this might work with small databases, if we are dealing with large databases with many tables and millions of rows, it becomes a very expensive operation because the database will have to be recreated each time the synchronization is performed. Also, if the databases are in a cloud environment like Azure, which does not support restoring a backup, this option is automatically eliminated. As a final point, in those cases where backup – restore is a viable option, there is always the issue of automating the synchronization process, which in the case of backup - restore operations is problematic at best. 
  • Using comparison tools like our xSQL Data Compare. For any large databases, whose data change rapidly, this is probably the best option. And that’s because the comparison and synchronization process is highly customizable and easy to automate.
     There are two cases that show in detail how xSQL Data Compare’s features can be used in the QA synchronization process.

  1. Suppose you have a production database with a table which currently has 5 million rows and needs to be synchronized with the QA Database. One way to go about this is to back up the live database and restore it in QA. The problem here is very easy to identify. Unless this is the first synchronization, it’s very improbable that all 5 million rows of the table in the Production database will have differences from the table in QA.
    For argument’s sake, let’s say that there are 100,000 records out of sync. If one was to use the backup – restore option, 5 million new rows would be inserted in the table in the QA database. So there’s 4,900,000 unnecessary INSERT operations and the server will be doing 50 times the amount of work it actually needs to do. By any standards, this is unacceptable.
    The process can be made much more efficient by using xSQL Data Compare, because after the comparison, xSQL Data Compare generates a synchronization script
    only for the rows that are out of sync. This is a big improvement already, but it can be made even better. Since the synchronization process is, in most cases, performed periodically, every week for example, than you already know that the only rows that are out of sync are the ones added or modified in the week prior to the synchronization. So there is no need to compare all 5 million rows. Just the ones that are out of sync.
    You can do this by using the
    where clause of xSQL Data Compare in which you can enter conditions in the same way you would enter them in a SQL Query. Below is an example in which the where condition is specified as ModifiedDate >= DATEADD(DAY, -7,GETDATE()). This will compare only the records which were modified in the last week. To open the dialog shown in the picture click the button selected in red.

  2. As I said in the first case, synchronization of the QA environment is usually a periodic and very repetitive process, so automating it would save a lot of time for DBAs or people responsible for this task. Data Compare addresses this issue with its command line version. The comparison from the first case can also be done from xSQL Data Compare command line and scheduled to be run periodically with Windows Task Scheduler. The same options that were specified in the UI can be specified in an XML file that will be given as a parameter to xSQLDataCmd.exe. Below is the example XML configuration to perform the same comparison as in the first case.
<?xml version="1.0" encoding="utf-8"?>
<SqlDataCompareCmdConfig xmlns="">
  <!-- **** auto-generated data compare cmd line config file, created on 10/20/2016 4:14:43 PM **** -->
  <TablesMappingRules />
    <!-- ******* custom pairs ******* -->
    <!-- Sales.SalesOrderHeader -->
    <Pair Action="Include">
      <LeftObject Name="Sales.SalesOrderHeader" />
      <RightObject Name="Sales.SalesOrderHeader" />
      <WhereClause>ModifiedDate >= DATEADD(DAY, -7,GETDATE())</WhereClause>
    <!-- **************************** -->

Now, all that needs to be done is for the task to be scheduled in windows task manager and Data Compare will synchronize all the changes made in the last week in the production database. And just so you don’t have to manually write the XML file, xSQL Data Compare can generate it for you from the UI by clicking this button.
If, for any reason, the schema in your production database has changed and is out of sync with the QA database, use xSQL Schema Compare to sync the schemas first and then sync the data.

In conclusion, apart from the scenario described in this article there are a ton of other synchronization scenarios which, by using xSQL Data Compare, can be customized to be very efficient, and have the QA environment at your disposal in a very short time. For a full reference of the available customizations check out xSQL Data Compare’s online documentation.

Tuesday, October 18, 2016

Columnstore Indexes in 2 minutes

The standard way of storing relational database tables on physical media is row based, that is, each row occupies a contiguous space. The term that is used for this type of data storage is rowstore.

In simple terms, you can think of the columnstore as a transposed rowstore. Logically nothing changes, you can still think of and see a table as a normal table with rows and columns, but physically the data is stored in a column-wise format.

Why do this and when? 

  • Think of a column like “Country” on say an “Orders” table – you basically have a handful of country IDs repeating millions of times. Just imagine the kind of data compression you can achieve on such column! 
  • Now think of a query like “get sales by country” – instead of scanning the whole table, SQL Server will only need to deal with two compressed columns and will be able to return the results many times faster using significantly less resources. 
  • When? Storing fact tables and large dimension tables in data warehouses as clustered columnstore indexes will significantly improve compression and query performance.

Why not do this?
If columnstore indexes are so great why not store tables as clustered columnstore indexes always? Just imagine what an insert|update|delete looks like in the case of a clustered columnstore index for a table that say has just 20 columns! It is kind of like doing 20 separate inserts|updates|deletes, one for each column. So, on a transactional database columnstore indexes are not a good idea.

Best of both worlds?
SQL Server 2016 lets us create an updatable non-clustered columnstore index on a rowstore table and non-clustered rowstore indexes on clustered columnstore indexes. This mixing comes with a cost in both cases but in certain scenarios the gains achieved make this worthwhile.

Our Schema and Data Compare tools support comparing and synchronizing columnstore indexes.

Monday, October 17, 2016

xSQL Schema Compare SDK v9 released

A new version of our SQL Server Schema Compare SDK is now available for download. The new version brings full support for SQL Server 2016 and the latest features of SQL Azure v12, including support for system-versioned tables, memory optimized tables,  stretch tables, security policies etc. All the details of the new release can be found here:

The xSQL Schema Compare SDK allows developers to easily integrate SQL Server database schema comparison and synchronization functionality in their own .NET applications.

You can download the new version of the SQL Server Schema Compare SDK from:

Friday, October 14, 2016

Synchronizing databases with stored procedures referencing FREETEXTTABLES

Applies to: xSQL Schema Compare v7.0.0 - v9.0.0

Some time ago, I had to copy an entire database to a new one that was already created on Azure. So, of course, I decided to use xSQL Schema Compare and Data Compare, given that the “backup/restore” option was not available. There were two elements in the database that made this case unique in regards to the schema synchronization process:
  • One of the tables (let’s call it ‘T’) had a full-text index.
  • There was a stored procedure (we will call this ‘SP’) that used table ‘T’ as a FREETEXTTABLE.

Full-text indexes are non-transactional by design, so in the synchronization script for the new database, these are added after the main transaction, which synchronizes all the other elements including the stored procedures.

The issue: The synchronization script tries to create a stored procedure which uses the table ‘T’ as a FREETEXTTABLE, before the full-text index is created. SQL Server will not allow this because to use a table as a FREETEXTTABLE, there needs to be a full-text index in this table.

The workaround: Thankfully, the comparison process is customizable and the solution to this problem is just a matter of checking/unchecking a few database objects from the comparison options. For this particular case, the comparison and synchronization needs to be done in 2 steps, each with different comparison options.
  1. Exclude stored procedures by unchecking the “Stored Procedure” checkbox (picture bellow) and compare and synchronize the databases. Schema Compare will not generate a script to synchronize stored procedures, which means that SQL Server will have no problem with adding full-text indexes at the end of the synchronization.
  2. Do the comparison again, but this time, uncheck everything except for the Stored Procedures (leaving them checked is not an issue, but it will take longer and since the other objects were synchronized, there is no need to compare them again) in the Database Objects tab. This time, a synchronization script will be generated only for the stored procedures, which were not synchronized on the first step.
Conclusion: As a general practice, to solve problems where the order in which database objects are synchronized becomes an issue, all you need to do is remove the objects that are causing problems from the comparison, compare and synchronize, and then do another comparison and synchronization only for the objects that were excluded in the first step.   

Thursday, October 13, 2016

In-memory tables in 60 seconds

As a SQL Server DBA or developer here is what you should know about memory-optimized tables (in-memory tables):

  • Introduced in SQL Server 2014
  • Primary store is main memory, second copy maintained on disk to achieve durability
  • Handled by in-memory OLTP, a memory optimized database engine integrated with SQL Server engine
  • Fully durable, ACID transactions. Supports delayed durability for better performance (risk losing  committed transaction that have not been saved to disk)
  • SQL Server supports non-durable memory optimized tables (not logged and data not persisted to disk) – no recovery option in case of disk crash
  • No-lock concurrency eliminates blocking and achieves better performance. Concurrency handled through row versioning. 
  • Not organized in pages and extents, memory-optimized tables are a collection of row versions and a row version is addressed using 8-byte memory pointers
  • Data in memory-optimized tables can be accessed:
    • (most efficient) Through natively compiled SPs (the limitations is that natively compiled SPs can only access in-memory tables)
    • Through interpreted t-sql inside a standard SP or through ad-hoc t-sql statements. 

Our Schema and Data Compare tools support comparing and synchronizing in memory tables. 

Wednesday, October 12, 2016

Stretch Databases in 60 seconds

SQL Server 2016 introduces Stretch Databases, a beautiful feature that by itself makes migrating to SQL 2016 worth it. What is it? A stretch database is basically a database with an automated cloud archiving mechanism. Here’s how it works in a few words:
  • You enable stretch database for your database and the tables you’re interested in “archiving” 
  • You decide to “archive” either a whole table, for example a history table, OR just certain rows, for example all transactions older than 12 months for a transaction table.
  • SQL Server will then silently migrate (archive) your cold data based on the criteria you defined, from the live database to Azure 
A couple of awesome things to note:
  • No need to change queries or applications – if the data a query is pulling happens to be “cold” then you may notice some latency but other than that the whole thing is completely transparent. 
  • Your data is encrypted end to end (in the live database, on the way, and in the target)
  • Faster backups, faster queries, overall significantly better performance (of course the improvement depends heavily on how you define the criteria that separates cold and hot data). 
By the way, as of version 9 our Schema and Data Compare tools support comparing and synchronizing Stretch Databases and  stretch tables.

Tuesday, October 11, 2016

Temporal Tables in 60 seconds

If you've ever designed a database you've most certainly run into the need for what we called history tables, those tables that stored the history of row changes (sometimes the whole row and sometimes just certain columns) and that were populated through insert/update/delete triggers defined on the "parent" table. With SQL Server 2016 there's no need to manually implement history tables anymore, you just define a table as a system-versioned temporal table and let the SQL Server engine take care of maintaining the row change history.

With system-versioned temporal tables pulling data from the current and the associated history table at the same time is very easy and efficient using the FOR SYSTEM_TIME clause.

System-Versioned temporal tables are supported on SQL Server 2016 and SQL Azure.

This is all you need to know in order to decide if and when you may need to use temporal tables.

PS our Schema and Data Compare tools support comparing and synchronizing System-Versioned Temporal tables.

If you wish to spend more than 60 seconds here is a brief explanation on how this works.
  • The live, temporal table has two explicitly defined datetime2 type columns referred to as period columns that are used exclusively by the system to record period of validity for each row whenever the row is modified (the columns are typically denoted as SysStartTime and SysEndTime)
  • The temporal table contains a reference to the history table (SQL Server can automatically create the history table or you can specify it yourself)
  • ON INSERT on the temporal table the system sets the SysStartTime to the begin time of the current transaction and SysEndTime to max value (999-12-31)
  • ON UPDATE/DELETE on the temporal table 
    • a new row is inserted in the history table with the SysStartTime coming as is from the temporal table and the SysEndTime being set to the begin time of the current transaction. 
    • On the live table either the row is deleted (in case of a delete operation) or the SysStartTime is updated to the begin time of the current transaction (in the case of an update). 

More details from the source:

Thursday, October 6, 2016

Synchronizing NOT NULL Columns

Applies to: xSQL Schema Compare v7.0.0 – v 9.0.0, xSQL Data Compare v7.0.0 – v9.0.0

Description: Using xSQL Schema Compare and xSQL Data Compare to keep the tables of two databases in sync is a no-brainer, and, almost always, the default synchronization options will do the trick. However, there are a few special cases in which knowing how to correctly manipulate the comparison and synchronization options is a must to complete the synchronization process correctly. One of this cases is the synchronization of NOT NULL Columns.
Let’s say there are two tables in two different databases that need to be synchronized. Both of these tables have data and one of them has one or more columns with a NOT NULL constraint and no default value. In this case, synchronization of these table’s schemas using the default options is not possible.
Reason: This is because when you generate the synchronization script xSQL Schema Compare will create the following statement:

Notice the NOT NULL without a default value specification in the ALTER TABLE statement. Logically this is correct because one of the tables has the ‘TestColumnNotNull’ with a NOT NULL constraint and no default value, so it will try to create the same column on the other table. But, because these tables both have data, adding a column in one of them would mean that the values for this column would be NULL. Since the column has a NOT NULL constraint SQL Server will not allow the addition of this column.
Workaround: The solution to this is to force xSQL Schema Compare to script the new columns as NULL by checking the ‘Script new columns as NULL’ in the comparison options dialog (picture below):

This time, when the synchronization script is generated it will not add the NOT NULL constraint, and SQL Server will allow the schema synchronization. After this, the column on the target database can be manually updated with valid data, or the data synchronization with xSQL Data Compare can be performed, which will fill the newly added column with values. To add the NOT NULL constraint, all that needs to be done is to uncheck the ‘Script new columns as NULL’ and perform the schema comparison and synchronization. This time SQL Server will have no problem with adding a NOT NULL constraint in the new column because it does not have any NULL values.
And there you go, the tables are synchronized.

Wednesday, September 21, 2016

xSQL Schema/Data Compare database list is empty

Applies to: xSQL Schema Compare v 7.0.0 to v 9.0.0; xSQL Data Compare v 7.0.0 to v 9.0.0

Summary: When adding databases to the workspace both in xSQL Schema Compare and xSQL Data Compare the first step after connecting to the specified SQL Server is to read the list of databases from the server and make that list available for the user to select the databases he/she wishes to work with. SQL Server stores the list of the databases in the master database so in order to obtain that list the account that is used to connect to the SQL Server instance should have permission to read from the master.

In the case of SQL Server this is almost never a problem as all users usually have permission to read from the master however, the situation is different in SQL Azure. Often, the developers only have access to certain databases on an Azure account and do not have permission to read from the master. Therefore, in these cases, the Schema and Data Compare tools are not able to read the list of available databases.

Symptoms: After providing the login credentials on the "Add Database" dialog window and clicking on the "Refresh" link-button to populate the "Databases" window the list fails to populate and the database window remains empty. You are thus not able to proceed with the comparison and synchronization operation.

Cause: The account used for connecting to the SQL Server instance or SQL Azure does not have permission to read from the master database.


  • Install the latest build from our website - builds v. and higher include a fix that eliminates the need to read from the master db, but only for the default database for that user. If you need to add a database other than the default one then continue reading. 
  • Grant read permission on the master database to the account you are connecting with. 
  • Login with a different account that does have read permission on the master database.

Future versions of the compare tools: both comparison tools will eliminate the need for reading from the master database by allowing the user to specify the database(s) to be added.

Workaround: xSQL Schema and Data Compare tools come with Command Line Utilities that allow you to specify all the comparison and synchronization details, including the database name, in a XML file which is given as an argument to the comparison command, thus eliminating the need to read from the master. The command line utilities are intended primarily for unattended, automatic database comparison and synchronization operations and they provide great convenience and utility for that purpose but you don't get the benefits of the interactive GUI.

Tuesday, September 13, 2016

Migrating to Azure - a very helpful article

Our friend Albert Tollkuci just posted an article about migrating to Azure that a lot of you will find very helpful:

Of course we like the article even better since he has included our Schema and Data Compare tools into his migration process but even if our tools were not mentioned we found the article worth sharing.