Hi Guys This is a question regarding mimicking the UAT environment if you don't have one. We have development and prod environments and have different report servers (SSRS) set up to point dev, uat and prod environments. However, for uat and prod the backend database is same. So whenever we want to test certain reports in uat report server we first make a seperate stored proc and point the report to this stored proc. As you would realize this basically creates 2 copies of a given stored proc. Are there some techniques such as database mirroring where we could just create a copy of the prod database and call it as uat's version of the db. This is because we cannot get a standalone server for UAT database. Hope it makes sense, please ask questions for some clarity.
Since you do not have a database server for UAT, why not make a daily copy of your production database (back it up), then restore it with UAT in the database name and place it on your development SQL Server? Then, point your UAT reports to it. Yes, you will still have two copies of the same procedure, but, you could have uat in the name to differentiate them. Yes, log shipping and database mirroring will provide you with copies of your production database and keep the data in sync, but as you know, there are limitations with readability. For example, with Log Shipping, although a log shipping secondary can be made readable using “STANDBY” mode, it’s just readable to a specific point in time. Then, when its restoring a log, it kicks all the users out. Also, with regard to database mirroring, a mirrored secondary is non-readable. If you have enterprise, there are database snapshots. Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation. See
https://technet.microsoft.com/en-us/library/ms175472(v=sql.105).aspx for more info on snapshots.