3

We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary server restore backups daily from the primary server, but only the development instance could have data modifications.

  • The two databases are from different applications, but one database has triggers that alter data on the other database. Triggers update the second database hardcoding its name, like UPDATE database2..thetable

  • One database can fetch data from another via SQL queries from one application or Stored Procedures, hardcoded as SELECT FROM database2..thetable

We are considering joining the two SQL Server instances on secondary server (reporting and development) to simplify administration and memory managing, but I couldn't find a solution to make triggers and stored procedures calls another database dynamically and automatically with another name using the same suffix. Ex:

  • Instance: RS

    • DB1 (calls DB2 with DB2..table)
    • DB2

  • Instance: DEV

    • DB1 (calls DB2 with DB2..table)
    • DB2

Into:

  • Instance: default
    • DB1_RS (calls DB2_RS with DB2_RS..table)
    • DB2_RS
    • DB1_DEV (calls DB2_DEV with DB2_DEV..table)
    • DB2_DEV

What are the possible solutions to this? I can think about some, but they are too convoluted:

  • Create dynamically T-SQL to be executed via EXEC(). This makes thing more complex because the triggers have lots of code for UPDATES and INSERTS and DELETES.

  • Create separate triggers and stored procedures for each group of database names (one for DB1, other for DB1_DEV, other for DB1_RS). This approach seems to not scale...

  • Make the database name dynamic on the query: INSERT INTO @db..table. Unfortunately SQL Server doesn't support this... :-(

Is there a simpler and effective solution to this?

Thanks!

Improve this question

2 Answers 2

Reset to default
5

Not sure I fully understand all of the variables but I suspect you would be able to use synonyms that are different between the two environments which would allow the code to be the same. It is a layer of abstraction that requires documentation and/or an extra layer of the onion to peel, but can definitely simplify the code you write. Here is the CREATE SYNONYM topic... I have used them extensively so please let me know if you have further questions about them.

Improve this answer
1
  • Aaron, thanks for your response! This is the solution I was looking for! I like this abstraction layer, and the synonyms can be dropped/created automatically by our restore scripts when databases are restored for testing, development, reporting, etc. Thank you so much!
    – IT2
    Commented Jul 6, 2011 at 20:58
0

Have you looked into using linked servers? Linked servers help you to treat remote SQL server/DB as a local server/DB. You can create a linked server, and once you consolidate the DBs (on the same server) you can either change your linked server configuration, or use three part naming conventions for the DBs.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.