December 2, 2016 at 8:07 am
hello, we have a 5 instance sql 2014 SP2 QA environment which has the same specs as our PROD environment. Three out of the five instances database the total size of the database equal to around 3 TBs and we also have transactional replication set up in QA with their according reporting instances; same way as its set up in our production environment.
It is very ideal to do our testing; the problem however is that QA is looking to have their environment available at all times, but they also are requiring to have their databases refreshed every month. Usually our Refresh process takes around one week starting by writing all the scripts for restores, moving backups, scripting replication, permissions, then ending by putting everything back after restores.
I was thinking maybe we could integrate the always on availability group and have QA pointing to that environment while we work on the other one, then once we are done with the restores and post restore scripts then sync it? Has anyone done that for refresh? Also, how do sql jobs and replication come into play with always on environments? Usually we remove replication completely , restore databases and then put back replication but the synch takes one full day at least.
If you don't think always on environment would be the best in the case, can you let me other tools you might have used to always have a QA environment or for a refresh this size with replication be done in a short amount of time?
December 2, 2016 at 10:36 am
Availability Groups only support read only secondaries. Is QA a read only process or do they do writes? If they do writes, you can't use AG to solve this at all.
For the data sizes you're looking at, there are not good solutions currently outside of using some type of SAN snapshot process. Those could work for what you want. It depends on your infrastructure.
Disclosure: I work for Redgate.
You should check out SQL Clone. It's a brand new tool from Redgate. It allows you to mount a backup through the use of a virtual drive. It achieves are restore in about 30 seconds or so. It currently only supports 2tb, not 3tb, but it could be something useful for you in the future. It would be ideal for your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2016 at 12:30 pm
thank you Grant! yes QA do writes and updates . Thank you for your recommendation, i will look into the redgate tool.
December 7, 2016 at 8:08 am
hi, i was planning on removing the databases I refresh from availability group. QA users can continue using those databases as well as others in the primary. I refresh the databases in the secondary (since i removed them from availibilty group, they are not read only anymore). Complete my refresh and then add those databases back in the availability group. Change my Secondary to be my primary. Is this possible?
December 8, 2016 at 6:21 am
We had a similar situation - but did something different.
We needed to buy new SAN's anyway - so we found a few that do SQL SAN level duplication (and compression)
Long story short we can now do a SAN snap of our production data onto our QA server in around 10 minutes (over 2TB of data). This refresh process took 9 hours the old way. And because of de-duping we don't use much storage space. Neat stuff.
If interested look into flash storage SAN's - we use Solidfire.
December 8, 2016 at 9:35 am
thanks for your recommendation, did you also have transactional replication? the issues with our refresh is usually the time it takes for the restores and also the reinitialization of replication after the restores.
December 8, 2016 at 10:01 am
We are using high availability.
December 8, 2016 at 1:41 pm
We use Availability Groups in some of our production systems and replication in others, and yes, Microsoft does not make life easy for what you are trying to do. Best bet is to investigate 3rd party solutions such as the one Grant suggested.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply