Solved

How do I centralize Hostname/IP address so they can be changed in one place for all stored procedures?

Posted on 2014-04-10
2
235 Views
Last Modified: 2014-04-10
Historically, our development team hard coded the IP address or hostname into each stored procedure. I want to point all of the stored procedures to a centralized location so that I can change these values once, make a server change, and still have all of the stored procedures function correctly. Can anyone give me some insight as to how this can be accomplished? We have over 300 stored procedures and it appears that they will have to be updated manually.
0
Comment
Question by:dpwhite
2 Comments
 
LVL 35

Accepted Solution

by:
Bembi earned 500 total points
ID: 39992677
A common upgrade problem. I guess the best solution is to use an alias name for our solution and register the alias name in your DNS as CNme (or use local Alias names for SQL server.). This way the name is neutral and when the target changes, you either cahnge just the DNS or local alias.

The decision, if local alias or DNS name depends, if it is a server to server (SQL alias) or client to server (DNS) connection. SQL Alias has to be changed on each clients,which accesses the server, For IIS to SQL connections ok, but for clients, which has to find the SQL server better via DNS
0
 

Author Closing Comment

by:dpwhite
ID: 39992801
Thanks for the solution. This gives me the ability to migrate quickly and change the stored procedures at a slower pace.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Is there a better way to combine two queries 7 27
TSQL XML Namespaces 7 23
SQL Group By Question 4 20
job schedule 8 20
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

837 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