August 23, 2011 at 1:16 pm
Background
I am part of a team that develops internal applications for a major company. We tend to create a large number of small applications using 1-4 developers on a project.
We have a number of support applications that we have written to provide easy and standard functionality to our other applications (security, auto-emailing, employee management, etc...)
When I am using the support applications I am often coding utilizing a specific ID from a support application. For example, I might use a ToolID from the security application.
Problem
That ID can be different between different environments. If the ID I am assigned by the support application in the Dev environment has already been consumed in UAT and/or Prod then I end up in a position where my code ends up customized by environment.
Like:
DECLARE @ToolID INT
SELECT @ToolID =
CASE @@SERVERNAME
WHEN 'DevelopmentServer01' THEN 57
WHEN 'UATServer01' THEN 201
WHEN 'ProductionServer01' THEN 33
END
I hate this. It is awkward, and subject to recode when migrating to new servers.
Question
Are there standard methods for resolving this sort of issue? Are there built-in features in SQL Server that I can take advantage of to resolve this?
It might be a little late to address this at a team level, simply due to how many applications we have developed, but if it isn't a huge effort for the department, I might be able to spearhead a rewrite to some of those support applications.
I am thinking that this sort of issue must be somewhat common, especially if you were using 3rd party software.
Is there a common name for this sort of issue, so I can refer to it properly?
Thanks
Stu
August 24, 2011 at 4:48 am
Let's assume the ToolID is the identity column of a Tools table. Then there also should be a description or name.
Instead of using the db internal ToolID directly, I would refer to the global description or name.
Example:
DECLARE @ToolID INT
SELECT @ToolID = ToolId
From dbo.Tools
WHERE ToolDescription ='MyTool'
Or did I miss something obvious?
When writing business logic, I try not to use db internal references as 'user input'. I'll use the value provided by the business logic and translate it internally.
August 24, 2011 at 5:49 am
In addition to the good advice given by Lutz, assuming these are lookup tables, we built a database that held the standard values for all lookup data. We only ever added the data through that common database. Then we deployed the data from that database to all our other environments so that simple lookup data was standard across the enterprise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2011 at 11:22 am
Hey Lutz,
The only reason I don't like that method is because I have a tendency to think of the ID value as what should be static, so the description can change without impact. It certainly does cut to the chase and solve the immediate problem, but does seem like it might expose an additional risk down the line. The risk might be fairly low though, since these are developer support applications, so it's not totally out of the question.
I may have to mull that one over.
Thanks!
Stu
August 24, 2011 at 11:33 am
Hey Grant,
That is more along the lines of what I was thinking. In fact, I was thinking it would be common enough that there might be support within SQL Server directly for a solution. I am guessing that if you already went down that road, you must have already researched that. I suppose environment and business needs would likely be varied enough that building something from scratch is probably the only option.
I think I will have to talk to our DBAs to see if it isn't something we could look into.
Thanks!
Stu
August 24, 2011 at 11:43 am
There is something in 2008R2 called Master Data Services, but the strictures for using it were so severe we couldn't get it to fly within our environment. We went the old-fashioned way and set up a process. We did take advantage of a tool, SQL Data Compare[/url], but I was loath to bring it up before since I work for Red Gate now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply