locked
Your experiences hosting all your SQL Servers virtually RRS feed

  • Question

  • Hello everyone

    My organisation has gone virtual for 3 years now-file servers/sql everthing. I just joined so have no choice but to have all the databases I support hosted on VMs. Over these 3 years, we have about 20+ SQL instances hosted on each VM so 20+ VMs you can just imagine the management of these servers,not to mention licensing! Well we are reviewing our licensing strategy to cut costs and so we'll be starting a project to consolidate all these sql instances. 

    Reading online, I have come up with a plan to group the databases according to these criteria:

    1) Business critical-impacts entire organisation;reads/writes random

    2) Decision Support Systems-Reporting: Read only access

    3) BI/ETL-Read/write intensive at certain times of day

    4) Non critical databases,department-level : Only dept impacted if downtime.

    • Disk drive partitions for each: Data files | Log files | Temp DB | Backup. Please note we have SAN shared storage (PS. I dont know much about SAN but I'm reading up on it to understand its perf impact on the database)
    • Max RAM+CPU: to be discussed with storage admins

    Can you please share your experiences on how you have deployed your SQL servers to your virtual environment- design considerations, problems faced, things to improve on etc?

    Thank you all in advance


    Esperanza



    Saturday, September 24, 2016 4:50 AM

Answers

  • https://www.brentozar.com/archive/2014/08/book-review-virtualizing-sql-server-vmware/

    https://www.brentozar.com/sql-server-training-videos/virtualization-and-san-basics-for-dbas/

    https://www.brentozar.com/archive/2011/05/keys-deploying-sql-server-on-vmware/

    PS. What I have seen after we moved to VM, that a performance on the physical box was much better :-)))))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Esperanza7190 Wednesday, September 28, 2016 10:24 PM
    Sunday, September 25, 2016 11:04 AM
  • You have group these well.

    If you trust your admins of the VMs, then they should be able to give the SQL Server instances the resources needed to support all your options.

    They can split the virtual disks on the SAN to specific types of IOPS to support all these types.


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    • Marked as answer by Esperanza7190 Wednesday, September 28, 2016 10:24 PM
    Monday, September 26, 2016 1:47 PM

All replies

  • https://www.brentozar.com/archive/2014/08/book-review-virtualizing-sql-server-vmware/

    https://www.brentozar.com/sql-server-training-videos/virtualization-and-san-basics-for-dbas/

    https://www.brentozar.com/archive/2011/05/keys-deploying-sql-server-on-vmware/

    PS. What I have seen after we moved to VM, that a performance on the physical box was much better :-)))))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Esperanza7190 Wednesday, September 28, 2016 10:24 PM
    Sunday, September 25, 2016 11:04 AM
  • You have group these well.

    If you trust your admins of the VMs, then they should be able to give the SQL Server instances the resources needed to support all your options.

    They can split the virtual disks on the SAN to specific types of IOPS to support all these types.


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    • Marked as answer by Esperanza7190 Wednesday, September 28, 2016 10:24 PM
    Monday, September 26, 2016 1:47 PM
  • https://www.brentozar.com/archive/2014/08/book-review-virtualizing-sql-server-vmware/

    https://www.brentozar.com/sql-server-training-videos/virtualization-and-san-basics-for-dbas/

    https://www.brentozar.com/archive/2011/05/keys-deploying-sql-server-on-vmware/

    PS. What I have seen after we moved to VM, that a performance on the physical box was much better :-)))))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Hi Uri

    Thanks alot for the links, I go will thru' them. 

    As for performance, it was a business decision to go virtual so I'll just have to explain the perf impact if we do get there ;-)


    Esperanza



    Wednesday, September 28, 2016 10:26 PM
  • You have group these well.

    If you trust your admins of the VMs, then they should be able to give the SQL Server instances the resources needed to support all your options.

    They can split the virtual disks on the SAN to specific types of IOPS to support all these types.


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Hi Thomas

    Thanks for the info on the SAN config, I dont know anything about this so I'm trying to read up too to at least understand what our VM admins are giving us


    Esperanza


    • Edited by Esperanza7190 Wednesday, September 28, 2016 10:29 PM
    • Proposed as answer by vhrioss Wednesday, September 28, 2016 10:32 PM
    • Unproposed as answer by vhrioss Wednesday, September 28, 2016 10:33 PM
    Wednesday, September 28, 2016 10:28 PM
  • Hi Esperanza,

    It's important in VM and SQL Server's put attention on CPU's. For cutting off costs,  v-cpu's must be from  the same physical cpu for each VM. This strategy help you to sabe extra money. As you comment , multiple drive partitions help SQL Server to distribuite IO and in a SAN works the same. 

    Name all your SQL Server mail  alerts with a name that represent your environment and business group for example

    Info BCI (business critical impact) sales   SQL Alert System....

    this help you to focus on the server faster because of the subject mail.

    Victor

    Wednesday, September 28, 2016 11:10 PM