January 29, 2015 at 7:09 am
hello,
we have a number of possibilities but wanted to know peoples thoughts or stories:
we have a active/passive sql 2012 enterprise cluster and will be creating AlwaysOn (AO) group(s) to provide quick failover for all application databases and better data protection without restores being required
- we currently have log shipping down to our office from hosting
- reporting needs are now growing and the offline time for log shipping restores is no longer feasible
- we cannot get an enterprise licence for the office as they will not pay an extra £25k+ per server just for reporting (we have 2 servers to populate)
this means we cannot have a remote AO replica down here
that leaves me with using sql transactional replication against the AO replica across a VPN
can anyone think of another solution?
=================================================================
this raises the questions:
- can we replicate from the passive cluster node without having to buy a sql licence (no querying, backups....just replication)
- how would we manage an automatic failover situation with the passive node replication still being able to run....would both instances run happily on the now active node??
please comment, ridicule or advise!! i dont have the ability to test the failover with replication scenario currently so am looking for some advice before i speak to our hosting company and arrange to get our hosted servers properly configured
many thanks
January 29, 2015 at 9:20 am
Replication is a big can of worms. I would personally avoid it for a number of reasons.
AO readable secondaries have HUGE issues, including modifying PRIMARY data structures to add the 14-byte version store pointer (most don't know about that).
I want to know EXACTLY why you feel that tlog shipping is no longer acceptable for your needs, as this is by far the simplest option that has the least effects. It also allows for direct and quick recoverability as the primary should something happen to the main server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2015 at 9:33 am
thanks kevin
the reason log shipping is now becoming unusable is the want to drop the restore window to 15mins. it could take 5 mins to restore the log and standby work leaving 10 minutes to query the database....and we have reports that run longer that that let alone the datamart building.
we used to have 2 copies....one to midnight to build datamarts and one close to realtime
the business requires a as-close-to-as-possible realtime copy of live to query during the day and run reporting from and overnight it can be used for datamart building.
how can i get this without either buying enterprise licences or using replication and still have the databases online all the time??
January 29, 2015 at 9:41 am
lilywhites (1/29/2015)
thanks kevinthe reason log shipping is now becoming unusable is the want to drop the restore window to 15mins. it could take 5 mins to restore the log and standby work leaving 10 minutes to query the database....and we have reports that run longer that that let alone the datamart building.
we used to have 2 copies....one to midnight to build datamarts and one close to realtime
the business requires a as-close-to-as-possible realtime copy of live to query during the day and run reporting from and overnight it can be used for datamart building.
how can i get this without either buying enterprise licences or using replication and still have the databases online all the time??
Can you not just report off of the production copy for that subset of reports that need "near-real-time" data freshness? If you isolate those (and tune them so they don't crush the system) then you could probably back up your restore window on your secondary to 30-60 minutes or perhaps more. You could still do backups and copies on 15 minute interval for recoverability purposes, just hold the restores until your secondary window. This would give you 3 levels of reporting freshness which should be pretty easily manageable. I have set up this exact arrangement at several clients.
If you cannot make that work then replication will be your only option to avoid needing an Enterprise SQL Server license (for database mirroring snapshots or readable AG secondaries either one).
Oh, I bet you (or someone) can tune your reporting queries to get them to run in less time than 15 minutes. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2015 at 9:49 am
oh....and there is no requirement for resilience, HA, standby or failover actions for this
it is purely for reporting
is there a better way to replicate the data changes to a standby database without using TSQL or SSIS??
January 29, 2015 at 9:57 am
thanks
i could tune most of the reports....i would never let a report run on live for more than 5 minutes anyway if i can avoid it
the business doesnt want to report off live apart from flash management reports which need up-to-the-minute data for website activity monitoring.
its more for the dev and analysis team to run deep-dive queries, test new reports and queries, tune existing application processes etc. Cannot do that with the constant single-user mode of logshipping and they require recent data
say we make a change to the application and push out a release....they want to see within 15mins if it has worked and without stressing the live server...and dont want to spend too much money either
its a hard one to satisfy all the needs of all sides
my last option would be to use standby databases but use the DAS (MD3200) to create readable san clones but i havent even configured the DAS yet and they want this resolved by monday lol!!
January 29, 2015 at 6:54 pm
lilywhites (1/29/2015)
thanksi could tune most of the reports....i would never let a report run on live for more than 5 minutes anyway if i can avoid it
the business doesnt want to report off live apart from flash management reports which need up-to-the-minute data for website activity monitoring.
its more for the dev and analysis team to run deep-dive queries, test new reports and queries, tune existing application processes etc. Cannot do that with the constant single-user mode of logshipping and they require recent data
say we make a change to the application and push out a release....they want to see within 15mins if it has worked and without stressing the live server...and dont want to spend too much money either
its a hard one to satisfy all the needs of all sides
my last option would be to use standby databases but use the DAS (MD3200) to create readable san clones but i havent even configured the DAS yet and they want this resolved by monday lol!!
That main requirement really doesn't sound like it would need data as fresh 15 minutes. Not at all. As for your justification about "pushing out a release and wanting to see within 15 minutes if it has worked", well, you need MUCH better testing if you have to worry about that!!!
SAN clones (on the same set of disks) WILL create a performance issue against the production system!
You have a solution, and it meets ALL reasonable requirements. Log shipping with less frequent updates (with ability to apply 15 minute logs immediately after a patch if you like - that is still doable). They want to be cheap, then they need to have cheap expectations. It is as simple as that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2015 at 3:16 am
i know this.....but i still have to provide something they will be happy with
January 30, 2015 at 9:26 am
lilywhites (1/30/2015)
i know this.....but i still have to provide something they will be happy with
Then your primary responsibility is to adjust their expectations so they will be happy with the only reasonable solution available to them (based on THEIR constraints). Easy-peasy, lemon-squeezy!! :hehe:
Good luck with it!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2015 at 10:36 am
thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply