Sql Data Compare Script Servers
I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me? The result of the comparison between two databases or database projects is analyzed and allows you to apply the change from source database to target, or simply generate T-SQL scripts you can view, modify and run at your control.
Hi everyone,
Can anyone tell me about a tool that would make it easy to script out, version, and diff the configuration of SQL Agent - like Red Gate SQL Source Control for Agent objects?
In my organization we version our database code in an SVN repository. We rely on Red Gate's SQL Compare and SQL Source Control to script out database changes to our version control system.
Not all of our database code is in a database, however. Some of our servers run lots of agent jobs to perform back-end processing tasks. The Red Gate tools are ignorant of objects managed by SQL Server Agent, such as jobs and operators.
Currently we manage our Agent settings manually. We try at least to script out every job we create and re-script the job when we make a change. We don't always remember to do this.
To attempt to automate the process, I crafted the following PowerShell to script out all the jobs on the local server to the working directory:
Add-Type -AssemblyName 'Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91';
$server = New-Object Microsoft.SqlServer.Management.Smo.Server;
$server.JobServer.Jobs % {$_.Script() > '$_.sql'};
This is proof-of-concept only; it would need a lot of work to become robust.
Should I consider putting effort into developing my own tools for this, or does such a product already exist?
By Keith Schreiner
I have read and been told many times that it is a “best practice” to version control my database. To do so, I’ve tried past tools from Microsoft, RedGate, and others, but none have really caught on for me. I’ve also disliked the “messy” process of having to manage multiple SQL scripts when upgrading a production database. But Microsoft’s latest (free) tool, SQL Server Data Tools (SSDT), has restored my faith that versioning my database should not be a dreaded chore.
Define
SSDT adds a lot of functionality to Visual Studio for working with SQL Server databases. The main things it adds are a “SQL Server Database Project” and something called a DACPAC (Data-Tier Application Package). A DACPAC is a single deployment file that contains your entire database schema and some related SQL files (like look-up data), basically, everything to deploy a new version of your database in one file. It is similar to a BACPAK, which is a DACPAC plus all of the data in every table (like a standard database backup). But before we talk more about DACPAC’s let’s discuss what SSDT adds with its SQL Server Database Project.
Develop
In Visual Studio, after creating a new SQL Server Database Project, you have an option to import a database, which nicely creates SQL scripts of all your database objects (tables, views, stored procedures, and more). The next step that I do is to create a “Data” folder and add a post-deployment SQL script to populate the look-up data and optionally create some test data.
In a database project you can only have one post-deployment and one pre-deployment file, so it is a best-practice to use the SQLCMD syntax to include other files in your main file to help with organization and maintenance. As you are creating these SQL scripts, or editing the SQL for the tables or stored procedures, you will start to notice all of the other things SSDT adds to Visual Studio: SQL IntelliSense, SQL code navigation, a SQL Server Object Explorer, debugging of SP’s, unit tests for SP’s, a visual Table Designer, a Schema Compare tool, a Data Compare tool, and more. All of these features make database development and maintenance feel natural in Visual Studio.
Deploy
After making your database changes, the next step is to deploy them, and SSDT provides several ways. The first way is “Publish”, which works great if you have a direct connection to the database from your Visual Studio machine (like to your local or “Dev” database). Right-click the database project and select “Publish…” which opens a dialog to save a profile. Once a profile is saved as part of your project, you can just double-click it to directly publish your database project to the database or to just generate a script of the changes that it would run.
The second way is to use a DACPAC, which we talked about above, when developers do not have access to the target database (like for Production). To create a DACPAC, just right-click the database project and select “Snapshot Project”, which then creates a DACPAC in your project’s “Snapshots” folder. Then to deploy this DACPAC you have several options. If you have a DBA that always applies database updates, you can give him or her the DACPAC to apply using SSMS (SQL Server Management Studio), which has a built-in “Upgrade Data-tier Application” task; it uses a wizard to help apply the changes (and allows all changes to be reviewed for problems, like possible data loss, at every step).
Another DACPAC deployment option is to call “SqlPackage.exe” (with command line parameters) or to write some C# code (from the Microsoft.SqlServer.Dac namespace) that deploys the DACPAC itself. This makes deployment automatic, almost to a point where you don’t have to worry about it.Sql Script Data From Table
Real-life sample
On the first project I used SSDT (a new MVC web site), I encountered three issues with SSDT. Here is how I solved them and greatly simplified database development. First, I only wanted to auto-deploy the DACPAC on the website’s Application_Start when the database had actually changed. So I added a piece of logic to check for a new version number in the latest DACPAC before applying it. The version number of the DACPAC is set in the Database Project properties -> Project Settings -> Properties dialog, and the current database version is stored in the system table, msdb.dbo.sysdac_instances_internal. Having this version number not only made startup faster, it made others aware of a “database version”.
The second issue was how to set up the database project to include “test data” for the Dev database, but exclude it for Production. This was accomplished by creating a second database project (DatabaseAndTestData), which had a “database reference” to the first project. In this second project, its post-deployment script includes the first project’s post-deployment script and then all of the test data. I used the first project (with no test data) to create the DACPAC for Production, and then just published the second project (with test data) directly to Dev.Lastly, some developers complained that after updating the schema or look-up data, they still have to do several boring and repetitive tasks to get the DACPAC ready to be deployed. To solve this, I wrote a simple application (UpdateDatabaseVersion.exe) that can be called from the “Solution Explorer” of the database project using the Visual Studio extension, “VSCommands”. It increases the database version number, creates the DACPAC, and then puts it in the correct folder for deployment. By implementing these SSDT processes, it streamlined my team’s database related tasks, allowing us to spend more time on other tasks.
Conclusion
In the past, making a change to a database felt like a long, complicated process of making a schema change (to hopefully the correct database version), manually creating a change script, reminding the deployment person about the database change, and crossing my fingers that everything went correctly. With SSDT all of this uncertainty has disappeared. SSDT is a great tool that makes it easy to create, deploy, and version your SQL Server database updates. To learn more, download the sample code, which demonstrates how to auto-deploy and version a SQL Server database.
Notes:
- SSDT Website: http://msdn.microsoft.com/en-us/data/tools
- SSDT Installed In: C:Program Files (x86)Microsoft SQL Server110DACbin
- SSDT Version Used: July 2013 with Visual Studio 2012
- VSCommands Extension: http://vscommands.squaredinfinity.com/
Keith Schreiner is a Software Architect with Geneca, a custom software development company based in Chicago. He has over 15 years of experience working in the software development industry.