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.
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 Employeesas a table to mirror:
The source table comes from
Northwindinto the Destination Mirror.
The fields will be in the new
Mirrortable 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
Iss2008PkgNorthwindEmployeesinside 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:
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]
CREATE TABLE [dbo].[Employees_NEW] (
drop table [dbo].[Employees]
exec sp_rename 'employees_new', 'employees'
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.
- Initial version 1.0 - 5 July 2011