Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Simple SQL Server Code Deployments with xSQL


By:   |   Read Comments (2)   |   Related Tips: More > Application Development

Problem

To put it mildly we have been having challenges with our code deployments.  Just about every time we have a deployment there are issues.  It never fails.  Some small and others causing downtime.  We have issues with consistently deploying code partially because my team is stretched thin.  Unfortunately, the code deployments are starting to reflect poorly on our Developers and DBAs.  I think I am finally getting through to my team that our code deployments are serious and business critical.  Now I need to figure out how to build a solution.

Solution

To me all solutions are the combination of people, processes and technology, and code deployments are not an exception.  As technology professionals, we generally gravitate towards the technology, train ourselves to master the technology, and along the way unknowingly build a process.  In many organizations, this solution has been the norm for application code, but not SQL Server objects.

The reality is SQL Server code deployments can be challenging because they are typically a component of a larger deployment.  Application code and the coordination among numerous groups makes the deployments business critical.  Unfortunately, many companies suffer from numerous issues related to code deployments including:

  • Insufficient Time - Code deployments are time consuming, require coordination among multiple groups and generally not a primary focus.  They are a task that is just expected to be worked into your schedule.
  • Downtime - Code deployments are business critical, generally resulting in planned downtime for a successful deployment and unplanned downtime when problems arise.
  • Perception - Problematic code deployments reflect poorly on the Developers and DBAs, because no one wants the surprise of knowing your applications are unavailable.
  • Inconsistency - Code deployments need to be a consistent process as code is created as a build then moved to QA, UAT and ultimately Production.
  • Numerous Iterations - It is rare that the first iteration of all code will easily move from Development to Test and to Production.  The norm is multiple iterations of code will need to be written, tested, consolidated and deployed.
  • No Rollback Plan - Rollback code is generally tedious to build, but your best option to return to a operational state.
  • No History - Historical records of code deployments are important to understand application\SQL Server changes as the business needs evolve.

These types of challenges are common at many organizations.  To address these needs, I would like to introduce xSQL makers of numerous tools to simplify the code deployment process.  xSQL offers a number of software bundles to help address these needs and more.  With the xSQL Silver Software bundle, there goal is make code deployments as simple in the shortest amount of time so Developers and DBAs can do what they enjoy most rather than tedious work at a very affordable price.  They even offer two of their tools completely for free for SQL Server Express Edition.  Let's check out what xSQL has to offer.

 

Identify and Correct SQL Server Code Changes

Code deployments can be time consuming with numerous dependencies, countless changes and multiple versions of code for different releases overlapping one another.  xSQL has recognized these challenges and has built a tool that can help in the following ways:

  • Comprehensive - 50+ database objects including tables, indexes, views, stored procedures, functions and a lot more.  See Figure 3.
  • Reusability - Session tabs to reuse syncing configurations to save you time to sync code from Development to QA and then to Production.
  • Snapshots - Point in time versions of your database code for comparison, history and more.
  • Rollback - Create a snapshot on your target database prior to the code deployment, then if a rollback is necessary, the code just compare the previous snapshot with the target database, generate a script of the differences and seamlessly rollback.
  • Flexibility - 100's of options for the database objects, comparison options, scripting options and execution options to meet your needs.
  • Versions - Supports SQL Server 2014 to 2005 with version specific code regardless of the SQL Server edition.
    • xSQL Schema Compare is free for SQL Server Express Edition - Learn more.
  • History - Complete history of code changes to understand how your database objects have changed over time.
  • Command Line Interface - Full command line support to schedule snapshots and deployments.

With this information, let's check out the xSQL Schema Compare for SQL Server tool.

 

The main interface provides easy access to all of the databases supported on the left as well as the snapshots taken to compare databases, create snapshots, etc.  On the right, you see a history of your recent code deployments.

xSQL Schema Compare for SQL Server

Figure 1 - xSQL Schema Compare for SQL Server

 

When you click the 'Compare Databases' icon in the top navigation, the first option is to select the SQL Server instances and databases you want to compare as shown in Figure 2 below.

Select SQL Server Instances and Databases to Compare

Figure 2 - Select SQL Server Instances and Databases to Compare

 

As you work through the comparison process, you are completely in control to select the database objects as well as the comparison, scripting and execution options.

