How to deploy a SQL Server database to a remote host

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

1. First time deployment
• Backup / restore
1. Backup the database on the source
2. Copy the backup file to the target machine
3. Restore the database on the target
4. Create logins and set permissions as needed
• Compare and Synchronize
1. Create database on the target machine (blank)
2. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
3. xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)
2. Database exists in the target server
• Compare and Synchronize
1. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
2. Use xSQL Data Compare to push any data you need to push from the source to the target. Caution: be careful not to affect any data that exists on the target already.

CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

1. Restore the remote database on your local environment
2. Use xSQL Object to compare your source database with the restored database. Generate the schema synchronization script and save it.
3. Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it. 
4. Deploy your schema synchronization script to the target machine. 
5. Deploy your data synchronization script to the target machine.

Both xSQL Object and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

xSQL Database Compare tools V3.5 released

We just released version 3.5 of our database comparison and synchronization engine xSQL SDK, the schema comparison and synchronization tool xSQL Object, and the data compare and synchronization tool xSQL Data Compare.

The new version includes:

• Support for SQL Server 2008 R2.
• The passwords for SQL Server connections are now encrypted and stored in the workspace file thus eliminating the need to enter the password each time the xSQL comparison tools are launched.
• Fixing the discrepancies between the name in the object definition and the name in the system catalog. This enhancement addresses an issue caused when the sp_rename procedure is used to rename objects with sql definition such as procedures, views, functions and triggers. The procedure sp_rename in these cases changes only the name in the sys catalog, but it does not change the name in the object's definition. This particular problem manifests itself mostly on SQL Server 2000 databases.
• A new schema options named "Include objects with invalid dependencies" replaces the previous option named "Include views with invalid dependencies"; in addition to views this option now affects stored procedures, functions and triggers.
• Trial expiration issue on Windows Vista and Windows 7 with UAC on. The enhancement addresses an issue with early expiration of xSQL Object and xSQL Data Compare trial period on Windows Vista and Windows 7 when the UAC is turned on - it guarantees that both tools will be fully functional with no restriction for the duration of the trial period. After the trial, the products will revert to Lite edition.
• Some enhancements and bug fixing in the command line utilities including a newly added error code that tracks script execution errors.
• A fix to stored procedure multi-line comments.
• An enhancement in the way xSQL Data Compare handles identity columns. Explicit updates of the identity columns are no longer supported and will trigger data warnings.

You can download the new version from: http://www.xsql.com/download/

Compare and Synchronize SQL Server Databases

Why compare databases?

Whether you are a developer that uses SQL Server on the back end to support your application or a database administrator in charge of monitoring and maintaining those SQL Server databases you often have to deal with different versions of the same database. For example, you may have a development environment, a testing or QA environment and a production environment and at any given time your database is in different "states" in each of those environment. As you work on implementing changes and additions to your applications you routinely make changes on the development database like adding tables, adding columns, changing types, creating indexes, adding new and modifying existing views, stored procedures, user defined functions etc.

At different points in the development you may need to promote all the work you have done on to the QA environment so that the QA team can start "pocking holes" in the work you have done. As soon as you do that you go back to your development World and continue working on your application and making database changes as the need arises so, it won't be long before the version of the database you are working on does not match the version the QA team is testing and that does not match the version of the SQL Server database that is running on your production servers.

Any professional in this position will keep meticulous records on each version of the database so that at any given time he would be able to tell what is different from one version to the other. That's well and good but it does not take much to realize that, especially if the database is relatively big and changes relatively many, sorting through such records to figure out what changes when (let alone how to change it back if necessary) is like looking for the needle in the haystack. That's why the need to have a tool that compares the SQL Server databases and tells you what is different.
 
How do I compare two databases?

xSQL Object which you can download from this site allows you to compare the schemas of two SQL Server databases with a few clicks, regardless of the location of those databases (as long as you can access those databases). The results of the comparison are displayed on an easy to use grid that shows the objects on the top section and the differences for the selected object on the bottom section. In addition of the side by side display of the selecte objects' scripts it also provides two change scripts - one to make the object on the second database in the comparison the same as the corresponding object in the first database in the comparison and abother change script to go the other way.
 
What do I do after comparing the two databases?

After you have compared the SQL Server databases in question, knowing on which direction you wish to transfer changes (example: if you were comparing the development version of your SQL Server database with the QA version it is likely that you would want to transfer changes from the development version to the QA version) you simply click on the right button (link) and xSQL Object automatically generates the change script required to transfer those changes. The script generated is safe and version specific (this is great for cases when you have different versions of SQL Server running on different environments - for example: you have upgraded the development environment to SQL Server 2005 or SQL Server 2008 but your QA and production environments are still on SQL Server 2000). At this point you can simply review the script to ensure that you do indeed intend to make those changes on the target database and then execute the script directly from xSQL Object's interface.
 
How about the data - the content of two SQL Server databases?

