none
Database Aliases

    Question

  • Is there currently a way to created "database aliases"? We have many instances of SQL Server running in our development environment, and have a few metabases that are supposed to be in sync across all instances. It would be extremely handy to have a database "X" on Server A actually point to database "X" on Server B.

    As far as any application is concerned, Server A has a database called "X", but in reality the database only physically exists on Server B.

    It would be incredibly useful to centrally manage a database that is meant to be shared among many server installs. It would also help for app hosting situations.

    If this isn't possible, I'll enter it as a feature request for a later version of SQL Server.

    Thanks,
    Bryan Somerville
    Software Engineer
    Retail Anywhere, Inc.
    Friday, June 22, 2007 6:07 PM

Answers

  • As the other authors on this thread have already indicated, currently there is no support for this functionality. If you haven't already done so, please file a request via http://connect.microsoft.com. That way the requested functionality gets tracked as a customer request and can be considered for a future release.

     

    Thank you.

     

    Thursday, September 20, 2007 6:51 PM

All replies

  •  

    Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

     

     

    **unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

     

    hope it helps.

    Sunday, June 24, 2007 1:25 PM
  •  Ahsukal wrote:

     

    Have you considered using linked servers? It works with local and remote dbs, so you don't care where thoses dbs are. There is the slight drawback of addressing your special dbs with four part names, but that should be it**.

     

     

    **unless there is overhead with query optimisation when a local db is accessed as a linked server. It works well but I haven't tested perf in complex scenarios.

     

    hope it helps.

     

    The concept is something of a beginning on what he's looking for, but he's after much, much more. Essentially, he's looking for a database name to be a pointer and nothing else. To use a linked server, at least in its simple form, the calling app would still have to be aware of the linked server (just as a linked server instead of as a different connection). One way you could somewhat force this would be to create a different linked server for each database, and train the application to that. After that, whether the linked server is just another name for the local server, or serves as a passthrough to a different server is transparent to the calling application. You should start off with all databases on the same server, then move several off to another server without the applicaiton knowing (simply by changing the link associated with the linked server paired to that database). Note, however, that you are building significant management and performance overhead into things by doing it this way. You much deal with the issues surrounding the security pass through, and, perhaps more importantly, you are running things through the connectitivity layer not once, but twice now (with all that associated overhead).

    Sunday, June 24, 2007 2:56 PM
  • I find this a really interresting Idea, but from a slightly different perspective. This would mean that you can have two (or more) kind of front end database servers handling receiving all the requests, and have the database servers located behind these. The frontend servers could be load balanced using NLB. This would potentially improve the security, as none of the clients needs to have access directly to the backend servers, only to the front-end, which does not contain any data.

     

    I also see the advantage mentioned above regarding splitting the data. However, I can see an even greater improvement, it would simplify the process of migrating a database to new server. You do not need to do anything special on the client, nor the new server. You only have to update the reference, making it point to the new location. This would really be a nice feature, but unless the development team have started on this one already, I'm afraid it won't make it in SQL Server 2008.

    Monday, June 25, 2007 5:17 AM
  • That would also be a nice benefit, yes.

    Regarding security passthrough - that's a very good point. Perhaps an explicit linked server connection or other trust relationship could be required for aliasing to take place.

    The issue that made me ask about this is as follows:

    Our primary application is based on an extremely complicated, industry-specific (retail) database standard. We have a metabase that defines more intuitive "objects" like Customers, Items, Discounts, etc, and bidirectional relationships between them all.

    That database is often being added to, but is read only as far as the application is concerned. Unfortunately, we have database servers running locally on our dev systems, we have a multi-tiered distribution and testing system, we have sales giving demos, we have implementation staging and assisting with pilots, etc. We have to continuously backup and restore this database to keep things current, but it's hard to keep everything synced up. Replication adds a layer of complexity that we don't quite want. The application is extremely extensible - we can add complex new features without touching almost any code, provided we can keep the metabase up to date.

    All we need is a way to say "hey server, this database, as far as you care, is located elsewhere". When it comes time to take a backup to restore on site, the central database will be our only concern. When a client is hosted in our datacenter, we can just point it to the linked database.
    Monday, June 25, 2007 4:21 PM
  • Instead of replication, consider using log shipping. Essentially just utilize the log to forward the changes to the other copies of the database without the need for the low level monitoring and subtle changes that replication induces.

    Monday, June 25, 2007 5:31 PM
  • As the other authors on this thread have already indicated, currently there is no support for this functionality. If you haven't already done so, please file a request via http://connect.microsoft.com. That way the requested functionality gets tracked as a customer request and can be considered for a future release.

     

    Thank you.

     

    Thursday, September 20, 2007 6:51 PM
  • See
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311079
    and
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288421
    Monday, November 19, 2007 1:32 AM
    • Proposed as answer by ChalkyWhite Saturday, October 22, 2011 12:24 AM
    • Edited by ChalkyWhite Saturday, October 22, 2011 12:25 AM
    Saturday, October 22, 2011 12:24 AM
  • Any updates on this?  Are synonyms for databases supported now? Please advise.
    Friday, November 18, 2011 3:08 PM