How to treat Database Snapshots as if they were one database?

Hi,
I am considering various methods of creating a reporting database from
our production ERP system, in SQL Server 2005, and one of the solutions
suggested by the technical documentation is to create a database mirror
and a sequence of database snapshots. These snapshots, of course, have
different names.
This would be fine for client applications that we manage, in that we
could intercept client requests and connect them to the most recent
snapshots, and clear out the old snapshots as older connections finish.
However, we use applications such as Business Objects which point to a
named database. Does anyone have any suggestions about how we can get
such an application to always connect to the most up-to-date snapshot?
Thanks for any help in advance,
Rich"Rich B" <rjback@.hotmail.com> wrote in message
news:1138093573.261952.197210@.o13g2000cwo.googlegroups.com...
> Hi,
> I am considering various methods of creating a reporting database from
> our production ERP system, in SQL Server 2005, and one of the solutions
> suggested by the technical documentation is to create a database mirror
> and a sequence of database snapshots. These snapshots, of course, have
> different names.
> This would be fine for client applications that we manage, in that we
> could intercept client requests and connect them to the most recent
> snapshots, and clear out the old snapshots as older connections finish.
> However, we use applications such as Business Objects which point to a
> named database. Does anyone have any suggestions about how we can get
> such an application to always connect to the most up-to-date snapshot?
>
You can have a single database full of synonyms or views which point to the
most current snapshot. When you have a new snapshot you need to drop and
recreate all the synonyms or views. So you would need to ensure that client
applications don't hold schema locks (Sch-S) on the target objects for long
periods of time, preventing the switch. And you should recreate the objects
in a transaction so the client always gets a consitent view of the data.
David|||Thankyou, that seems like a reasonable approach.sql