Monday, October 31, 2016

t-sql random string generator

Note: originally published in our old blog on December 2008.

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.

CREATE PROCEDURE [dbo].[usp_GenerateRandomString]
  @sLength tinyint = 10,
  @randomString varchar(50) OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @counter tinyint
  DECLARE @nextChar char(1)
  SET @counter = 1
  SET @randomString = ''

  WHILE @counter <= @sLength
    BEGIN
      SELECT @nextChar = CHAR(ROUND(RAND() * 93 + 33, 0))
      IF ASCII(@nextChar) not in (34, 39, 40, 41, 44, 46, 96, 58, 59)
        BEGIN
          SELECT @randomString = @randomString + @nextChar
          SET @counter = @counter + 1
        END
    END
END

Thursday, October 27, 2016

Distributed Databases - One Way Synchronization

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

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

/*-- -Delete(s): [dbo].[Products] */

/*-- -Update(s): [dbo].[Products] */
SET IDENTITY_INSERT [dbo].[Products] ON;
UPDATE [dbo].[Products] SET [QuantityPerUnit]=N'35 gift boxes' WHERE [ProductID]=20
SET IDENTITY_INSERT [dbo].[Products] OFF;

/*-- -Insert(s): [dbo].[Products] */
SET IDENTITY_INSERT [dbo].[Products] ON;
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(8,N'Northwoods Cranberry Sauce',3,2,N'12 - 12 oz jars',40.00,6,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(9,N'Mishi Kobe Niku',4,6,N'18 - 500 g pkgs.',97.00,29,0,0,1);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(10,N'Ikura',4,8,N'12 - 200 ml jars',31.00,31,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(11,N'Queso Cabrales',5,4,N'1 kg pkg.',21.00,22,30,30,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(12,N'Queso Manchego La Pastora',5,4,N'10 - 500 g pkgs.',38.00,86,0,0,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(13,N'Konbu',6,8,N'2 kg box',6.00,24,0,5,0);
INSERT INTO [dbo].[Products] ([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) VALUES(14,N'Tofu',6,7,N'40 - 100 g pkgs.',23.25,35,0,0,0);
SET IDENTITY_INSERT [dbo].[Products] OFF;


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:

First 100 get Silver for $100

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

Syncing QA data with Production

Applies to: xSQL Data Compare v7.0.0 and higher

WARNING: this article focuses on the data transfer / synchronization task and does not address operation security issues and sensitive data scrubbing / obfuscation which are critical whenever dealing with live, production data.

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="http://www.xsqlsoftware.com/schemas/xSQLDataCompareCmdSchema.xsd">
  <!-- **** auto-generated data compare cmd line config file, created on 10/20/2016 4:14:43 PM **** -->
  <LeftDatabase>
    <SqlServer>Production</SqlServer>
    <DatabaseName>AdventureWorks2012</DatabaseName>
    <TrustedConnection>true</TrustedConnection>
  </LeftDatabase>
  <RightDatabase>
    <SqlServer>QualityAssurance</SqlServer>
    <DatabaseName>AdventureWorksCopy</DatabaseName>
    <TrustedConnection>true</TrustedConnection>
  </RightDatabase>
  <TablesMappingRules />
  <TablePairs>
    <!-- ******* custom pairs ******* -->
    <!-- Sales.SalesOrderHeader -->
    <Pair Action="Include">
      <LeftObject Name="Sales.SalesOrderHeader" />
      <RightObject Name="Sales.SalesOrderHeader" />
      <WhereClause>ModifiedDate >= DATEADD(DAY, -7,GETDATE())</WhereClause>
    </Pair>
    <!-- **************************** -->
  </TablePairs>
  <CommandLineOptions>
    <ErrorLogName>xSQL</ErrorLogName>
    <ErrorLogType>WindowsLog</ErrorLogType>
    <OverwriteComparisonLog>true</OverwriteComparisonLog>
    <Direction>RightAsLeft</Direction>
    <Synchronize>true</Synchronize>
  </CommandLineOptions>
</SqlDataCompareCmdConfig>


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 Schema Compare for SQL Server 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 onlinedocumentation.

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: 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

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.