Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

OK, here's a challenge for you guys.

I am tasked with setting up reporting for two companies being run as a single company. For reasons I won't get into, they are implicitly split but (for our purposes) are run identically. They each have two software implementations for which I have access to the databases.

What is being done currently is we have a suite of queries that output to reports in various formats, and the logic is replicated between the two companies, making for 4 sets of queries.

This graphic represents the DB summary, and for each of them there are reports. We are using MS SQL Server.

AB Grid

We may add additional software in the future, turning 4 suites of queries into 6, 8, etc. I need to ensure that the query logic has no variation between the two companies in order to minimize the risk of discrepancy and for ease of development.

So here's the question:

Is it possible to share queries somehow between companies without using Dynamic SQL?

share|improve this question
    
Have you tried to look into Linked servers ? I would suggest you to get the data into a centralize database and then do reporting. Linked server queries would be quiet expensive depending on the n/w and the amount of data you are pulling. –  Kin Aug 21 at 20:19
    
All 4 DBs currently reside in the same server. I hadn't considered creating "merge" databases, and that might be a valid approach. It would definitely work but there is no shortage of work on the front-end since each of the software's DBs contain hundreds of tables. –  n8. Aug 21 at 20:25
    
Out of curiosity, why are you afraid of Dynamic SQL? Anyway there are tools out there that can help you synchronize multiple databases to make sure, say, a stored procedure is the same in both databases, or to help you deploy a change to multiple databases at the same time. –  Aaron Bertrand Aug 21 at 21:00
    
Well, I'm kind of new to this stuff and I keep hearing that Dynamic SQL has a lot of risks. It makes sense and I am using it now, but in lieu of widespread implementation I first wanted to check and see if there were better solutions available. –  n8. Aug 21 at 22:12
    
Have you looked into using different SCHEMAs for each set of tables/objects within single DB? –  Stoleg Aug 21 at 22:20

2 Answers 2

up vote 3 down vote accepted

Some other ideas:

  • Use readily available tools to compare two databases after a change is deployed (you can automate this perhaps, depending on your existing deployment process), and synchronize them

  • Automate the deployment of changes such that changes to procedures always occur against both databases (again this depends on what your current deployment process entails)

  • Implement dynamic SQL carefully such that data is pulled from the right database depending on the user connecting.

  • Use a central database with views or synonyms under different schemas, each set pointing to the two different databases. Construct the calls to procedures dynamically, or leave off the schema prefix and set the default schema per user (you can double up on the security by granting database A's user access to B's schema and vice versa).

share|improve this answer
    
OK, I feel goofy that I didn't consider copying/distributing sprocs from a development DB. But now that I am considering it I can't find much on how to do it in an automated fashion. Scripting them doesn't seem to be the way to do it. –  n8. Aug 21 at 22:39
    
Ok, you get a +1 for following the sane approach! –  Max Vernon Aug 21 at 23:03
    
@n8 well, how do you deploy a stored procedure change in dev to one database in production? For two databases, just do that twice. For more, you could look at SMO (via C# or PowerShell) or tools like MultiScript from Red Gate. –  Aaron Bertrand Aug 21 at 23:06
    
I right-click and modify, which places a "USE" statement at the top. I guess I can just change the USE database for every "alter proc" that I do. –  n8. Aug 21 at 23:20
1  
@n8 You can turn that off. Tools > Options > SQL Server Object Explorer > Scripting > set Script USE <database> = false. –  Aaron Bertrand Aug 21 at 23:34

Assuming you can use Stored Procedures for the queries, you could create a single database for the purpose of version control of the queries in question.

Each Stored Proc could then check with the version_control database to see if the stored proc is the latest version, and refuse to run if it is not the latest version.

Something like:

--Create a database to hold the version control data
CREATE DATABASE VersionControl
ON (NAME='VersionControlData', FILENAME='D:\SQLServer\MV\Data\VersionControl.mdf', SIZE=32MB, FILEGROWTH=32MB)
LOG ON (NAME='VersionControlLog', FILENAME='D:\SQLServer\MV\Log\VersionControl.ldf', SIZE=16MB, FILEGROWTH=16MB);
--set the log to simple recovery mode.
ALTER DATABASE VersionControl SET RECOVERY SIMPLE;
GO
--Add some tables to the version control database
USE VersionControl
GO
-- this table stores "Packages" that you want version controlled.
CREATE TABLE dbo.Packages
(
    PackageID INT NOT NULL CONSTRAINT PK_Packages PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , PackageName VARCHAR(255) NOT NULL
);
-- this table holds details about the individual versions you have created.
-- update this every time you release a new or updated Stored Procedure
CREATE TABLE dbo.Versions
(
    VersionID INT NOT NULL 
        CONSTRAINT PK_Versions 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , PackageID INT NOT NULL 
        CONSTRAINT FK_Versions_PackageID 
        FOREIGN KEY REFERENCES dbo.Packages(PackageID)
        ON UPDATE CASCADE
        ON DELETE NO ACTION
    , VersionNumber DECIMAL(10,2) NOT NULL
        CONSTRAINT DF_Versions_VersionNumber
        DEFAULT ((0))
    , IsActive BIT NOT NULL
        CONSTRAINT DF_Versions_IsActive
        DEFAULT ((0))
);
GO
-- Add some sample data to test the hypothesis
INSERT INTO dbo.Packages (PackageName)
VALUES ('TestProc');
INSERT INTO dbo.Versions (PackageID, VersionNumber, IsActive)
VALUES (1, 1.01, 0);
INSERT INTO dbo.Versions (PackageID, VersionNumber, IsActive)
VALUES (1, 1.02, 1);
GO
-- Create a test stored procedure (normally this would be created in one of your
-- two production databases for each company.
CREATE PROCEDURE TestProc
AS
BEGIN
    DECLARE @ThisVersion DECIMAL(10,2);
    SET @ThisVersion = 1.02;
    DECLARE @CurrentVersion DECIMAL(10,2);
    SELECT @CurrentVersion = VersionNumber
    FROM VersionControl.dbo.Versions V
        INNER JOIN VersionControl.dbo.Packages P ON V.PackageID = P.PackageID
    WHERE P.PackageName = 'TestProc' 
        AND V.IsActive = 1;
    IF @ThisVersion <> @CurrentVersion 
    BEGIN
        -- ALERT THE USER THAT THIS IS NOT THE CURRENT VERSION
        DECLARE @msg NVARCHAR(255);
        SET @msg = 'YOU ARE ATTEMPTING TO RUN AN OLD VERSION OF TestProc';
        RAISERROR(@msg, 0, 1) WITH NOWAIT;
    END
    ELSE
    BEGIN
        -- PERFORM THE ACTIONS NECESSARY
        SELECT 1;
    END
END
GO
-- run the test procedure (it will return '1' indicating it successfully ran)
EXEC dbo.TestProc;
GO
--Reset the IsActive bit on the old versions
UPDATE dbo.Versions 
SET IsActive = 0
WHERE PackageID = 1
    AND IsActive = 1;
--Insert the new Version number into the version control table
INSERT INTO dbo.Versions (PackageID, VersionNumber, IsActive)
VALUES (1, 1.03, 1);
GO
--Try running the old version - it will fail reporting an error.
EXEC dbo.TestProc;

Clearly, there are some flaws in this system. In order to version control your queries, you must check the version control table each time any query is ran. You must update the version control table accurately. You must update each stored proc whenever you make a change to look for a new version number.... etc. etc. etc.

Consider this a poor-man's version control system that can work if you use it correctly!

share|improve this answer
2  
This is pretty cool, I just learned a few things. –  n8. Aug 21 at 22:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.