Comparison Options in xSQL Schema Compare

Figure 3 - Comparison Options in xSQL Schema Compare

 

Once the options are finalized, the database code comparison is very fast.  The 'Status' column is key to determine on a per object basis if the objects are in sync, if there are differences with one of the databases or both.  As you scroll through the objects, you can see the detailed code differences at the bottom of the interface.  Once your analysis is complete and you have determined which objects need to be scripted, just click on the 'Generate the script for...' link in the right pane.

SQL Server Database Code Comparison Results

Figure 4 - SQL Server Database Code Comparison Results

 

Below is an example of the script generated after clicking on the 'Generate the script for...' link in the right pane which can be saved or executed in real time.

SQL Server Database Code Script Generation

Figure 5 - SQL Server Database Code Script Generation

 

Based on the script executed, below is a sample log outlining all the code changes at an object level.

SQL Server Database Script Log

Figure 6 - SQL Server Database Script Log

 

In need of a complete SQL Server code comparison tool?  Click here to learn more about xSQL Schema Compare for SQL Server.

 

 

Analyze and Synchronize SQL Server Data

Keeping your data in sync is just as important as your code for deployments, reporting, ad-hoc business needs and more.  xSQL recognized these needs and built a data comparison tool similar to the code comparison tool just discussed.  With xSQL Data Compare for SQL Server you have the ability to compare data sets from two databases, analyze the results and update your data based on your needs.  xSQL Data Compare for SQL Server helps Developers and DBAs with the following:

  • Reusability - Ability to compare predefined data sets for your environment in a few clicks to move data between Development, Test and Production.
  • Configurable - 20+ comparison and scripting options to meet your unique needs.
  • Flexibility - Map tables between databases with differing table names including prefixes and suffixes.
  • Primary Key - Compare data is based on a predefined Primary Key or a key you define on the tables.
  • Large Tables - Tame a large table by comparing only the data you need with a WHERE clause.
  • High Performance - Serialize large data sets to disk for fluid data access and time savings.
  • Tables and Views - Compare data sets for both tables and views which can be helpful for both application and reporting needs.
  • Sync Multiple Data Sets - Ability to sync data with a centralized database or push data from a centralized database all without SQL Server Replication.
  • Historical Records - Available record of all data changes generated and deployed.
  • Versions - Supports SQL Server 2014 to 2005 with version specific code regardless of the SQL Server edition.
    • xSQL Data Compare is free for SQL Server Express Edition - Learn more.

 

Similar to the code comparison product, all of the registered databases, configurations, etc. are in the left pane with the comparison history on the right for research and re-use.  This is your launching pad to compare data among two databases by using the 'Compare Database' icon in the top menu.

xSQL Data Compare for SQL Server

Figure 7 - xSQL Data Compare for SQL Server

 

There are 20+ options available as you compare your data related to the types of tables, synchronization options, different collations, etc.

Data Compare Options

Figure 8 - Data Compare Options

 

While exploring the details of the data comparison, you have the ability to map columns of differing names (i.e. prefixes and suffixes), data types, collations, etc.  All the data comparisons are based on a unique key, so you have the ability to map tables if a Primary Key has not been defined.  A WHERE clause can also be defined to look at data from a particular time period in a table to reduce the time needed for the comparison.  As a final option you can exclude columns, if they are irrelevant for your needs to once again save time.

Data Mapping Properties

Figure 9 - Data Mapping Properties

 

Once all the configurations have been set, the data comparison is quick even for very large data sets because the data is serialized to disk.  This serialization keeps resource consumption in check even for the largest SQL Server databases and makes the application very responsive.  Based on the results, you can analyze the data then click on the 'Generate the script for' link in the right pane to generate applicable script.

Data Comparison Results

Figure 10 - Data Comparison Results

 

Here is an example script to sync the data in your environment.

Data Comparison Script Generation

Figure 11 - Data Comparison Script Generation

 

Need to get your data in sync as part of your deployments?  Click here to learn more about xSQL Data Compare for SQL Server.

 

 

Deploy SQL Server Code and Data Changes