So, what if you have some sort of a distributed SQL Server database or a replicated database or some other scenario where you need to compare the actual data between two SQL Server databases to make sure that the replication has worked correctly or to see what records were changes since the last backup if you are trying to perform some auditing or for some other reason? Well, we thought of that to: xSQL Data Compare allows you to compare the data, the content, of two SQL Server databases, see where the differences are and then transfer all or some of those changes from one database to the other. xSQL Data Compare comes together with xSQL Object and the corresponding command line utilities as part of the xSQL Bundle that you can download from this site.

How much am I going to have to pay for those tools?

Not much at all - in fact it is likely that you may not have to pay anything - xSQL Bundle which includes both xSQL Object and xSQL Data Compare is completely free for SQL Server Express and comes in a free Lite Edition for other editions of SQL Server.

Publish database changes to hundreds of clients - software publisher

This is a "how to" written with software publishers in mind but it will benefit other software development groups that may not be classified as software publishers.

Here is the scenario: you have tenths, hundreds or maybe more clients using the software you wrote that utilizes SQL Server on the backend. The first release was painless but all the subsequent releases have given you grief - you send out database upgrade scripts with clear instructions but the users just don't seem to be capable of following those instructions and then they call you..., so you are looking for a way to make this process pain free. Well, you are in luck as nowadays there is a tool for everything. Following, I will provide some guidance for each of the scenarios you may be facing.

1. If you cannot access the clients' servers from your location (most likely scenario)
1. if you have a small number of database versions on the field and each version's schema is "un-touched" - that is you (or the client) did not make any changes to it then the most efficient way to handle this would be to use xSQL Object to compare each previous version to the new "master" version and generate a sync script for each version upgrade and then use Script Executor to package the sync script and any additional scripts you may want to run on the clients' servers into a executable which can then be made part of your installation package;
2. if you have many versions out in the field and what's more critical you may have made (or allowed the client to) customizations for different clients then the approach described above would not work very well, but xSQL Builder was designed exactly with this scenario in mind. In just few easy steps you can generate an executable package that embeds the schema of the master database you wish to publish. When the executable is launched on the client's site it will compare the currently installed version with the embedded master schema and synchronize the target to the master. While doing that it will generate a detailed synchronization log and automatically email it back to you so that you know what happened when the client ran the upgrade. 
2.  If you happened to have access to all your clients' databases then you can easily manage this from your location. Use xSQL Object to generate synchronization scripts for upgrading from version x to the current version, then use Script Executor to create a deployment package - basically add all your target servers (databases) into database groups, add your sync scripts into script containers and then map script containers to databases and execute ALL with a click of a button.

If you happen to face a scenario not covered by the above options please do let us know.

Applies to:

• software publishing
• publishing database schema changes
• synchronizing clients' databases (the schemas) to a master database
• comparing and synchronizing database schemas
• executing multiple scripts against multiple databases

Backup database structure only

Sometimes you need to back up the database structure (schema) only – it can be because you simply would like to maintain an audit trail of database schema changes or because you need to compare the schemas of two databases that you can’t access directly from one location.

Whatever your reasons maybe xSQL Object provides the ideal solution to accomplish this – with a click you can take a snapshot of the SQL Server database schema. The schema snapshot contains all the schema information but no data and consequently is a very small size file. You can then use xSQL Object to compare this schema snapshot to other snapshots or to live databases and see exactly what changed from one schema version to another.

Synchronize two SQL Server databases

You are furiously pounding code on your computer and in the meantime making small changes to the local (development) version of the database as the need arises – add a column, change the type and width of another, add a view, make a change to a stored procedure and so on. At some point in the early morning hours as the World wakes up you are ready to publish your work on the production server, somewhere… – just a few clicks and the early risers will see your masterpiece (application) in action. Well, not so fast unfortunately, all those changes you made to the development database must be made to the production database otherwise your application is not going to work, is it? Now, that is some painful and tedious work to say the least. What if you forget something? And here is a twist to make this a real nightmarish scenario – remember last week when the client needed those urgent changes and you were forced to do the “unthinkable” – make changes right on the production database! Worst yet, you realize that you never got around to bringing those changes down to your local, development database – no words can describe the pain you must feel at that moment!

Is this time for panic? No it is not, it is time for xSQL Object – in just a few clicks it will show you exactly where the differences are on both sides and better yet auto-generate the scripts that you need to quickly and safely make the changes.

Download it now and eliminate “the pain” – free lite edition with no strings attached. Supports SQL Server 2000, SQL Server 2005 and SQL Server 2008.

New build of xSQL Bundle available

A new build of xSQL Bundle that includes xSQL Object for database schema comparison and synchronization and xSQL Data Compare for comparing and synchronizing data is available for download from: http://www.xsql.com/download/sql_server_comparison_bundle/

The new build adds the ability to include and exclude object level permissions from the synchronization. Permissions are now shown under each object they're granted to or denied on. Statement permissions are listed under the database node in the comparison grid.

Right tool for the job - what our customers say

