Best Practices

Question

Best Practices

ramana3327

SSCoach

Points: 19700
More actions
October 20, 2014 at 5:17 pm

#300369

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.

Viewing 15 posts - 1 through 15 (of 29 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

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" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLFunkateer SSChasing Mays Points: 629 More actions · Answer 4

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!

ramana3327 SSCoach Points: 19700 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19700 More actions · Answer 7

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

ramana3327 SSCoach Points: 19700 More actions · Answer 8

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 10

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?

ramana3327 SSCoach Points: 19700 More actions · Answer 11

Yes. You are right but they are very interested in availability group replicas ( Read only copies).

ramana3327 SSCoach Points: 19700 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

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