Friday, May 1, 2009

Linked Server Naming

Spurred by a post from SQL Batman I wanted to share a few of the Linked Server topologies I have encountered. I see the reasoning behind each one, and I have yet to find the be-all-end-all of naming conventions.

Both scenarios include the development sandbox servers, testing and production servers. Keeping simple references by server function seems to be common and helps with the reuse of code. Additionally, having entries to link across environments helps with change management.

JobA (all found on each server)
PUB - publisher
SUB - subscriber
EXT - High volume query processing server
WEB - Internet database server
INT - Intranet database serer
BI - Datawarehouse server
RPT - Reporting server
{servername} - publisher
{servername} - subscriber
{servername} - High volume query processing server
{servername} - Internet database server
{servername} - Intranet database serer
{servername} - Datawarehouse server
{servername} - Reporting server
PROD - matching server in Production environment
Example: if current server is the Publisher, this links to the Production equivalent
DEV - matching server in Development environment
TEST - matching server in Testing environment

LINKSVR_APP - Application DB Server
LINKSVR_STG - BI Staging Server
LINKSVR_RPT - Reporting Server
{servername} - Application DB Server
{servername} - BI Staging Server
{servername} - Reporting Server

In addition, this is what I have seen for a Replicated environment:
Development: PUB (publisher), SUB (subscriber)
Testing: PUB (publisher), SUB1(subscriber), SUB2 (subscriber)
Production: PUB (publisher), {loc}_SUB (subscriber 1), {loc}_SUB (subscriber 2), {loc}_SUB (subscriber 3), etc...

In the end, I believe you need two sets of linked servers. One set for the application to utilize and another set for the maintenance of the environment.

No comments:

Post a Comment