It is always nice to see a nice blog post about your products but it is awesome to see a paying customer praising the product without being asked to do that and without asking for any favors in return! I just ran accross one such blog post that was published today:
http://catholicinformation.aquinasandmore.com/2009/05/06/using-the-right-tools-for-the-job/ - it is in such cases that we can proudly say: you don't have to listen to us - see what others are saying...

Support for Change Tracking and Data Compression

xSQL SDK 3.0 and xSQL Object 3.0 support the new change tracking and data compression features that SQL Server 2008 introduces.

By default change tracking is NOT compared and neither is synchronized however the user can turn that on easily. It is important to note that Change Data Capture, which is different from Change Tracking, is not supported in this version of xSQL Object and xSQL SDK.

xSQL SDK and xSQL Object recognize, compare and synchronize data compression property for the following database objects:

• Tables
• Primary Keys
• Unique Keys
• Indexes

By default data compression is compared and synchronized.

New data types supported by xSQL Software's database comparison tools

The just released xSQL Object v3, xSQL SDK v3 and xSQL Builder v2 support all new data types that Sql Server 2008 introduces as well as the enhancements on the existing data types. xSQL Object allows you to compare and synchronize SQL Server database schemas, xSQL SDK allows you to integrate database schema comparison and synchronization in your application and xSQL Builder allows you to package SQL Server databases in an an easy to deploy executable.

Following is the list of the new and enhanced SQL Server 2008 data types

• date: a date/time data type that stores only the date portion.
• time: a date/time data type that stores only the time portion.
• datetime2: similar to existing datetime data type, but with a larger fractional seconds and date range.
• datetimeoffset: a date/time data type that has time zone awareness.
• geography: a new data type for geodetic spatial data defined latitude and longitude coordinates.
• geometry: a new data type for planar spatial data defined by coordinate on a plane (used mostly for maps).
• hierarchyid: used to store hierarchical relationship.
• FILESTREAM varbinary(max): a variation of varbinary(max) that stores the BLOB data as file in the file system outside the database.

You can download those products from http://www.xsql.com/download/

Support for SQL Server 2008 – new release

We are very excited to announce a major new release of the following tools which now provide full support for SQL Server 2008:

• xSQL Object V3 (free for SQL Server Express)
• xSQL Data Compare V3 (free for SQL Server Express)
• xSQL SDK V3
• xSQL Builder V2
• RSS Reporter V3 (free for one SQL Server instance)
• xSQL Object Search V2 (free tool)
• xSQL Script Executor V2 (free tool)

What's new - a detailed description of changes and enhancements on this new release can be found at: <>. Following are some highlights:

• Support for all new or enhanced data types such as geography, geometry, hierarchyID etc.
  Support for the table level change tracking feature introduced by SQL Server 2008.
• Data compression support for tables, primary key constraints, unique constraints and indexes.
• Support for the enhanced full-text catalogs and indexes.
• Support for Remote Service bindings for Service Broker.
• Improved object dependency handling.

You can download those tools from: http://www.xsql.com/download/

Tracking database schema changes - why and how

There are many scenarios that highlight the need for tracking changes to the database schema – security, auditing and performance issues often require that you be able to pinpoint what changed when in the database schema. An index may have been dropped, a stored procedure may have been changed, a trigger may have been added and so on, and any one of those changes, however “small”, could have huge un-intended consequences from performance degradation to loss of data integrity and more.

Why can’t we use the daily full database backups to always go back and figure out what changed when – after all the database backup does not only capture the data but it also captures the database schema? Yes, BUT:
- full database backups are in many cases only available for the last few days / weeks and you are not able to go back beyond that period;
- it takes a long time to let say restore 20 different versions of a large database and figure out what changed in the schema from one version to the other
in short, even if it is possible it is not practical.

A nifty feature in xSQL Object called “schema snapshot” addresses this issue in very elegant and practical way – it allows you to take snapshots of the database schema that:
- capture 100% of the database structure – not the data, just the schema;
- have a very small footprint thus enabling you to store them forever – the database itself could be in the tenths or hundreds of Gigabytes in size whereas the schema snapshot could be a few Kilobytes in size;
- can be compared with each other and with the live database instantly without having to restore anything – instead of taking days restoring and comparing trying to track down a change you can do that in minutes using the snapshots.

xSQL Object is free for SQL Server Express with no limitations and is also free for other editions of SQL Server as long as the number of objects in the database does not exceed certain limits that you can find here…

xSQL Bundle receives highest marks and praise from SSWUG

Just realized that we never published a link to this review of our database comparison tools xSLQ Object and xSQL Data Compare. The review was conducted by Stephen Wynkoop, Microsoft SQL Server MVP and SSWUG founder who was duly impressed with our products and gave them the highest rating possible in all aspects of the review, overall score, installation, usage and real-World usefulness. You can read Stephen's review here:
http://www.xsqlsoftware.com/reviews/sswug_xsql_compare_review.pdf