By default, SQL Compare copes with slight changes by making an educated assumption when matching columns that are similar but not identical. This command-line option does the reverse. Uses a database’s compatibility level instead of the SQL Server version. By default, it works out the correct comparison by reading the version of SQL Server, but individual ‘legacy’ databases may be hosted on a recent version of SQL Server but set at a lower compatibility level.
SQL Compare’s comparisons vary according to the version of SQL Server. Database schema comparison options: a reference Use database compatibility level On top of that level of customization, you can alter the way that SQL Compare command-line, and SCA does its comparisons and generates its scripts every time it runs, by passing to it a collection of options. SQL Compare, however, allows you to save your own preferred default options. SQL Compare will, out of the box, ignore such things as white space, database user properties and database and server names in synonyms. SQL Change Automation (SCA) projects also use these options and you can specify SQL Compare options in calling SQL Chang Automation PowerShell cmdlets.īoth SQL Compare and SCA use default options decided by Redgate. You can also pass the options as parameters to the SQL Compare Command-line. In the SQL Compare UI, you set these options in the project Options menu, or in one case on the dependencies step of the deployment wizard where a subset of the differences are selected for deployment and unselected dependencies were identified. Many of these determine whether certain difference between tables, such as between constraints, nullability or comments, represent a legitimate change that should be deployed to the target. To help it deal with all the types of issues described above, it has, over the years, accrued a large number of options that alter the way that the comparison and scripting is done.
If you want, it will update a database or scripts folder, directly. When it has done it, it will create a script that would alter the schema of the target to be identical to the source. It will take a representation of a database, either a real database, a backup, a Redgate Snapshot, a scripts folder, or a source control reference and compare it with another such representation of a database. It does precisely what its name suggests: it compares SQL databases. A database schema comparison tool like SQL Compare makes light work of the task. If you must sort all of this out manually, it’s a laborious, error-prone and time-consuming task. See Using Filters to Fine-tune Redgate Database Deployments When you need to exclude certain classes, or types, of database objects completely from any schema comparisons and deployments, sometimes there is an option that will do it, but it probably safest to set up filters to do this job.
With some of these features, it is by no means clear-cut that the code is suitable for a development version of a database. There are a host of features in SQL Server, and more are being introduced on every release. Conversely, when those classes of objects sneak into development systems, regardless, and then get changed and included into deployment script for production, it can cause all sorts of problems. For example, you’ll probably want to exclude synonyms, partition schemes and partition functions, and users to name just a few. If you’re generating a script to capture production changes into your source control system, or development database, then there will be parts of the metadata that are strictly outside the responsibility of the development and shouldn’t even be in source control you’ll need to exclude those objects from comparisons. Customizing schema comparisons and deployment scripts It also depends on whether you’re deploying changes from development to production systems, or vice versa. But which database objects should be included in the schema comparison? Are all differences significant? Is it right, for example, to list or deploy differences in comments, indexes, or constraints? How do you deal with issues such as system-named constraints, or differences that arise because the collation setting isn’t the same in the databases being compared? How should you handle differences in encryption settings between two databases? When answering these questions, so much depends on the style, design, or type of database. You are examining schema differences between two copies of a SQL Server database.
He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.ĭespite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.