Wednesday, December 28, 2016

Failed to map database objects. An item with the same key has already been added.

We just released a new build (9.0.5.0) of xSQL Schema Compare tool that addresses a recently reported issue with object mapping. In certain cases the database schema comparison may fail in it's first stage (object mapping) with the error message "Failed to map database objects. An item with the same key has already been added." 
If you run into this issue please download and install the latest build from our website: https://www.xsql.com/download/sql-server-comparison-bundle

Friday, December 16, 2016

Synchronization tools for SQL Server Express

Most of the tutorials/case studies I have presented in this blog have had SQL Server Enterprise edition as their primary focus. However, the reality is that, this version, if you didn't get the hint from its name, is mostly for big enterprises that need advanced features like SQL Server Agent or Analysis Services. In any other case this version is a bit of an overkill. In these cases, SQL Server Express is a very good starting point because it's free and offers a lot to work with, such as a full database engine, full T-SQL support, full text search, native XML, and some very useful built in tools like SQL Server Management Studio. A few classic examples where SQL Server Express is a very good option are:

  • Deploying desktop windows solutions that use SQL Server to users who do not own a SQL Server. Given that it's free, its setup can be embedded in the application's setup so that Windows application can enjoy most of SQL Server's functionality.
  • Lightweight web applications that only need to use small SQL Server databases such as websites using a CMS like Umbraco.
  • Testing and developing applications with a local database.
Although it has some limitations regarding the processing power it can use, computer memory available and database size, it is suitable for a wide range of applications. Now, this might be something that one does not think of right away, but at some point in time, it's certain that there will be the need to perform some kind of database synchronization. This is where SQL Server Express has another advantage. Some of the synchronization tools, xSQL Schema Compare and xSQL Data Compare included, follow the same 'no charge' pattern set by Microsoft for this version of SQL Server. The Lite version of these two tools, when used with SQL Server Express offers the full functionality of the licensed version. Let's first have a look at the windows desktop solution case which, in my opinion is a very interesting case where schema synchronization is useful.

Suppose you have created and deployed a desktop Windows application that uses a SQL Server database. A good example here might be an app that does some sort of system monitoring and stores any data it finds in the database. SQL Server Express is a clear choice for the database because there is no complex functionality required and the database size is very likely to be small. Let's also suppose that you have just developed a major update for the app that includes a lot of database changes. How would you go about transferring those changes in the clients' databases? One option would be to generate the full schema script from SSMS and during the update's installation on the client, run that script to recreate the database. As you can imagine this is not an optimal choice because it would delete all the data that are already saved in the database. Another option is to manually generate the script but, in most cases, simply mentioning this, would get you a few "are you kidding?" looks as it is a very tedious and error prone process. Buying a comparison tool seems like a big waste of money if you're going to use it just for this one thing, so here is where the Lite version of xSQL Schema Compare comes to the rescue. For absolutely no cost you get a tool that, on SQL Server Express, will compare the 2 database versions and generate a synchronization script that can be run during the installation process and perform all the necessary changes in the client database.

A scenario in which data comparison on SQL Server Express might be necessary is the development of websites with a CMS. Content Management Systems store all the content management-related data in a database. Take Umbraco for example. It uses an element called Document Type to provide the structure of different webpages. This element stores all the info it needs in a database which can very well be a SQL Server Express database as this type provides the necessary functionality and more. It's always a good idea to develop these sites locally or in a development server and once the changes are fully tested, upload those on the live server. Of course along side this upload, the data on the database need to be transferred as well so that the site admin won't have to recreate the new content structures. Backing up and restoring the database is not a good idea because there are some data (like the actual content of the site) that do not need to be transferred, and if the live site is on a Cloud service like Azure, restoring is not possible. Again, the best option here would be a synchronization tool and a very good choice is xSQL Data Compare. Since the synchronization will be performed on SQL Server Express, the FREE Lite version of xSQL Data Compare can be used to transfer the data. Also, the synchronization process is highly customizable (refer to the online help), so you can manually determine which tables and what data in those tables you want to synchronize. This way, only the data regarding the content structure will be transferred.
In conclusion, I think it's safe to say that the two scenarios mentioned here are a very small portion of the huge number of cases where the application of SQL Server Express is enough to fulfill a software's database needs. Keeping in mind that database synchronization is a process that sooner or later will take place in most of these cases, the extension of the 0 cost principle of SQL Server Express to its synchronization tools like in the case of the Lite versions of xSQL Data Compare and Schema Compare is something that can greatly reduce the development and maintenance costs of software.


   

Monday, December 12, 2016

Database synchronization in the Software Development Life Cycle

Reading the title, most of you are probably thinking: What does database synchronization have to do with the Software Development Life Cycle (SDLC)? Stick around, because I'll get to that in just a minute. First, let me give a quick introduction to SDLC for those one or two developers out there who, by an impossible twist of fate, may have never heard of SDLC in their working experience. SDLC is a multi-phase process which ensures that good software is supplied to the customers. The term 'good software' has quite a broad definition but generally, 'good software' implies software that meets the requirements, has been tested thoroughly and has minimal errors. SDLC has a number of variations, but usually the software will go through these 5 stages:

  1. Requirement analysis 
  2. Design
  3. Implementation / Development
  4. Testing
  5. Maintenance
