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: https://www.xsql.com/products/sql-server-comparison-bundle/release-notes/version-9

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: https://www.xsql.com/download/sql-server-schema-compare-sdk

Read More

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.   

CodeProject

Read More

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. 

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

Read More

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). 

CodeProject By the way, as of version 9 our Schema and Data Compare tools support comparing and synchronizing Stretch Databases and  stretch tables.

Read More

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:

• Creating a System-Versioned Temporal table: https://msdn.microsoft.com/en-us/library/mt590957.aspx
• Querying System-Versioned Temporal tables: https://msdn.microsoft.com/en-us/library/mt591018.aspx

CodeProject

Read More

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. CodeProject

Read More

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.

Resolution:

• Install the latest build from our website - builds v. 9.0.1.0 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.

Read More

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: http://www.tollkuci.com/blog/post/Migrating-to-Azure-Quirks-Tips

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.

Read More

xSQL Compare v9 w/ support for SQL 2016 and SQL Azure v12 now available

We are excited to announce the release of version 9 of our SQL Server Schema and Data Compare tools with full support for SQL Server 2016 and latest features of SQL Azure v12! The new version includes support for security policies, encrypted columns, stretched databases, system-versioned tables etc. A complete list of the new and improved features included in this version can be found at http://www.xsql.com/products/sql_server_comparison_bundle/release_notes/v9/ .

Here are a few important things to note:

• if you have an active Silver or Gold subscription you can simply download and install the latest build from our site and you are set. The new build will install over the exiting one and the license will remain intact, support for SQL 2016 will be included.
• if you have a perpetual license with an active maintenance agreement, you should have received a new license via email last week. Please note that the new license was sent to the email address that was used when purchasing the original license. Once you activate the new license the tools will then support SQL Server 2016.
• if you have a perpetual license but no active maintenance you will need to purchase an upgrade license of the same "class" as the original license in order to be able to compare SQL 2016 databases. You may also switch from a perpetual license to a subscription if you wish. Note that you can download and use version 9 with your old license but SQL 2016 comparison and synchronization will not be supported.
• if you are a lite edition user you may download the latest build to take advantage of the improvements and fixes that the new version brings, but unfortunately you will not be able to compare and synchronize SQL Azure databases.

Please send all questions and / or suggestions to support@xsql.com 

Read More

Let us do your work for FREE

Are you struggling with a SQL Server problem / task? We can help for free, no strings attached, no obligation whatsoever. In this little “transaction” you are supplying us with real life scenarios and we are giving you a certain amount of expert hours in return. Here are the conditions /rules:

• The problem or task should in general not require more than about 2-3 hours to solve / complete.
• We will have the right to de-personalize and generalize the problem / solution and then publish it as we see fit for the whole SQL Server community to benefit from it.
• We will choose the problems / tasks to work on based on the appeal we think it will have to the community at large and not based on how important or urgent it may be to you.
• We will let you know within 48 hours if we will work on your problem and when to expect a solution. Our goal, but not guarantee, will be to let you know within the day and have a solution ready within 2 days.
• No guarantees, written or implied. The responsibility for adopting / implementing the solution we provide is solely yours.

We may put a form up on our website but for now please email the task/problem you would like us to work on at support@xsql.com – here are some guidelines:

• Write “Solve my problem” on the subject line
• Provide the following information on the body of the email:
• Your full name
• Company name
• Your position in the company
• Your email address
• Clear and concise description of your task / problem
  SQL Server versions/editions where the solution will be implemented
• Acceptance criteria for the solution we provide
• Your desired “deadline” (when you would ideally like to receive the solution from us)

Your personal information, your company information and any information related to your environment will never be published. The problem description and the solution will be completely anonymized before they are published (if and when we decide to publish them).

Go ahead and email us, see how good it feels to have an expert work for you and cost you nothing!

Read More