Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Multiple Instances of Database on Same Server for each Environment

Posted on 2010-11-22
14
Medium Priority
?
330 Views
Last Modified: 2012-08-13
Hello,

We have stored procedures that access tables across databases. For example, we might have CustomerDev and OrderDev databases and the following query exists in a sproc on CustomerDev database (simplistic example):

SELECT * FROM [dbo].[Customer] c
INNER JOIN [OrderDev].[dbo].[Order] o ON c.orderxid = o.xid

Now say we have CustomerTest and OrderTest databases (same databases as Dev but for Test environment application to use) on the same physical database server.  

If we promote that same stored procedure to Test databases, the sproc is still hitting the OrderDev database instance instead of OrderTest.

This seems like it would be a common issue for any startup company or small project that needs multiple environments for promoting but only has one database server because its a shared hosting account or we can only afford one right now or etc.......

We don't want to use dynamic SQL statements to do this, we lose too much of the native SQL optimization features and its worse performance and a maintenance headache.  

The only other solution we can think of is to have a case statement detecting the database name which we would use to determine the environment, and then duplicate the sql logic in eadch case or if...else statement.  This is not ideal because of the amount of repeat code, also a maintenance nightmare.

Does anybody have a good solution for this issue?  

Thanks,
Steve
0
Comment
Question by:kruegerste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 34192484
One way to circumvent that restriction is to use an alias (synonym) or a view mapping the access to the other database. E.g. a synonym ORDER standing for [OrderDev].[dbo].[Order] resp. [OrderTest].[dbo].[Order].
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192510
I was just going to post that!
Anyway "ORDER" is probably a really bad word to use, but the idea is that you have an after-restore procedure that sets up the synonyms in one go (and you keep maintaining the script as your db evolves).  The procedure would be the only thing that is different between the databases.  That, and the synonyms that it maintains.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192511
I was just going to post that!
Anyway "ORDER" is probably a really bad word to use, but the idea is that you have an after-restore procedure that sets up the synonyms in one go (and you keep maintaining the script as your db evolves).  The procedure would be the only thing that is different between the databases.  That, and the synonyms that it maintains.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 71

Expert Comment

by:Qlemo
ID: 34192662
What, it isn't in ORDER to use that reserved name? :D
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192689
No. You can't guess by my posting the comment TWICE?!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192691
(sorry.. happens with the mobile client...)
0
 
LVL 4

Expert Comment

by:dquebe
ID: 34192776
This question looks the same as another recently posted. You would install SQL Server multiple times with named instances. For example, let the Production instance be default, install a named instance for "DEV" and "TEST". Each named instance has the databases with their production names. Scripting becomes easy.

Otherwise, use Visual Studio Database Project that allows for the creation of deployment and upgrade scripts that can change based on "variables". You could also script variables [$variablename] and use the command line sql executer to replace [$variable] with a value. It would do an exact text replace before executing the script.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192925
That would also work.
I have become accustomed to synonyms because we also deal with LINKED SERVERS, so it kills two birds with one stone.
I suppose you could macro the solution and create virtual networks on VMWare ESX and have the same server names, same prod db names and just different subnets, right?
0
 
LVL 4

Author Comment

by:kruegerste
ID: 34193227
Can either of you explain in detail how to do this or a link that explains  it? Sounds relevant, not sure how to execute.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34193267

drop proc refreshSynonyms
GO

-- your synonyms proc
create proc refreshSynonyms
@server1 sysname,
@server2 sysname,
@db1 sysname,
@db2 sysname,
@db3 sysname
as
declare @sql nvarchar(max)
set @sql = 'create synonym OtherServer_OtherDB__tbl1 for '+quotename(@server1)+'.'+quotename(@db1)+'..tbl1';
if exists (select * from sys.synonyms where name='OtherServer_OtherDB__tbl1') drop synonym OtherServer_OtherDB__tbl1
	exec (@sql);
set @sql = 'create synonym OtherServer_OtherDB__view1 for '+quotename(@server1)+'.'+quotename(@db2)+'..view1';
if exists (select * from sys.synonyms where name='OtherServer_OtherDB__view1') drop synonym OtherServer_OtherDB__view1
	exec (@sql);
set @sql = 'create synonym OtherServer_OtherDB__proc1 for '+quotename(@server2)+'.'+quotename(@db3)+'..proc1';
if exists (select * from sys.synonyms where name='OtherServer_OtherDB__proc1') drop synonym OtherServer_OtherDB__proc1
	exec (@sql);
GO

-- run it against each db (or after restore from prod -> QA -> Dev etc)
-- supplying the right names
exec refreshSynonyms
@server1 = 'SvrProdApp',
@server2 = 'SvrProdInterface',
@db1 = 'ProdAppDb',
@db2 = 'ProdAppDbSupplem',
@db3 = 'ProdFinancials'
GO

-- a sample query using a synonym
select c.name, SUM(invoice_amount)
from customer c
inner join OtherServer_OtherDB__view1 v on c.id = v.customer_id

-- which would have previously been
select c.name, SUM(invoice_amount)
from customer c
inner join SvrProdInterface.ProdFinancials..View1 v on c.id = v.customer_id

Open in new window

0
 
LVL 4

Author Closing Comment

by:kruegerste
ID: 34193453
thanks dude, awesome.  exactly what i was looking for.
0
 
LVL 4

Author Comment

by:kruegerste
ID: 34193458
Sorry Qlemo, probably should have split points, forgot to.  Thanks for the response though.  Thanks for the actual example cyberkiwi.
0
 
LVL 4

Expert Comment

by:dquebe
ID: 34193468
1. The professional (albeit expensive solution) Visual Studio Database Project. http://msdn.microsoft.com/en-us/library/ff678491.aspx Use with TFS for great source control and versioning. Can be used with approach 2 or 3 below.

2. Multiple named instances. Just run Setup again and install another instance.
http://msdn.microsoft.com/en-us/library/aa174516(SQL.80).aspx

3. Variables with SQLCMD http://msdn.microsoft.com/en-us/library/ms165702.aspx  Example script:

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar CheckNewConstraints "False"
:setvar DatabaseName "DBNAME"
:setvar DefaultDataPath "C:\MsSql\MsSql.Data\"
:setvar RxDatabaseName "Pharmacy"

GO


        SELECT @claimCount = COUNT (1)
         
            FROM [$(RxDatabaseName)].dbo.pharclaim AS PharmacyClaim
           
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34194723
For the sake of fairness, I would appreciate if you could request attention, ask for re-opening, and do a split then. But if you think it's not worth it, so be it.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question