I am using RedGate SQL Compare 11.6 to compare two SQLServer 2016 databases.
The problem is, the comparison result includes tables that have same columns but in different order. I would like to exclude this. Is there a way to do these?
RedGate's Support forum and help documents say that 'Default behavior is to ignore such differences' But it doesn't seem to work like that.
By default it doesn't compare column order. To validate this, I set up a test this way using SQL Server 2016 & Compare 11.6:
When I ran compare against the two tables, the output was here:
If you note at the top, it says there are 24 identical objects. These two tables count as identical. However, compare does note that the column order is different. There's an option you can set 'Force Column Order' that changes this behavior. When that's enabled you would see this from the comparison:
It's possible that's what you're seeing. Check the options on the Compare project.
Today during the Visual Studio launch event, we announced that we’ve partnered with Redgate to include Redgate Data Tools in Visual Studio 2017. Redgate Data Tools includes three components that extend DevOps practices to SQL Server and Azure SQL databases and increase your productivity while doing database development.
Here’s a brief overview of each of them:
You can install Redgate Data Tools using the Visual Studio Installer through the Data storage and processing workload or through the Individual components tab.
In this post, I’ll highlight how each of the Redgate Data Tools can improve your SQL database development.
Managing your database changes and incorporating them into your DevOps pipeline has always been difficult. Keeping track of what scripts need to be run and managing database states is often painful and error-prone. ReadyRoll Core simplifies this process by turning changes to your database schema into database migration scripts. These migration scripts can then be checked into source control and deployed to your other databases.
When you’re working in a ReadyRoll project and make changes to your development database using Visual Studio, SQL Server Management Studio, or another tool of choice, ReadyRoll Core will list the changes you’ve made and show you the differences.
Then, you can tell ReadyRoll Core to create a migration script based off these changes and add the new migration script to your project. You can modify the generated migration script yourself if needed.
Once you’re done making changes, you can check these migration scripts into source control alongside any other updates you made to your application and share your work with your team.
Redgate also offers a ReadyRoll Visual Studio Team Services plugin that includes VSTS build and release tasks to safely deploy these migration scripts to other databases. This way, you can keep your database deployments consistent across multiple instances. You can set up VSTS to automate these database deployments to shift your database development further left and learn about potential problems sooner.
Check out Redgate technical lead David Simner’s video on Channel9 for an overview of how you can use ReadyRoll Core for your own database development.
SQL Prompt Core is a suite of SQL code editing productivity enhancements that help you write SQL code efficiently. Most importantly, SQL Prompt Core offers advanced code completion that automatically suggests SQL commands, column names, and more as you type.
SQL Prompt Core is more than autocomplete though – it provides a myriad of other quality of life improvements that come together to simplify writing SQL code. Here are two of my favorite examples:
SQL Prompt will then replace the * with all available columns in that table.
If you don’t want all columns, you can highlight the * instead and start typing to select the subset of columns that you need.
Finding SQL objects can be tricky and time-consuming when dealing with complex databases or when you’re unfamiliar with a database. SQL Search saves you time by quickly finding fragments of SQL in tables, views, stored procedures, and more.
If you’re a database developer, you may have run into scenarios where you need to change a column and all stored procedures that reference that column. SQL Search makes this a cinch. Simply search for the column name and SQL Search will display all references to it. Double clicking an occurrence navigates you to it in the SQL Server Object Explorer, where you can make the appropriate changes.
We’re excited to include Redgate Data Tools in Visual Studio 2017 to make your database development easier and more efficient. Download Visual Studio 2017 to try them out, and feel free to share your feedback with Redgate. Check out Redgate’s SQL Toolbelt as well if you’re interested in their other offering.
Jeff Gao, Program Manager, Visual Studio Platform Jeff is a Program Manager on the Visual Studio Platform team focused on improving the product acquisition experience. |
You've found a database that is not in source control. What do you do? Phil Factor shows how to use SQL Compare to generate all the missing object scripts, in Git, and then keep them up-to-date automatically, in response to any further database changes, during development.
This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.
Despite 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.
He is a regular contributor to Simple Talk and SQLServerCentral.
You have tripped over a database, somewhere in the IT department, that is either not in source control, or not completely in source control. What do you do?
The database may be in the middle of development, but it may even be in production. What I mean by ‘not completely‘ is that you cannot easily trace the creation and alteration of individual objects, such as procedures or functions, because, if anything, maybe only the full build script has been put into source control.
There is nothing particularly wrong with using build scripts that incorporate several database objects: schema-based development, for example, is quite common. I’m a bit ‘beard and sandals’ about building a database. I don’t often work away at individual database objects such as tables or procedures. When I need to develop a tangle of inter-related tables, for example, I’ll use all the help I can get, including diagramming tools and design tools. I’m quite happy to develop via migration scripts. I’ll also do schema-based team development on a shared development server. I know, I know, you may need a steady nerve to read this.
Whichever way one develops a database, it is wise to maintain source control at the object level because, if you are developing code for a team within an organisation, it is easily auditable: there is a record of who did what and when. It allows you the maximum flexibility in the way you work. It helps even if you are the only developer in a start-up.
This article will explain how, among this apparent chaos of development methods, you can use SQL Compare to maintain object-level source control.
For this demonstration, I’m using Github. It should be a paid account because otherwise, when you push to origin, you can only use a public repo. First, create a new local repository, using the Github client.
This will create the AdventureWorks directory, the local repository, as a subdirectory of the path that we specified. In it, we put the bare minimum of the documents we need, including filling in the details of what we are doing in the markdown file called README.md. This is a text file describing the repository that you add to the repo. It uses the markdown notation to signify such typographic things as bold, italic, headings and bullets.
In this local repository, we’ll create a directory called SQL, within which we’ll generate a set of object-level build scripts, in a series of sub-directories, one for each type of object. This collection of scripts, combined, will build the database. It is best to keep the build scripts in a separate directory from all the other clutter associated with your database, such as migration scripts, SSIS projects, unit tests, PowerShell scripts, config scripts and agent scripts.
Open SQL Compare and create a new project. The Source will be the rogue database we found, and the Target will be a Scripts folder, namely the SQL directory in our local repo. The initial comparison will convince SQL Compare that the database has all the objects, and the scripts folder has none. When you’ve entered everything satisfactorily, you can then hit the ‘Compare Now‘ button. You can see Feodor Georgiev’s article for a few more details about this process.
If all goes well, you will shortly see a screen displaying the differences between the source and target. When SQL Compare does its comparison work, it compares the source to the target and generates a list of objects that exist only in the source or exist only in the target or exist in both but have differences. In this case, the target was a blank directory, so we see a long list of all the objects in the database, on the left, and nothing on the right.
I want to ‘deploy’ all the objects to our local repo, so I just clicked on the checkbox that selects every object and then hit ‘Deploy‘. For every object that is new, or changed, a new file is created with the source in it. If it was not in the source database, the equivalent file is deleted.
The next window elicits from you the way you wish the process to end.
We don’t want a single deployment script, which in this case would be an entire build script. We can generate this easily later. If you are asked if you wish to back up the target, you don’t need that either in this case. It is comforting to re-compare the directory with the database after all the files are written. I can’t remember ever having a problem, but it is good to check unless the database DDL scripts are so big that it would take too much time.
Normally, things go well. In this case, we hit a warning familiar with anyone who has tried to use AdventureWorks as a demo. SQL Compare generates a synchronization script, which it wraps within a transaction, so that it can roll back all changes should something go wrong. However, SQL Server will not allow creation of a full-index within a transaction, so SQL Compare will deploy the full-text index changes after the transaction completes. However, if the initial transaction includes changes to objects that reference that index then the deployment may fail.
You can ignore the warning at this stage, because we’re deploying to a directory, but you’ll need to deal with the issue when you come to use the contents of source control to deploy the database. Firstly, you’ll need to install the Full-Text Search on the target, by running SQL Server Setup again to add it service into the target SQL Server for the deployment. That done, it will deploy properly if you activate the ‘don’t use transactions in deployment scripts’ SQL Compare project option. Alternatively, you might consider Feodor’s technique of excluding from the initial deployment any objects that reference the index, and then deploying them in a second step.
Hit ‘Update Scripts’ and SQL Compare will synchronize the source and target. In this case, we have started with an empty directory, so we’ll end up with script files being created for every database object. If we look at our source code directory to confirm this, we can see that we now have directories for all the object-level source.
We can examine a directory, the Tables one in this case.
Yes, everything is there so we’re ready to commit the changes to source control. First though, we must save the SQL Compare project under a name. If you don’t then you would need to set SQL Compare up manually every time you run the comparison. With the project file, you can repeat the comparison by running command-line SQL Compare, specifying the project in the command line.
That done, we can go back to GitHub and do an initial commit.
So, what happens if we make a change to the database? Let’s add a view and find out.
And now we refresh the project in SQL Compare.
Now, having run through the process, we get the view flagged up as a change in SQL Compare
We go ahead and hit the Deploy button, and a new file has appeared in the local source control directory. Note that the time has changed, but the other files haven’t changed.
Github client has detected the change in the repository and invites you to commit the change.
We’ll commit that.
So far, we’ve established our Github Repository. We’ve shown that to save changes into the local repository, all we need to do is to refresh the SQL Server project. We can go ahead and push to origin at github.
We’ve been a bit cavalier in our description of the synchronization process. There can be a wide range of requirements in the way you would want the source code. You might, for example, want to ignore changes that have no bearing on the actual functioning of the code. You might need comparisons to be case-sensitive. You might want to exclude details that concern the way that the database is installed, or the code for access control. To do this, there are a lot of knobs to twiddle to alter the way that the project runs. The reason we’ve been so relaxed is that we will be using SQL Compare to do all our builds and deployments, so we can be confident that these settings will work. However, the settings are all there for a reason, either for comparison or for scripting builds or deployments. For example, SQL Compare can be set to ignore certain types of difference in objects, such as collation: The ‘Use case sensitive object definition‘ option is essential if your database is on a SQL Server with case-sensitive sort order.
If your script isn’t right for your requirements, then the options are likely to allow you to correct it. The options are saved with your project, and then can be used either in the GUI or the command line. They are modified on the Project Configuration dialog box.
With the project honed to our requirements and saved, we now have the database in source control.
You will have noticed a flaw in what we have done. Everything has been saved under a single developer’s name. Had we done this in reality we’d have emphasized this by using an ‘admin’ user.
As this article is about the steps we need to take to get the database into source control, we will leave the nuts and bolts of managing a Github project, but it is certainly possible for individual programmers to save their work under their own ID in Github or any other source control system, even when a process such as SQL Compare is doing the actual abstraction of the object-level source onto a network share.
Having saved the project file that you used for the successful generation of the source, and for the subsequent refresh that found the new file, you can rerun it from the command line to pick up all subsequent changes. Obviously, this wouldn’t apply to a production server, but you can easily alter the project to compare with a backup.
To reuse the saved project, let’s call it AdventureWorks.scp, from the command line you would simply use this syntax, providing the path to the project file:
sqlcompare /project:”C:SQLCompareProjectsAdventureWorks.scp”
There are many advantages to using a project, and not just to do with the database authorization, paths and so on. They are also particularly useful if you are only tracking a subset of objects, because all objects that were selected for comparison when you saved the project are automatically included. The only problem is with your choice of non-default options. The defaults are used instead for some reason, so you need to use the /options switch to specify any additional options you want to use. Also, you can’t use the /include and /exclude switches with /project.
Once your command-line version of the project is working well, then it can be scheduled, either on the windows scheduler on the workstation or on the development SQL Server.
In supporting development work with databases, it is important that your tools can support the way that teams work rather than impose a way of working on the team. This is because there are so many different requirements. The database could be part of a purchased application, doing such organisational work as accounting, purchasing or payroll. The alterations to a database could be restricted to maintenance of indexes or constraints. The database alterations may be under a regime that is determined by compliance. It could be an agile environment doing continuous delivery, or a start-up racing to get an application up and running whilst it is still funded. SQL Compare is an oddly-named tool when one considers how useful it is in supporting a range of database development methodologies. It doesn’t hector you into a single approach but just gets on and supports the way you need to work.
This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.
Despite 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.
He is a regular contributor to Simple Talk and SQLServerCentral.
Share this post.
Article
Tony Davis explains how to resolve simple merge conflicts, such as conflicting changes to the same stored procedure, using SQL Source Control and a merge tool such as Beyond Compare.
Article
Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three.
Event
SEACON is THE Enterprise Agility conference that brings business and technology together, and Redgate are pleased to be taking part as one of the sponsors. Following the sold out conference in 2017, the 2018 edition will again host FinTech practitioners and Thought Leaders in Enterprise Transformation, Entrepreneurial Leadership, Agile , DevOps, Cloud and Fintech.
Article
Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such as renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).
University
SQL Toolbelt includes all of the components that enable Database DevOps. This step-by-step guide takes you through the process right from being able to analyze the impact of database changes through to making those changes, source controlling them, deploying them out to your target environments, and finally monitoring and documenting those environments.
Here you will see an overview of each tool and how it can benefit your organization, but be sure to check in the module description whether there is a full course available for the tool you are learning.
Forums
Forum for users of SQL Compare schema synchronization utility
Possible Duplicate:
Free Tool to compare Sql Server tables
I would like to compare two SQL Server databases including schema (table structure) and data in tables too. What is best tool to do this?
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
I am using Red-Gate's software: http://www.red-gate.com
I use schema and data comparison functionality built into the latest version Microsoft Visual Studio 2015 Community Edition (Free) or Professional / Premium / Ultimate edition. Works like a charm!
Red-Gate's SQL data comparison tool is my second alternative:
(source: spaanjaars.com)
I like Open DBDiff.Updated Open DBDiff Link
It's not the most complete tool, but it works great and it's free. And it's very easy to use.
SQL Admin Studio from http://www.simego.com/Products/SQL-Admin-Studio is now free, lets you manage your SQL Database, SQL Compare and Synchronise, Data Compare and Synchronise and much more. Also supports SQL Azure and some MySQL Support too.
[UPDATE: Yes I am the Author of the above program, as it's now Free I just wanted to Share it with the community]
I'm partial to AdeptSQL. It's clean and intuitive and it DOESN'T have the one feature that scares the hell out of me on a lot of similar programs. One giant button that it you push it will automatically synchronize EVERYTHING without so much as a by-your-leave. If you want to sync the changes you have to do it yourself and I like that.
There is one tool with source code available at http://www.codeproject.com/Articles/205011/SQL-Server-Database-Comparison-Tool
That should give flexibility as code is available.
dbghost is the best i have used to date. one of the best features i have seen is that it will generate SQL code to go between versions of a database based on the SQL you keep in source control, as well as a database. simple and easy to use.
I've used SQL Delta before (http://www.sqldelta.com/), it's really good. Not free however, not sure how prices compare to Red-Gates
Try DBComparer, it's free and fast:
Database Workbench can made it too
Cross database development
Use the Schema Compare and Migration Tools to compare testing and deployed databases, migrate existing databases to different database systems.
you can also made it with database Comparer Free xbox 360 full game downloads.
I use it for Firebird and it works well.
Try dbForge Data Compare for SQL Server. It can compare and sync any databases, even very large ones. Quick, easy, always delivers a correct result.Try it on your database and comment upon the product.
We can recommend you a reliable SQL comparison tool that offer 3 time’s faster comparison and synchronization of table data in your SQL Server databases. It's dbForge Data Compare for SQL Server and dbForge Schema Compare for SQL Server
Main advantages:
Plus free 30-day trial and risk-free purchase with 30-day money back guarantee.
We are using an inhouse developed solution that is basicly a procedure with arguments of what you want included in the comparision (SP's, Full SP code, table structure, defaults, indices, triggers. etc)
Depending on your needs and budget, it might be a good way to go for you as well.
It is quite easily developed as well, then we just redirect output of procedure to textfiles and do text comparisions between the files.
One good thing about it is that its possible to save the output in source control.
/B
I've used Red Gate's tools and they are superb.However, if you can't spend any money you could try Open DBDiff to compare schemas.
I would definitely go with AdeptSQL if you're using MSSQL. It's the least good looking but the most talented db compare tool amongst the ones I've tried. It can compare both the structure and the data. It tells you which tables exist on one db but does not exist on the other, compares the structure and data of the common ones and it can produce the script to synchronize the two. It's not free but has a 30 day trial (as far as I can remember)
I tried OpenDiff Tool . Great tool that is free and easy to use .
SQL Monitor’s Job duration unusual alert is a powerful tool for keeping track of most jobs in your SQL Server environment. It works by comparing the running time of any job instance against the median for that job, and identifying when a variation in the job duration could hint at a performance problem. However, if your environment contains big jobs whose durations vary by several orders of magnitudes, it can be tricky to specify what kind of variation is expected and what would be unusual. For these cases, it is possible to go beyond the functionality of the built-in alert with a custom metric.
2013-04-05
2,857 reads