Automate SQL Server Management Across the Enterprise with Conductor4SQL
We have a large distributed SQL Server environment and a small team to manage it. From a technology perspective, we need to streamline our deployments, get access to real time information across the environment and more. From a business perspective, we need to scale our environment with a limited staff, support auditing requirements and get ahead of the business needs. We face a number of challenges: total number of SQL Servers we support, the business criticality of our SQL Server implementation, the operational reality that nothing is perfect. But our team needs to be nimble and efficient to meet the business needs. Can you give me any insight into resolving any of these issues?
Many of the issues you mentioned are common with nearly every business with an investment in SQL Server based solutions, its just all relative. What one professional considers a large SQL Server environment, someone else may not. The reality is most SQL Server environments have many of the same technical needs:
Deploy and rollback code successfully
Replicate data from a central repository to numerous databases
Insight into the environment for health, issues and information
Report across all databases in real time in a simple manner
Audit for compliance needs as well as internal checks and balances
These same businesses face many of the same challenges:
Managing SQL Server is complex
Numerous competing internal and external priorities
Limited team members and resources
Difficulty hiring qualified and quality professionals
Answering simple questions from the business
Tedious and time consuming tasks for SQL Server Professionals stretched thin
SQL Server instances inaccessible due to network issues
So how do you address these needs and challenges? How are these issues solved today by business and technology professionals? In many cases these items are addressed by single purpose tools or internally developed code to meet very specific needs. At times there are also manual processes performed to pull all of the pieces together.
This is considered "good enough", but what happens as the landscape changes? Often times this leads to frustration from both the business and technology groups. The business has an expectation that a simple question is just that: simple. The reality is the question is terribly complex from a technology perspective. Is there no answer? Do I have to start hiring anyone I can find? Do we need to just continue to find single purpose tools?
To address these needs and challenges, I would like to introduce you to Conductor4SQL from EnterpriseWorx. Conductor4SQL is an enterprise SQL Server Management Platform for automating numerous common needs in SQL Server environments for:
Querying data across all of your SQL Servers
Auditing coding changes
Efficiently replicating data from a centralized server to the remainder of your databases
Reporting on database and business activities seamlessly across your entire SQL Server environment
Conductor4SQL is built on SQL Server and with a firm understanding of SQL Server. This is evident, first and foremost with the need for asynchronous communication supported by Service Broker, scheduling with SQL Server Agent, reporting via Reporting Services and more. As you learn more about the platform, it is clear the intention of the product is to reduce the burden of an overtaxed SQL Server DBA and Development teams by eliminating many common tedious tasks. This has proven to be the case for the last seven years for two of the largest retailers in South Africa that have been benefiting from Conductor4SQL managing more than 1000 SQL Server instances. Let's dive in and show you how they do it.
How can Conductor4SQL Improve our SQL Server Enterprise?
From an architecture perspective, Conductor4SQL is built on a hub and spoke model with Central serving as the hub and Clients as the spokes. This enables Conductor4SQL to easily streamline the code deployments, push data from Central to the Clients, report on data across the enterprise from Central in real time, audit data on the Clients then consolidate the data on Central, etc. As you learn about Conductor4SQL, you will quickly notice the product has a feature rich GUI with a very low learning curve to help overburdened SQL Server Professionals with the following:
Deployments - Streamline code deployments across the enterprise including files
Data Distribution - Push data changes from Central to each of the Clients and vice versa
Distributed Queries - Issue queries from Central against each of Clients with a single result set
Auditing - Record of all planned, unplanned and ad-hoc code changes in your environment
Reporting - Standardized set of reports to minimize SQL Server management with the flexibility to create custom reports
Based on these features, Conductor4SQL also provides business value by:
Time Savings - Reduce level of effort for critical, yet time consuming tasks for SQL Server team members
Real Time Information - Enable the business to get questions answered in real time across the enterprise
Compliance - Built-in auditing included with the code deployments and collection of all code changes at the client
Single Tool Set - Ability for a single integrated product to meet numerous needs
Cost Reduction - Invest in a single tool, ability for a small team to efficiently manage a large environment and more
Here is a first glimpse of Conductor4SQL:
Figure 1 - Conductor4SQL Central Client Dashboard
SQL Server Code Deployments
As your SQL Server environment grows, deploying code becomes exponentially more complex. Pushing code to hundreds or thousands of SQL Servers in a successful manner is no small feat. As a SQL Server Professional, you need the ability to push out code reliably to the environment. This is a critical challenge for the SQL Server team to support the business. With complex code deployments including code and files it is imperative to have a reliable, consistent and trusted product. Further, you need to be assured the code deployed correctly, ensure consistency and validate the results. Conductor4SQL has the flexibility to push code and files out to all your SQL Server instances, a predefined set of SQL Server instances called a Group or servers you select as needed. This flexibility makes code deployments simple as code progresses from Dev to Test to UAT to Production.
In the example below, Conductor4SQL is pushing files out to numerous SQL Server file systems, with full monitoring, status reporting and history. The approach implemented by the product simplifies the file management component which is key for many deployments and provides assurance that the process is successful.
Figure 2 - Conductor4SQL Send Multiple Files
Figure 3 - Conductor4SQL Monitor File Send Process
SQL Server Data Replication to All SQL Server Instances
Data Synchronization is an important feature to ensure data is consistent across the SQL Server environment for lookup tables, product catalogs and centralized reporting. Although code can be deployed to meet these needs, the team at Conductor4SQL recognized these needs and built a bi-directional data synchronization module to meet these needs without having to write a line of code. This point and click Conductor4SQL feature is built upon Service Broker technology to efficiently replicate data changes from Central to the Clients and vice versa. In order to configure the replication, the SQL Server instances and tables needed to replicate are defined, which is shown in the first screen shot below. The second screen shot demonstrates the management interface available at an instance level to manage the data sync process as your business changes.
Figure 4 - Assign Client Data Sync
Figure 5 - Manage Data Sync Tables
Enterprise Wide SQL Server Meta Data Comparison
The ability to compare meta data from one environment to another has been available for many years in the SQL Server community. Conductor4SQL has taken things to the next level with the ability to compare tables, stored procedures, views, functions, etc. from your Central Database to all of your Client Databases. This provides a great deal of value in terms of time savings in a large SQL Server environment in order to have a consolidated view of the code comparison with the ability to click on the object for all of the details as well as corrective code generation. Let's break down each step of the process with Conductor4SQL.
Step 1 - Object Selection for Meta Data Comparison
To compare a group of SQL Server instances, first select the Central Database and corresponding Client Database. Next select the tables you are interested in comparing. Press OK to continue.
Figure 6 - Select Database Tables for Meta Data Comparison
Step 2 - Review Overall Results
Once the analysis is completed on all of the instances, a single consolidated view of the data is available for each SQL Server instance. This data includes:
Server and Instance Name
Central Object Name
Client Object Name
Figure 7 - Conductor4SQL Metadata Compare - Tables
The ability to see all of this data in a single interface is very valuable to quickly understand the issues. The last column, View Differences, is also key to understand the detailed code differences. By clicking on the magnifying glass for a corresponding row, the Code Comparison interface will load as shown below.
Step 3 - Code Comparison
At the most detailed level, the code can be reviewed between the Central Database and corresponding Client Database. This enables you to make a decision on the next steps to sync the code and keep your environment consistent.
Figure 8 - Conductor4SQL Detailed Metadata Comparison
Real Time SQL Server Data Access Across All Instances
The ability to query across a large distributed SQL Server environment from one interface provides a great deal of value in terms of time savings and access to real time data. Here you are able to gain insight into your SQL Server environment for health, issues, etc. and for the business to make better decisions. With Conductor4SQL you have the ability to directly enter code or work with an existing template (see Figure 9) setup by the team to gain insight into the environment. Once the code is run, you can monitor the results then review the results in a single interface which is very beneficial to begin the decision making process. This functionality enables SQL Server and Business Professionals alike to gain new insights in a rapid manner ultimately leading to better decision making.
Figure 9 - Conductor4SQL Real Time Data Access
Figure 10 - Monitor Real Time Data Access Request
SQL Server Code, Configuration and Security Auditing
Auditing is critical on many levels. For legal requirements, auditing is necessary to prove compliance for the business. For internal needs, auditing provides a checks-and-balances mechanism to ensure code is consistent across environments as well as assurance that code is not being modified outside of deployments. This is especially critical for DBAs because in many circumstances, DBAs are in a situation where they have to find a resolution to performance or other systems issues. With a uninterrupted audit being performed they can understand the environment changes as it pertains to the issue to quickly work towards the root cause.
The team at Conductor4SQL understands these needs and provides two sets of auditing. First is from Central with a record of all code changes deployed through the product. Second is an audit that is performed on each of the Clients with the code changes pushed to Central. This two phased approach ensures code, configuration and security changes are captured in a reliable manner.
The reporting interface is very intuitive and straightforward to use. The filtering of data is very simple: just type the keyword you are looking for above the column to see the data you are interested in. In the example below, we are interested in the Object Name "TESTTABLE1" which is entered in the filter row on the top of the grid. Once you filter the desired data, simply click on the row in the main grid to see the corresponding code change in the right pane. It's just that simple and your auditing needs are covered.
Figure 11 - Conductor4SQL Auditing Results and Details
SQL Server Reporting
The team at Conductor4SQL has made reporting a priority and has not overlooked this critical need. Included are more than a dozen well designed reports to help a busy SQL Server DBA team quickly gain insight into the environment. These reports cover configurations, security, database storage and more to address many common SQL Server daily operations needs. As previously mentioned, much of the Conductor4SQL tool set is built upon native SQL Server technology. The Conductor4SQL reporting module is built with SQL Server Reporting Services, so you can add and modify reports based on your needs. Let's take a look at a few examples.
Conductor4SQL Client Reports
Check out the reports shipped with Conductor4SQL in the Report Manager interface:
Figure 12 - Conductor4SQL Client Reports
Job Summary Gantt Report
Knowing when SQL Server Agent Jobs run is easy to determine; but having a visualization to see when jobs run, overlapping and more is easy to see with this report.
Figure 13 - Conductor4SQL Client Jobs Summary Report
Client Database File Space Details Report
The Conductor4SQL Client Database File Space Details Report includes over a dozen parameters to help understand your database storage. This report even color codes the results to focus on particular issues.