Toad World Blog

Server Compare sets itself apart [Toad for SQL Server]

Nov 12, 2019 9:39:00 AM by Jeff Surretsky

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:

  • Configuration
    • Server and Version Information
    • Login and Language Configurations
    • Configuration and Run Values
    • Assemblies
  • Databases
    • Database Instance between two servers
  • Data Transformation Services
    • SQL Server Metadata Service
    • Local DTS Packages
  • Security
    • Login
    • Server Roles
    • Credentials
  • Server Objects
    • Endpoints
    • Linked and Remote Servers
    • Triggers
  • Management
    • Mail Accounts
    • Profiles
    • Settings
  • SQL Server Agent
    • General, job system, and alert properties
    • User-defined error messages
    • User-defined operator, job, and alert categories
    • Proxies
    • Operators
    • Schedules
    • Jobs
    • Alerts
  • 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:

 

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.

ServerCompareOptions

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:

Invoke1

 

Or from the Tools menu pulldown:

 

Invoke2

 

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.

CreateSnap1

 

Here you can specify which server you want to take a snapshot on.

 

CreateSnap1a

 

And where you want to store the snapshot file.

 

CreateSnap2

 

As you can see, you have the option of comparing two server connections or use snapshots.

 

NewCompareServer1

 

I’ll choose a Server Connection and Snapshot.

 

NewCompareServer2

 

After clicking on Compare, we get our results.

 

Results1

 

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.

 

Results2DB-1

 

In addition, the quest login’s Enforce Password Policy was changed from true to false.

 

Results3Security

 

After viewing these differences, you have the option to synchronize all the differences or just specific ones by invoking the Synchronization Wizard.

 

Sync1

 

After clicking on Next, choose the sync direction.

 

Sync2

 

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.

 

Sync3

 

You can also see a Synchronization History with options of creating sync and rollback scripts.

 

SyncHist

 

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.

Useful resources:

Product page: Toad for SQL Server

Tech brief: Solving the SQL Tuning Problem: Secrets of Quest SQL Optimizer

Jeff: Quest SQL Optimizer tech brief (above) is listed on the Toad for SQL Server page, do you think that is confusing? Should we remove it?

Case study: County enhances critical services for citizens with Toad®

Related blog posts:

Using Toad for SQL Server with SQL Database on Azure

Toad for SQL Server? Who Needs It?

Using Toad for SQL Server with AWS RDS

 

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.

 

Got questions?

If you have any questions, please post questions to the Toad for SQL Server forum on Toad World.

Tags: Toad for SQL Server SQL Server DevOps Development Agile Automation

Jeff Surretsky

Written by Jeff Surretsky

Jeffrey Surretsky has been working at Quest since 2000 as a Sales Engineer focusing on a wide variety of solutions including Foglight, SharePlex and the TOAD Family of Products. Before working at Quest, he was a DBA for various organizations spanning a diverse range of industries. With over 30 years of experience in Information Technology, Jeffrey has a Bachelor’s Degree in Computer Science and an MBA in Management of Information Systems.