question

erlokeshsharma08 avatar image
erlokeshsharma08 asked

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.
copy-databaseproccreate databaseenvironmentuat
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
rvsc49 avatar image
rvsc49 answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks...the database's size is 269 GB...I think restoring would take hours and would not be a possible solution
0 Likes 0 ·
I'll explore other options thanks
0 Likes 0 ·
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.
0 Likes 0 ·
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. Thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.