{"id":26,"date":"2016-02-07T11:51:47","date_gmt":"2016-02-07T10:51:47","guid":{"rendered":"http:\/\/fomas.be\/blog\/?p=26"},"modified":"2016-02-07T11:51:47","modified_gmt":"2016-02-07T10:51:47","slug":"instances-instances-instances-or-sql-server-consolidation","status":"publish","type":"post","link":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/","title":{"rendered":"Instances instances instances or SQL server consolidation"},"content":{"rendered":"<!-- google_ad_section_start --><p>This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation.<\/p>\n<p>In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costs\u00a0our keyword should be\u00a0<strong>consolidation<\/strong>.<\/p>\n<p>When should you think about consolidation?<\/p>\n<ul>\n<li>You have more then 2 SQL Servers in your environment? Oh yes, those nasty SQL Express editions delivered with several software packages. I mean you too.<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<p>Try to cram as much of SQL databases on one server as you can. This will save you costs in several areas:<\/p>\n<ul>\n<li>Hardware:<br \/>\nFor every SQL server you install there is a hardware overhead for the operating system. Best practices mention you should always reserve 2-4GB RAM for you OS. Memory isn&#8217;t \u00a0our most expensive resources anymore. But\u00a0picture yourself what could be done with all those VCPU resources coming available in your virtualisation platform. Consolidating will also save you on disk space. All those &#8216;c-drives&#8217; suddenly aren&#8217;t needed anymore, as will the overhead on all your SQL disks.<\/li>\n<li>Licensing:<br \/>\nDid you know that once you&#8217;ve paid your Microsoft SQL license you can install up to\u00a0<strong>50<\/strong> instances on one server? Or 25 when it concerns a failover cluster.<\/li>\n<li>Maintenance (read as work hours):<br \/>\nYou will save some time on maintenance:<\/p>\n<ul>\n<li>All instances of the same version can be patched at the same time.<\/li>\n<li>Only one server to install Windows updates.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>However, before you start installing your brand new SQL server, you should think and check your current configuration. You can start by asking yourself the following questions:<\/p>\n<ul>\n<li>Do I want the same amount of SQL instances as I have now? In other words, do I want to\u00a0<strong>consolidate <\/strong>instance level as well?<\/li>\n<li>Are there issues among the different versions in use now? Please check the Microsoft documentation on this topic (<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms143694(v=sql.110).aspx\" target=\"_blank\">https:\/\/msdn.microsoft.com\/en-us\/library\/ms143694(v=sql.110).aspx<\/a>).<\/li>\n<li>Are there versions nearing end of life cycle with Microsoft. Perhaps this is the time to replace them with newer versions. This doesn&#8217;t\u00a0necessarily mean that your application needs to be replaced as well. &#8216;Compatibility level&#8217; is a setting on database level which offers you great\u00a0flexibility. In general, I would recommend phasing out all 2005 and 2008 versions.<\/li>\n<\/ul>\n<p>Some SQL instance settings you should\u00a0think about:<\/p>\n<ul>\n<li>Collation: Not all applications require the same collation. Having different database collations on one instance isn&#8217;t really an issue. However, if you use constants in queries, they will always have the collation of the instance. In short,\u00a0<strong>different collations in one instance isn&#8217;t impossible but certainly not recommended.<\/strong><\/li>\n<li>Max DOP: Defines the number of CPU cores used to calculate the result of one query. Also here there some applications (for example Sharepoint) that require MAX dop settings that deviate from the standard practices (<a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/2806535\" target=\"_blank\">https:\/\/support.microsoft.com\/en-us\/kb\/2806535<\/a>).<\/li>\n<\/ul>\n<p>After thinking all of this through you should end up with one or two SQL servers in your network, which probably is going to be a lot less then you have now. If you end up with more. Try doing the same exercise again.<\/p>\n<p>Do I want virtual or go physical? Most servers will have enough with 4 CPU cores. This is something every virtualisation platform should be able to deliver without to much issues.<br \/>\nMemory is a whole other case. There isn&#8217;t really a best practice on how much RAM you should foresee in your server. But if the RAM requirements are greater then 1\/2 of the memory in one of your virtualisation hosts you should think about expanding them or go physical.<br \/>\nTo prevent adding a single point of failure, when going physical, think about implementing Microsoft Failover Cluster (most cost effective for storage). You could run your passive node on your virtualisation environment\u00a0to cut costs.<\/p>\n<p><strong>Sources:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms143432.aspx\" target=\"_blank\">https:\/\/msdn.microsoft.com\/en-us\/library\/ms143432.aspx<\/a><\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms143694(v=sql.110).aspx\" target=\"_blank\">https:\/\/msdn.microsoft.com\/en-us\/library\/ms143694(v=sql.110).aspx<\/a><\/li>\n<li><a href=\"https:\/\/support.microsoft.com\/en-us\/lifecycle\/search?sort=PN&amp;alpha=SQL\">https:\/\/support.microsoft.com\/en-us\/lifecycle\/search?sort=PN&amp;alpha=SQL<\/a><\/li>\n<li><a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/2806535\" target=\"_blank\">https:\/\/support.microsoft.com\/en-us\/kb\/2806535<\/a><\/li>\n<\/ul>\n<p>Should you have any questions or remarks, feel free to comment on this post or contact me via <a href=\"https:\/\/www.linkedin.com\/in\/thomas-vandezande-08a05917?trk=nav_responsive_tab_profile\" target=\"_blank\">Linkedin<\/a>.<\/p>\n<!-- google_ad_section_end -->","protected":false},"excerpt":{"rendered":"<p>This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation. In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costs\u00a0our keyword should be\u00a0consolidation. When should you think about consolidation? You have more then 2 SQL Servers in&hellip;<a href=\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\">Read more <span class=\"screen-reader-text\">Instances instances instances or SQL server consolidation<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[2],"tags":[],"jetpack_publicize_connections":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Instances instances instances or SQL server consolidation<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Instances instances instances or SQL server consolidation\" \/>\n<meta property=\"og:description\" content=\"This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation. In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costs\u00a0our keyword should be\u00a0consolidation. When should you think about consolidation? You have more then 2 SQL Servers in&hellip;Read more Instances instances instances or SQL server consolidation\" \/>\n<meta property=\"og:url\" content=\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\" \/>\n<meta property=\"og:site_name\" content=\"@F0mix\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-07T10:51:47+00:00\" \/>\n<meta name=\"author\" content=\"fomas\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"fomas\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\",\"url\":\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\",\"name\":\"Instances instances instances or SQL server consolidation\",\"isPartOf\":{\"@id\":\"http:\/\/fomas.be\/blog\/#website\"},\"datePublished\":\"2016-02-07T10:51:47+00:00\",\"author\":{\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b\"},\"breadcrumb\":{\"@id\":\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/fomas.be\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Instances instances instances or SQL server consolidation\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/fomas.be\/blog\/#website\",\"url\":\"http:\/\/fomas.be\/blog\/\",\"name\":\"@F0mix\",\"description\":\"Passion for SQL and POSH\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/fomas.be\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b\",\"name\":\"fomas\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/fomas.be\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g\",\"caption\":\"fomas\"},\"url\":\"http:\/\/fomas.be\/blog\/author\/fomas\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Instances instances instances or SQL server consolidation","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/","og_locale":"en_US","og_type":"article","og_title":"Instances instances instances or SQL server consolidation","og_description":"This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation. In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costs\u00a0our keyword should be\u00a0consolidation. When should you think about consolidation? You have more then 2 SQL Servers in&hellip;Read more Instances instances instances or SQL server consolidation","og_url":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/","og_site_name":"@F0mix","article_published_time":"2016-02-07T10:51:47+00:00","author":"fomas","twitter_card":"summary_large_image","twitter_misc":{"Written by":"fomas","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/","url":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/","name":"Instances instances instances or SQL server consolidation","isPartOf":{"@id":"http:\/\/fomas.be\/blog\/#website"},"datePublished":"2016-02-07T10:51:47+00:00","author":{"@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b"},"breadcrumb":{"@id":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/fomas.be\/blog\/2016\/02\/07\/instances-instances-instances-or-sql-server-consolidation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/fomas.be\/blog\/"},{"@type":"ListItem","position":2,"name":"Instances instances instances or SQL server consolidation"}]},{"@type":"WebSite","@id":"http:\/\/fomas.be\/blog\/#website","url":"http:\/\/fomas.be\/blog\/","name":"@F0mix","description":"Passion for SQL and POSH","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/fomas.be\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/d25a64d374776dd775ddf2827081d52b","name":"fomas","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/fomas.be\/blog\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/52f3d103e5bf09db931859e978d97df9?s=96&d=mm&r=g","caption":"fomas"},"url":"http:\/\/fomas.be\/blog\/author\/fomas\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7bTMw-q","jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/26"}],"collection":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/comments?post=26"}],"version-history":[{"count":4,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/26\/revisions"}],"predecessor-version":[{"id":32,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/posts\/26\/revisions\/32"}],"wp:attachment":[{"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/media?parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/categories?post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/fomas.be\/blog\/wp-json\/wp\/v2\/tags?post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}