Since, the focus of this article is database synchronization, I'll skip the first three stages and go straight to the stages which deal with database synchronization, which are testing and maintenance.

In the context of testing, database synchronization is useful in preparing the testing environment. For example, usually when a web application or a new feature is completed it will first be published on a test domain which is made available only to some chosen individuals / groups. This test site will probably need its own database that will have to be created or updated every time a change is made. You could backup the development database and restore it in the test environment or generate the database's script and execute it on the test environment but this would require the database to be recreated every time which can be a very time consuming process for large databases. Also, this is not possible if the databases are in the Azure cloud environment. The best option here is to use a synchronization tool like xSQL Schema Compare or xSQL Data Compare. So, you could use xSQL Schema Compare to transfer any schema changes from the development database to the testing environment and xSQL Data Compare to transfer any changes in lookup data. As a side benefit, xSQL Schema Compare can also be used to take periodic snapshots of the database in order to keep track of the changes made to it and rollback any of them is necessary.

Since the synchronization process is fairly straightforward and more or less the same in most of the scenarios, I'll first explain the other scenario in which database synchronization is necessary and then demonstrate how it's done and how to automate it.

The other, very important stage where database synchronization might take place in, is maintenance. In this stage, this process is done in both directions. First of all, for every bug fix or new feature, schema changes and lookup data are transferred from the development environment to the testing and then the live one. This process is the same as the one in the testing stage. Secondly, to make sure that the software is working correctly, quality assurance is performed. Quality assurance can have its own environment or it can use the testing environment. One thing is for certain though, it needs to have data that is as close as possible to the live data. Obviously, no type of data satisfies this condition better than the live data itself. By using xSQL Data Compare, these live data can be transferred to the quality assurance environment where they can then be used as needed.
Without further ado, let's proceed to a demonstration of schema and data synchronization. I'll be using the AdventureWorks2012 database in this demo. I made a copy of this database (AdventureWorksCopy) which will be used as the testing environment's database. Suppose this database belongs to a company that has decided to extend its medical and dental benefits to the employee families. Obviously, they will want to have some data in the system for the employee's family members. To do this, a table called EmployeeDependant is added to the HumanResources schema in the development database which has records for the employee's families. Of course, before this feature can be published it needs to be tested, which means that the schema changes need to be transferred to AdventureWorksCopy. There are 4 very simple steps to this process.

  1. Add the databases in xSQL Schema Compare.
  2. Compare AdventureWorks2012 with AdventureWorksCopy. The comparison reveals that there is a table EmployeeDependant in AdventureWorks2012 that does not exist in AdventureWorksCopy (note the one-way arrow to the right).
  3. Generate the synchronization script.
  4. Execute the script. By opening the database in SSMS I can see that the EmployeeDependant table is added to the AdventureWorksCopy database.

The new EmployeeDependant table has a column named 'RelationshipWithEmployee'. Instead of it being a simple VARCHAR column it's better to have it as a foreign key to a 'Relationships' table. The records in this table will probably never change (there is a fixed number of relationship types in a family), so it can be used as an example of the lookup data that may need to be transferred from the development environment to the testing one. Doing this synchronization with xSQL Data Compare is, again, a very straightforward process. Before I show this process' steps, here is a screen shot of the data in the Relationships table that will be transferred to the AdventureWorksCopy database:

To add these data to the Relationships table in AdventureWorksCopy database do the following:

  1. Add the databases to xSQL Data Compare.
  2. Choose the objects that need to be compared. In this case, only the Relationships table will be synchronized, so only that table will be selected from the objects to be compared.
  3. Compare the tables
  4. Generate the synchronization script for the AdventureWorksCopy database, Here is the script that is generated:
    SET XACT_ABORT ON;
    SET ARITHABORT ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
    
    BEGIN TRANSACTION;
    
    
    /*-- -Insert(s): [HumanResources].[Relationships] */
    SET IDENTITY_INSERT [HumanResources].[Relationships] ON;
    INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(1,'Spouse');
    INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(2,'Sibling');
    INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(3,'Parent');
    INSERT INTO [HumanResources].[Relationships] ([Id],[RelationshipName]) VALUES(4,'Child');
    SET IDENTITY_INSERT [HumanResources].[Relationships] OFF;
    
    
    
    COMMIT TRANSACTION;
    
  5. Excecute the script. After execution, if a select query is run on the Relationships table AdventureWorksCopy database, it will display the following data: 


Using a similar process to the one in this demo you can copy the data in the live database in the Quality Assurance database and run any tests that you need.

In most cases, these tasks are repetitive. For example, an organization might have the practice of synchronizing the live data with quality assurance every 3 days. Instead of doing this manually every time, you can just generate a XML configuration file for xSQL Data Compare Command Line by clicking the button in the picture below and then, using Windows Scheduler, schedule the comparison to be run periodically with that XML file as an argument.
Automation of the schema comparison is exactly the same as automation of data comparison. The only difference is that xSQL Schema Compare Command Line needs to be used.

In conclusion, the benefits of using synchronization tools like xSQL Schema and Data compare are quite obvious. They can simplify and automate parts of the SDLC that are trivial and boring and would otherwise, take a relatively long time to complete.