How to handle different server names per environment

schematech

We are looking to move from our current mad environment, where everybody works on the same, LIVE set of servers & databases :shock: , to something more sane. Having got agreement in principle to building a separate test environment, I am now faced with the prospect of changing work practices to make that possible.
One thing I have spotted immediately is that in our current production system, with three database servers hosting many databases between them, we have many instances of inter-server stored procedure calls, using linked server references. We have a similar situation with Service Broker routes. We make huge use of Service Broker to post updates and notifications around the system, and all SB services that can be accessed from outside the host server have unique names on the network.
What I don't understand is how can a set of procedures etc that work in one environment be automatically deployed into another environment, which is logically similar, but with different names.
If you can point me at any materials or references I would be very grateful.
Thanks. Bob Cullen

Chris.Allen

Hi Bob- good job on getting your 'higher-ups' to recognise the need for (at least) a test environment(!)

I don't 100% get the question- but I will take a stab at it anyway.

I believe you're asking- how to treat server names (etc) as variables [such that deployments can be made to multiple environments with minimal changes].

Well I have good news. Its name is Octopus! ( https://octopusdeploy.com/

Octopus will treat environments/servernames etc as variables so that you can predefine the names according to what environment they are in (TEST vs PRODUCTION) and, when it comes to deployment, Octopus substitutes the real names in. It then gives you full control over: what steps take part in any deployment (code/databases, powershell scripts, manual approval etc).

Does this sound like it will help?

schematech

Hi Chris -
Thanks for your response. If I may elaborate with a couple of code fragments, it may help. I have three database servers that all form part of the same solution, so when I deploy a new feature it may well touch all three servers. Let's assume that in my current production environment I have code on my web server that calls a sproc on a different server, like this:

exec @ret = [IVRProduction.mydomain.local].IVRStuff.dbo.spDoSomething

Now I build my test setup which also has three servers, but of course they have different names. So to make the code work correctly I need to execute

exec @ret = [IVRStaging.mydomain.local].IVRStuff.dbo.spDoSomething

What I don't understand is where and how the substitution at a code level will get performed. The same problem applies when posting to Service Broker services, which are named according to the server they live on, and which have matching entries in the Routes hive. So, a SB dialog will be opened with something like

begin dialog conversation @dialoghandle
				from service [WebProduction.IVR.VCC]
				to service 'IVRProduction.WebEvents.VCC'
				on contract [WebEvents.VCC.Event]
				with encryption = off

Unless the names can be substituted somehow to make them valid for the Staging environment, it will end up sending to the wrong service - and I won't be a popular man!!
Maybe I am thinking about this wrong, but that's why I posted a request here.

Don't know if this helps - hope so. Bob