Monday, October 31, 2016

CodeProject
This is a simple stored procedure that you can use to generate a random string. You can use it as a random password generator and for other purposes. It is very simple so I won't bore you with unnecessary explanations but if you do happen to have any questions feel free to post them here.

Thursday, October 27, 2016

Business Need

A very common organization of the data infrastructure for big companies who operate in many countries and use some type of centralized system to manage their operations, is to have separate databases for each branch and one database that is operated by the headquarters that has the following data in it:

A copy of all of the transactional data (sales, orders, etc.) from each branch.

Lookup data like lists of products, services, categories, etc.

Both types of data need to be periodically synchronized between the central database and the branches’ databases. There are two directions for this synchronization.

Central -> Branches

This synchronizes the lookup data. For example, every time the company decides to add new products, services or even a new country where it operates, the logical way to go about this is to add the new data to the lookup tables in the central database and then synchronize the branches’ databases with this central database.

Branches -> Central

This synchronizes transactional data. This type of synchronization can be done at the end of every business day to transfer all the new or updated transactions from the branches to the central database which can then be used by the company’s HQ to build different types of reports.

Solution

xSQL Data Compare’s one-way synchronization. If the synchronization script is generated using the default options, it will make the database upon which it is executed, the same as the database with which it is compared. But in this case, that is not the desired result. Thankfully xSQL Data Compare offers the option to choose between synchronizing the left, right or different rows, or a combination of these options. This means that if you choose to synchronize only the right rows, rows that are in the right database but not in the left would be copied to the left, and rows that are in the left DB but not in the right would not be deleted.

To demonstrate this, below are the comparison results for the Products table of two Northwind databases (NORTHWND and NorthwindCopy). The Products table in the NORTHWND database does not have products with ID 8 to 14. The Products table in the NorthwindCopy database does not have products with ID 1 to 7. Also, the data for the product with id 20 is different in the NorthwindCopy database. A row is considered different if that row exists on both tables with the same primary key and at least one of the other fields is different. The goal here is to copy products with ID 8 to 14 and the changes in the product with ID 20 from the NorthwindCopy to NORTHWND. This means that xSQL Data Compare needs to generate a script for the left database (NORTHWND) where only the different and new rows from the right database will be synced. These rows will be left checked.

So, all the right rows will be checked:

And all the different rows:

To make sure that none of the rows that are in the left database’s table but not in the right is deleted, all the left rows will be unchecked:

Doing this will generate the following script (when Generate script for NORTHWND is clicked):

As you can see, INSERT statements are generated for products with id 8-14. An UPDATE statement is also generated for Product with ID 20 and there are no DELETE statements.

Execute the script and there you have it, all the rows that are different and in NorthwindCopy but not in NORTHWIND are copied to the latter. To do the opposite, simply check all the left rows, uncheck the right and generate a script for NorthwindCopy.

One thing to note: In real life scenarios, to use this technique, it would be preferable to have all the Primary keys as ‘uniqueidentifiers’ to avoid primary key collision.

Automation

As always, xSQL Data Compare Command Line can be used to automate the entire process in order to perform this synchronization periodically. All you need to do is after you have specified the rows you want to sync is to generate the XML file that will be passed as an argument to xSQLDataCmd.exe. This can be done by clicking this button:

Save $199 today! You can get a 1 year Silver Subscription for only $100 if you act fast. Use code SILVER100 on checkout.
The code will be disabled either when the 100-th subscription is claimed OR at the end of the day on October 28, 2016 (Eastern Time), whichever comes first.

Also in case you missed our announcements, new versions with full support for SQL Server 2016 and SQL Azure v12 are available for the following tools:

The new versions include support for security policies, encrypted columns, stretched databases, system-versioned tables etc. A link to the complete list of the new and improved features included in the new versions can be found at the end of the description paragraph on each product's page.

Thursday, October 20, 2016

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.

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

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.

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

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.

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.

Friday, October 14, 2016

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

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.

Do
the comparison again, but this time, uncheck
everythingexcept 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.

Wednesday, October 12, 2016

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)

Tuesday, October 11, 2016

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.

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

Thursday, October 6, 2016

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.