Best Practices

  • 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.

  • 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

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

  • 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
  • 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!

  • 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

  • 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
  • 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

  • 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?

  • 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
  • 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?

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

  • 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

  • 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
  • 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