Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.
An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.