October 20, 2014 at 5:17 pm
Hi All,
We are planning to migrate to sql 2014 and implement always on high availability groups.
We implementing always on mainly because of reporting server. We don't have OLAP source for the reports. Our current reports are running hours and hours and causing deadlocks on production database. So We are planning to have asynchronous replicas.
I am looking for some best practices of migrating 2008r2 to 2014 and implementing always on.
We are using always doesn't support on vmware.
We are using now vmwares only.
October 21, 2014 at 12:42 am
Instead of doing this probably quite costly migration, why don't you create a simple ODS or data warehouse?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 22, 2014 at 4:36 am
ramana3327 (10/20/2014)
I am looking for some best practices of migrating 2008r2 to 2014 and implementing always on.
a simple google search would produce the results you require. VMWare have an extensive range of BP guides and so do Microsoft, too many for me to list here, but here's a taster
http://www.vmware.com/business-critical-apps/sql-virtualization/microsoft-support.html
ramana3327 (10/20/2014)
We are using always doesn't support on vmware.We are using now vmwares only.
AlwaysOn is fully supported on VMWare ESX platforms
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2014 at 4:43 am
ramana3327 (10/20/2014)
We implementing always on mainly because of reporting server. We don't have OLAP source for the reports. Our current reports are running hours and hours and causing deadlocks on production database. So We are planning to have asynchronous replicas.
You're looking at Enterprise edition licenses for at least two servers (the primary and the async), does the budget support all that for reports that are probably still going to run for hours?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2014 at 12:13 pm
How realtime do the reports have to be? Offloading the reporting to another (Standard Ed.) server, with automated nightly restores, or perhaps log-shipping with readable secondaries, might be a much less expensive/risky option. The problems you're having are solvable with 2008R2 technology, I'd say...not to dissuade you from an upgrade project but there's an awful lot of work/expense/complexity/risk with version upgrade projects. You also can consider things like isolation levels but I'm no expert in those and they can both solve and create problems, so caveat emptor!
October 23, 2014 at 11:04 am
Thanks for the replies.
Yes, they are ready for the budget. They want 3 replicas for some other purpose also. One is specially for reports.
Some how now just one simple report is taking minimum 12 hr time.
The row count in that report is 28000 and byte count is 790000.
It is taking time data retrieval for that report is 32000000 ms, Time processing is 4940ms & Time of rendering 26000.
The report server is getting data from the OLTP linked server.
We need to run that report for a particular time. i.e. every 24 hrs one time.
We are not optimizing that report. So we need to have other source
October 23, 2014 at 11:17 am
Just to be clear, moving the report to a read only replica won't necessarily make it any faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2014 at 12:04 pm
Yes but now it is hitting the production database performance (OLTP). That is the reason they want the read only copy for the reporting source
October 23, 2014 at 12:07 pm
Here I have one doubt about the availability groups.
Is it possible to have AG created across multiple domains?
Is it possible to have AGs created across VMs on same physical host?
October 23, 2014 at 12:11 pm
ramana3327 (10/23/2014)
Is it possible to have AG created across multiple domains?
No.
Is it possible to have AGs created across VMs on same physical host?
Should be, but that's not a good idea for the HA/DR replica.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2014 at 12:14 pm
ramana3327 (10/23/2014)
We are not optimizing that report. So we need to have other source
Just out of curiousity is the cost of getting all this set up hardware, licenses, dev time etc.... really less than the cost of getting the report to run more efficiently?
October 23, 2014 at 12:17 pm
Yes. You are right but they are very interested in availability group replicas ( Read only copies).
October 23, 2014 at 12:26 pm
Thanks Gila Monster.
I have just two more doubts to clarify.
I think it is possible, If there are 2 SQL Instances (Replicas) running on the same node, can they being part of the same AG?
Is there a particular quorum model to create the cluster for AG on VMware?
Thanks in advance
October 23, 2014 at 1:40 pm
ramana3327 (10/23/2014)
I think it is possible, If there are 2 SQL Instances (Replicas) running on the same node, can they being part of the same AG?
I'm glad you think it's possible....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2014 at 2:26 pm
What a TREMENDOUS budget you are throwing at reports that run too slowly!!! I am sure there are a dozen consultants at least on this site that could build you a reporting database that would be MUCH MUCH faster than you secondary will be for WAY less money than 3 enterprise sql server licenses!
You seem to be pursuing HA/DR without much knowledge of what you are getting into. That is a GUARANTEED recipe for a BAD experience!!!
Are you aware that if you read from an AG secondary you wind up modifying your PRIMARY data structures for any rows that are modified while being read on the secondary?? Think about all that that entails ... it isn't a pretty picture and can be DEVASTATING for performance in many respects.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply