UAT server refresh strategy

Question

Post reply

UAT server refresh strategy

Jason Marshall

SSCommitted

Points: 1791
More actions
June 15, 2012 at 7:46 am

#257606

We are repurposing an old production server as a UAT box (finally!!) and are having discussions on the refresh strategy. What are some things you guys have done that work well? Anything not work particularly well? Refresh will probably be done weekly, and UAT schema is to mirror prod at time of refresh. UAT box will hold less data due to space constraints (data size will still be in the terabyte range). Refresh window will likely be 12 hours max.

Thanks for the insight,

Jason

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

What typically works best is to apply the same deployments to UAT as you do to Prod. This means that if a deployment to UAT fails, the deployment needs to have a rollback plan in place that gets implemented. This is nice because it also ensures on those occassions that the rollback plan works too.

I would try to avoid doing backfills from production to UAT. If the engineering team is doing its job, it won't be needed.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

SQLisAwe5oMe SSChampion Points: 14027 More actions · Answer 2

Jason Marshall (6/15/2012)

We are repurposing an old production server as a UAT box (finally!!) and are having discussions on the refresh strategy. What are some things you guys have done that work well? Anything not work particularly well? Refresh will probably be done weekly, and UAT schema is to mirror prod at time of refresh. UAT box will hold less data due to space constraints (data size will still be in the terabyte range). Refresh window will likely be 12 hours max.

Thanks for the insight,

Jason

Hi, Do you mind explaining little bit more as to what exactly you mean by refresh strategy and when would you go for it.....what does it include? Please advise. Anyone can chime in.

Thanks,

TA

Regards,
SQLisAwe5oMe.

Jason Marshall SSCommitted Points: 1791 More actions · Answer 3

Sure; I mean updating the schema and data of our "warehouse" databases, for lack of a better term. These are databases that hold current and historical data that the developers access through a collection of views to create reports.

I am planning on restoring production backups for all user databases, but for the warehouse I only want to include active records due to space constraints. My thought was to use DROP/CREATE scripts and then SSIS packages to load only active records, but was wondering if anyone had done something similar that worked well.

Refresh would be done weekly on Monday after the previous week's deployments went in...

SQLisAwe5oMe SSChampion Points: 14027 More actions · Answer 4

Jason Marshall (6/15/2012)

Sure; I mean updating the schema and data of our "warehouse" databases, for lack of a better term. These are databases that hold current and historical data that the developers access through a collection of views to create reports.

I am planning on restoring production backups for all user databases, but for the warehouse I only want to include active records due to space constraints. My thought was to use DROP/CREATE scripts and then SSIS packages to load only active records, but was wondering if anyone had done something similar that worked well.

Refresh would be done weekly on Monday after the previous week's deployments went in...

Thanks so much for your quick reply.

TA.

Regards,
SQLisAwe5oMe.