Once scripts have been built to synchronize your SQL Server code and data, the next logical step is to deploy the code.  There are a few common code deployment scenarios and xSQL has options to meet two of the most common needs.  The first option is with the xSQL Script Executor which can help with code deployments in your enterprise.  The second option is intended for code deployments external to your enterprise perhaps for software development companies with customers at remote locations.  This is accomplished with xSQL Builder which is a simple wizard-based interface where a single package is built, distributed and implemented by the customer with a single click at a remote office.  Let's learn about these products and checkout the value:

  • xSQL Script Executor
    • Deployments in your enterprise
    • Folder-based source code for script organization
    • Conditional logic for script deployment
      • Deploy to all or specific SQL Servers
      • Deploy to all or specific databases
    • Logical ordering of scripts
    • Modify scripts as needed directly in the tool
    • Review script output for each deployment
    • Intuitive interface to manage and deploy code
  • xSQL Builder
    • Intended for software development companies
    • Deployments to remote offices
    • Build snapshot of master database with code and data changes then ship the executable file to customers
    • Compare embedded master schema and update customer schema as applicable
    • Ability to ship new databases with an *.bak file or T-SQL scripts
    • Pre and Post Script options for specific clients
    • Reporting on status and completion

 

Enterprise SQL Server Code and Data Deployments

With the xSQL Script Executor, simply build one or more folders to manage your code.  Once completed you can build a package with conditional logic to execute the files to one or many SQL Server instances including one or many databases on each instance.  This can easily be accomplished by dragging and dropping the folders to the applicable destinations.  Precedence constraints can be setup to prevent an issue in one script from executing subsequent scripts.  At the conclusion of the package running a final report can be reviewed to determine the status.

xSQL Script Executor Package Configuration

Figure 12 - xSQL Script Executor Package Configuration

 

Need to deploy your code internal to your organization?  Click here to learn more about xSQL Script Executor.

 

 

SQL Server Code and Data Deployments to External Customers

The xSQL Builder is a seven step wizard that was initially built for software development companies to build an executable package that can be distributed to their customers to update the software they have installed.  The wizard includes the following steps:

  1. Build a new or modify an existing package
  2. Connect to the database serving as the template to generate the package
  3. Determine whether or not a new database should be created
  4. Specify pre and/or post scripts should be executed as a portion of the package
  5. Enter the parameters for logging the progress of the package
  6. List the email parameters for your team to be notified with the status of the package execution
  7. Enumerate the package parameters and either build the package or save the settings
xSQL Builder
xSQL Builder
xSQL Builder

 Figure 13 - xSQL Builder

 

Need to push code to your remote customers?  Click here to learn more about xSQL Builder.

 

 

How do I make my SQL Server code deployments simple?

  1. Go check out all the resources available for the xSQL Silver Package including the 4 products we just learned about with prices starting at $249.
  2. Download your FREE 14 day evaluation copy of each product:
  3. Think about all the opportunities for the xSQL tool set to improve your SQL Server code deployments and to save you time.
  4. Communicate to your team and management about how you think the xSQL tools will help your department:
    • Automate and reuse for time savings
    • Minimize planned and unplanned downtime
    • Simple and consistent code deployment process
    • Easily build a rollback plan ahead of time in case of issues
    • Historical record of code and data changes
    • Highly configurable with hundreds of options
    • Flexible toolset for comparison, scripting and deployments
  5. Put the xSQL tools to the test with your toughest deployments, share the results with your team and determine the next steps.

 

Next Steps
  • Code deployments are tedious, time consuming and error prone.  Make SQL Server code deployments simple, repeatable and successful with the toolset from xSQL.
  • Check out the hundreds of customers like Microsoft, Google, IBM, Sunoco, Wells Fargo and more that are benefiting from the xSQL tools.

MSSQLTips.com Product Editorial sponsored by xSQL Software makers of Schema Compare for SQL Server, Data Compare for SQL Server, Script Executor and Builder.



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, May 04, 2015 - 4:25:32 PM - haider Back To Top

How this tool works with version and source control system such as TFS? TFS deploy does allow smooth sql project deployment. Also with SSDT you can build Dacpac and do the deployment from the dacpack. the dacpac includes delta script file that compares sql objects in Target platform and does the necessary deployment.


Thursday, April 30, 2015 - 10:22:27 AM - Joel Back To Top

xSQL's Data Compare tool looks suspiciously like Idera's SQL Compare tool set.


Learn more about SQL Server tools