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.
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.
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.
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.
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.
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.
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.
Based on the script executed, below is a sample log outlining all the code changes at an object level.
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.
There are 20+ options available as you compare your data related to the types of tables, synchronization options, different collations, etc.
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.
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.
Here is an example script to sync the data in your environment.
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.
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:- Build a new or modify an existing package
- Connect to the database serving as the template to generate the package
- Determine whether or not a new database should be created
- Specify pre and/or post scripts should be executed as a portion of the package
- Enter the parameters for logging the progress of the package
- List the email parameters for your team to be notified with the status of the package execution
- Enumerate the package parameters and either build the package or save the settings
source collected from mssqltips.com
No comments :
Post a Comment