Alternative to not having UAT environment

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.

more ▼

asked Nov 04, 2016 at 02:13 PM in Default

avatar image

2k 4 12 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 04, 2016 at 03:03 PM

avatar image

188 3

Thanks...the database's size is 269 GB...I think restoring would take hours and would not be a possible solution

Nov 05, 2016 at 05:31 AM erlokeshsharma08

I'll explore other options thanks

Nov 05, 2016 at 05:32 AM erlokeshsharma08

If you go down this route, you are facing all sorts of other problems with using production data in non-production environments... Make sure you anonymise / obfuscate your data between prod & non-prod.

Nov 07, 2016 at 10:23 AM ThomasRushton ♦♦

Right...I am not a db admin. So want an expert opinion. What would be your alternative @thomasrushton given the circumstances.

I had another opinion - i.e. to update our dev environment by syncing it with production every 14 days. This will ensure all sorts of testing takes place with virtually the live data.


Nov 07, 2016 at 10:37 AM erlokeshsharma08
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 04, 2016 at 02:13 PM

Seen: 56 times

Last Updated: Nov 07, 2016 at 10:37 AM

Copyright 2018 Redgate Software. Privacy Policy