Are you managing a large SQL Server environment and looking to migrate changes from Development through test and production, deploy and/or consolidating SQL Servers, or more easily manage your standby servers?
Toad for SQL Server is a SQL management tool that can help you accomplish these and more through its Server Compare feature; a feature you’ll find more powerful and complete than any other in the industry.
The Toad for SQL Server management tool, Server Compare feature allows you to easily compare and synchronize properties between SQL servers.
By using the Server Compare within Toad for SQL Server, you can compare Configuration, Databases, DTS, Security, Server Objects, Management, SQL Server Agent and Notification Services.
Toad for SQL Server management tool has other features like Schema and Data Compare (see below) which allow you to compare and synchronize your schema structures and application data.
In this blog, I’ll be focusing specifically on the Server Compare feature.
The Server Compare feature
When using the Server Compare feature, you can expect to see the following details:
- Server and Version Information
- Login and Language Configurations
- Configuration and Run Values
- Database Instance between two servers
- Data Transformation Services
- SQL Server Metadata Service
- Local DTS Packages
- Server Roles
- Server Objects
- Linked and Remote Servers
- Mail Accounts
- SQL Server Agent
- General, job system, and alert properties
- User-defined error messages
- User-defined operator, job, and alert categories
- Notification Services
Servers + snapshots
You’re not limited to comparing two servers; you can also compare a Server Snapshot!
What is a Server Snapshot? It’s an off-line binary file that stores server configurations and objects associated with the server. Snapshot file names include Date and Time so you can create snapshots at regular intervals giving you a history of the server. Read all about Server Snapshot in my previous blog, “What is a Toad Snapshot and why should I use it?”
This adds to your flexibility of what you can compare and synchronize. Not only can you compare two servers, you can also compare two snapshots or a server and a snapshot.
It is simple to use and can even be automated.
If you want to know more about how to use the Toad for SQL Server Automation feature, you can look at the following Toad World videos:
- Toad for SQL Server Automation – Part 1
- Toad for SQL Server Automation – Part 2
- Toad for SQL Server – Automate Schema Compare
Now let’s look at an example of how we can use the Server Compare feature.
First, you may want to change the Server Compare options. You can change the default options for Server Compare by clicking on Configure Toad Options (located in the ribbon bar) or selecting Tools > Options from the pulldown menu.
Within the options, you can choose which specific services you want to include when you invoke Compare Server.
Once done, there are a couple of ways of invoking the Compare Server feature.
From the Compare Icon as follows:
Or from the Tools menu pulldown:
As I previously mentioned, you can compare two Server connections, two snapshots or a Server connection and a snapshot. For my example, I’m going to compare a Server connection and a snapshot.
In order to take a snapshot, click on the snapshot icon as follows.
Here you can specify which server you want to take a snapshot on.
And where you want to store the snapshot file.
As you can see, you have the option of comparing two server connections or use snapshots.
I’ll choose a Server Connection and Snapshot.
After clicking on Compare, we get our results.
You can choose to see all results or filter them by what is different, what is only in one Server and not the other and vice versa. Notice that I filtered out the items that are the same.
At a high level, you can see that there is an additional database and that the quest login has different credentials. To get more details, highlight specific items in the Compare Results tree on the left.
It appears that a TestDB database was created with specific options.
In addition, the quest login’s Enforce Password Policy was changed from true to false.
After viewing these differences, you have the option to synchronize all the differences or just specific ones by invoking the Synchronization Wizard.
After clicking on Next, choose the sync direction.
Ultimately, you’ll get a synchronization script that you can be opened in the Toad Editor, run immediately or saved. By saving the script, you have the option of automating it.
You can also see a Synchronization History with options of creating sync and rollback scripts.
I hope you enjoyed this blog and I encourage you to try out the Toad for SQL Server powerful Server Compare feature and you’ll soon see how it sets itself apart.
Try Toad for SQL Server now:
Are you just starting your journey to evaluate if you should purchase the Toad for SQL Server management tool? If you’re not quite ready to buy, we’re so sure you’ll love Toad for SQL Server, that we’re offering a Toad for SQL Server free 30-day trial.
Already in a trial? Buy it now or contact sales.
Not ready for a free trial? Learn more.
Use the links below to learn how you can use the Toad for SQL Server management tool to resolve issues, manage change and promote the highest levels of code quality, performance and scalability.
Product page: Toad for SQL Server
Related blog posts:
Side note: ApexSQL adds to Quest SQL solutions
As an aside, since you’re managing SQL Server, did you know that ApexSQL is now a part of Quest? ApexSQL joined Quest this summer. We’re excited to nod our heads that yes, the ApexSQL portfolio enhances Quest’s already robust set of SQL Server management tools. Make sure to visit Apexsql.
If you have any questions, please post questions to the Toad for SQL Server forum on Toad World.