Database Mirrors with Differing Vendors

Introduction

Developers are often confronted with an Enterprise-crucial database from a vendor where the risk of even reading data during production hours can be very high. One low-level solution is to beg for text file extractions from the crucial production DBA folks. As an alternative, the Microsoft SqlServer product now offers a high speed interface through the industry standard ODBC connection technology to databases from most vendors. When a delay, say 24 hours, can be tolerated, one can produce a database mirror to almost anything (e.g. Oracle, Intersystems Cache) using the SqlServer Integration Services (ISS) functionality. Now that three terabyte hard drives are available, space on the mirror side is less of a problem than ever before.

Background

Before ISS or the earlier DTS version, one faced writing lots of code to parse the text files kindly supplied to developers using SqlServer for a mirror. The toolset and wizards made available through recent versions of SqlServer (2000, 2005, 2008) should make unnecessary most of that hand coding to parse text files. The key to make this happen is to negotiate an agreement with the production DBA folks so an off hours read-only access can be made for the ODBC connection to SqlServer. With the mirror site in place and working, huge stress can be removed from the production environment and from those involved on both sides. My first mirror site was motivated by a production DBA running a new query that shut down the enterprise for some minutes!

Using the Code

To minimize the effort in building a mirror site, I begin by negotiating for read-only access to the minimal set of tables needed from the enterprise DBA. Each enterprise source table then becomes an ISS package. The SqlServer import wizard will build the simple base starting package for you for a specific table.

First, I have built a new database called Mirror. The import wizard is shown as follows with the sample Northwind Employees as a table to mirror:

The source table comes from Northwind into the Destination Mirror.

The fields will be in the new Mirror table of the same name.

The package will be saved and optionally run as when the table is small.

The base package has been saved as Iss2008PkgNorthwindEmployees inside SqlServer for use in the BI version of Visual Studio (VS). Importing the base package into a new project with VS produces the following after a little annotation:

In a real mirror, more logic is needed as shown in the extended package:

--
-- TSQL source code for the above blocks is:
--
IF  EXISTS (SELECT * FROM sys.objects _
	WHERE object_id = OBJECT_ID(N'[dbo].[Employees_NEW]') AND type in (N'U'))
drop table [dbo].[Employees_NEW]   	-- drop table in case of partial 
				-- results in a previous run


CREATE TABLE [dbo].[Employees_NEW] (
[EmployeeID] int,
[LastName] nvarchar(20),
[FirstName] nvarchar(10),
[Title] nvarchar(30),
[TitleOfCourtesy] nvarchar(25),
[BirthDate] datetime,
[HireDate] datetime,
[Address] nvarchar(60),
[City] nvarchar(15),
[Region] nvarchar(15),
[PostalCode] nvarchar(10),
[Country] nvarchar(15),
[HomePhone] nvarchar(24),
[Extension] nvarchar(4),
[Photo] image,
[Notes] ntext,
[ReportsTo] int,
[PhotoPath] nvarchar(255)
)

-- data flows here

drop table [dbo].[Employees]   -- remove older copy inside the mirror

exec sp_rename 'employees_new', 'employees'  -- put new copy into working mirror

create index employees_0 on employees(EmployeeID) 

create index employees_1 on employees(LastName,FirstName)

In a real mirror site, the above package would be expanded beyond indexing with possibly many more steps. The overall package is designed so the mirrored table remains available even if the new mirroring read fails.

To make the package into a little software robot, the BI project properties would be set to permit a deployment utility. The package is then built for use in a recurring SqlServer job step. Using the BI build option produces the following inside the project bin\deployment directory:

Double clicking on the manifest launches a wizard to install the package for use in a SqlServer job.

It is well worth looking into the ISS technology since this article barely scratches the surface on what is possible. By use of the script task on the VS project toolbar, you can include .NET code blocks in either VB or C# within the overall flow. You may also find a mirror site will become essential almost as the related production environment.

Since so much functionality is available within SqlServer and ISS, non-obvious additional uses are possible such as a framework for automated testing or security scanning and the like.

History

• Initial version 1.0 - 5 July 2011