Head Office:

24 Sturdee Avenue

Rosebank

2196

Contact:

+27 (0) 11 301 0900

info@conductor4sql.com

Office Hours:

Monday to Friday

07h30 to 16h30

  • White Facebook Icon
  • White Twitter Icon
  • White Google+ Icon
  • White YouTube Icon

Our Partnerships:

September 29, 2014

September 16, 2014

Please reload

Recent Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Featured Posts

Automate SQL Server Management Across the Enterprise with Conductor4SQL

Automate SQL Server Management Across the Enterprise with Conductor4SQL

 

Problem

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?

 

Solution

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:

  • Code deployments

  • 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

  • and more. 

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

  • Status

    • Values: Matching, Difference - Missing Table, Difference - Column Definition, etc.

  • Column Name

  • Collation

  • Type

  • Length

  • Precision

  • Scale

  • Nullable

  • Identity

  • View Differences

 

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.

 

Figure 14 - Conductor4SQL Client Jobs Summary Report

 

How do I get started with Conductor4SQL?

  1. Go check out all of the resources available for Conductor4SQL.

  2. Download your FREE 60 Day Conductor4SQL trial.

  3. Think about all of the opportunities for Conductor4SQL to improve your SQL Server management and benefit your business.

  4. Communicate to your team and management about how you think Conductor4SQL will help.

    1. Single tool set to resolve numerous issues

    2. Simplify tedious SQL Server tasks

    3. Streamline code and file deployments

    4. Resolve the nagging auditing needs

    5. Beneficial reporting on core SQL Server needs

    6. Query across the enterprise for both DBA and business needs

    7. Save time for our team to focus on new initiatives

  5. Put Conductor4SQL through its paces, share the results with your team and determine the next steps.

 

Next Steps

  • We have just scratched the surface with all of the functionality available with Conductor4SQL.  The tool has a great deal features to streamline SQL Server Management and help your business grow.  Sound interesting?  Check out these resources:

    • Product Introduction

    • Learn more with these Videos

    • Download your FREE 60 Day Conductor4SQL trial

 

 

Tags: