{"id":887,"date":"2018-10-09T10:30:41","date_gmt":"2018-10-09T15:30:41","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=887"},"modified":"2018-10-09T10:30:41","modified_gmt":"2018-10-09T15:30:41","slug":"using-dbatools-powershell-module-to-configure-sql-server","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/","title":{"rendered":"Using dbatools PowerShell Module To Configure SQL Server"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-871\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/09\/dbatools-logo-1.png\" alt=\"\" width=\"223\" height=\"64\" \/>The more I use\u00a0the\u00a0<a href=\"https:\/\/dbatools.io\/\" target=\"_blank\" rel=\"noopener\">dbatools PowerShell module<\/a>, the more I like it.\u00a0 It really is a solid set of scripts that allow you to easily manage your SQL Servers via PowerShell.\u00a0 If you are not familiar with dbatools, I recommend you head over to my previous post,\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/getting-started-with-the-dbatools-powershell-module\/\">Getting Started With The dbatools PowerShell Module<\/a>, for a quick intro on getting it set up.<\/p>\n<p>At work, one of the things my team has been using dbatools on is a standard SQL Server configuration script that we will use on all new SQL Server installations.\u00a0 As part of this script, we wanted to use PowerShell to set the SQL Server settings that we would typically use <strong>sp_configure<\/strong>\u00a0for, which is very easily done by using the\u00a0<a href=\"https:\/\/docs.dbatools.io\/#Set-DbaSpConfigure\" target=\"_blank\" rel=\"noopener\">Set-DbaSpConfigure<\/a> command (you would use\u00a0<a href=\"https:\/\/docs.dbatools.io\/#Get-DbaSpConfigure\" target=\"_blank\" rel=\"noopener\">Get-DbaSpConfigure<\/a> to view the configurations).<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">#Get the value for XPCmdShellEnabled\r\nGet-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled\r\n\r\n#Set the value for XPCmdShellEnabled\r\nSet-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled -Value 1<\/pre>\n<p>Pretty straight forward, right?\u00a0 The parameter &#8220;-SqlInstance&#8221; is the name of the SQL Server you want to modify, &#8220;-Name&#8221; is the name of the configuration you want to view or modify, and &#8220;-Value&#8221; is the value you want to set the configuration to.\u00a0 In the second example above, we would connect to &#8220;localhost&#8221; and set\u00a0XPCmdShellEnabled = 1.<\/p>\n<p>Now, I will say that one area where I find the dbatools documentation lacking is around the list of &#8220;-Name&#8221; configurations that you can use here.\u00a0 I finally found a list of properties in the <a href=\"https:\/\/docs.microsoft.com\/en-us\/dotnet\/api\/microsoft.sqlserver.management.smo.configuration\" target=\"_blank\" rel=\"noopener\">Configuration Class<\/a>\u00a0documentation, and these seem to be the server properties that you can configure with <strong>Set-DbaSpConfigure<\/strong>.\u00a0 (I&#8217;m assuming.\u00a0 Every one I have tried so far has worked, and I haven&#8217;t seen any documentation to lead me to believe otherwise)<\/p>\n<h3>Configuring Individual SQL Servers<\/h3>\n<p>Here is a simple example of how we use\u00a0<strong>Set-DbaSpConfigure<\/strong> instead of\u00a0<strong>sp_configure<\/strong>\u00a0to configure a single SQL Server.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SQLInstance = \"localhost\" #Use Your SQL Server Name\r\n\r\n##################################\r\n## Set backup compression as default.\r\n########################################\r\nwrite-host \"Set backup compression\"\r\nSet-DbaSpConfigure -SqlInstance $SQLInstance -Name 'DefaultBackupCompression' -Value 1\r\n\r\n##################################\r\n## Enable remote dedicated admin connections.\r\n########################################\r\nwrite-host \"Enable remote dedicated admin connections\"\r\nSet-DbaSpConfigure -SqlInstance $SQLInstance -Name 'RemoteDacConnectionsEnabled' -Value 1\r\n\r\n##################################\r\n## Set Cost Threshold For Parallelism.\r\n########################################\r\nwrite-host \"Set Cost Threshold For Parallelism\"\r\nSet-DbaSpConfigure -SqlInstance $SQLInstance -Name 'CostThresholdForParallelism' -Value 25\r\n\r\n##################################\r\n## Set Optimize For Ad-hoc Workloads.\r\n########################################\r\nwrite-host \"Set Optimize For Ad-hoc Workloads\"\r\nSet-DbaSpConfigure -SqlInstance $SQLInstance -Name 'OptimizeAdhocWorkloads' -Value 1\r\n\r\n<\/pre>\n<p>This really isn&#8217;t much different than running a T-SQL script in Management Studio.\u00a0 I would typically run the above script in <a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/scripting\/core-powershell\/ise\/introducing-the-windows-powershell-ise?view=powershell-6\" target=\"_blank\" rel=\"noopener\">PowerShell ISE<\/a>.\u00a0 One of the advantages of this is that you can run it on your local machine, you don&#8217;t have to remote into the server or connect to SSMS, and all you have to do is change the\u00a0SQLInstance variable to run it against a different server.\u00a0 So, you could run it against Server1, then change the value of\u00a0SQLInstance to Server2 and run it against it.\u00a0 But, where the real power comes in is when you realize you can run this on multiple servers simultaneously.<\/p>\n<h3>Configuring Multiple SQL Servers<\/h3>\n<p>To run our configuration script against multiple servers, we just need to make a few slight modifications.\u00a0 We start out by creating a server list instead of specifying a single server (duh!).\u00a0 The next part may seem a little odd if you&#8217;re not familiar with how PowerShell works.\u00a0 First, we connect to each of the servers in our list using\u00a0<strong>Get-DbaSpConfigure<\/strong> and retrieve the values we want to change.\u00a0 Then we pass\u00a0(also known as &#8220;piping&#8221; in PowerShell) that server name and configuration info into\u00a0<strong>Set-DbaSpConfigure<\/strong>, which will connect to the server and set the configuration to the value specified.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$ServerList = (\"server1\", \"server2\")\r\n\r\n\r\n##################################\r\n## Set backup compression as default.\r\n########################################\r\nwrite-host \"Set backup compression\"\r\nGet-DbaSpConfigure -SqlInstance $ServerList -Name 'DefaultBackupCompression' | Set-DbaSpConfigure -Value 1\r\n \r\n##################################\r\n## Enable remote dedicated admin connections.\r\n########################################\r\nwrite-host \"Enable remote dedicated admin connections\"\r\nGet-DbaSpConfigure -SqlInstance $ServerList -Name 'RemoteDacConnectionsEnabled' | Set-DbaSpConfigure -Value 1\r\n<\/pre>\n<p>So, in the above example dbatools will get the\u00a0DefaultBackupCompression from both of the servers specified, then call\u00a0<strong>Set-DbaSpConfigure<\/strong> to change those values to &#8220;1&#8221;.\u00a0 It will then do the same for the\u00a0RemoteDacConnectionsEnabled setting.<\/p>\n<p>I initially tried to use the server list with\u00a0<strong>Set-DbaSpConfigure<\/strong> directly, but that didn&#8217;t work out like I expected, it was running the configuration update multiple times on each server instead of just once on each server.\u00a0 Looking through the documentation, they give an example of using\u00a0<strong>Get-DbaSpConfigure<\/strong> and then piping the values into\u00a0<strong>Set-DbaSpConfigure<\/strong>, so that&#8217;s what I did here and it seems to work pretty well.<\/p>\n<p>Admittedly this is a very basic example, but I will be building off of it in\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/tag\/dbatools\/\">more posts<\/a>\u00a0with real world examples of how I am using dbatools to enhance my day to day SQL Server administration.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The more I use\u00a0the\u00a0dbatools PowerShell module, the more I like it.\u00a0 It really is a solid set of scripts that allow you to easily manage your SQL Servers via PowerShell.\u00a0 &#8230;<\/p>\n","protected":false},"author":2,"featured_media":841,"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","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[20],"tags":[21,66,17],"class_list":["post-887","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell","tag-automation","tag-dbatools","tag-server-administration"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets<\/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:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"The more I use\u00a0the\u00a0dbatools PowerShell module, the more I like it.\u00a0 It really is a solid set of scripts that allow you to easily manage your SQL Servers via PowerShell.\u00a0 ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-09T15:30:41+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png\" \/>\n\t<meta property=\"og:image:width\" content=\"670\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"Using dbatools PowerShell Module To Configure SQL Server\",\"datePublished\":\"2018-10-09T15:30:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/\"},\"wordCount\":650,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/07\\\/powershell4.png\",\"keywords\":[\"Automation\",\"dbatools\",\"Server Administration\"],\"articleSection\":[\"PowerShell\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/\",\"name\":\"Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/07\\\/powershell4.png\",\"datePublished\":\"2018-10-09T15:30:41+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/07\\\/powershell4.png\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2018\\\/07\\\/powershell4.png\",\"width\":670,\"height\":250},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/using-dbatools-powershell-module-to-configure-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using dbatools PowerShell Module To Configure SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets","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:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets","og_description":"The more I use\u00a0the\u00a0dbatools PowerShell module, the more I like it.\u00a0 It really is a solid set of scripts that allow you to easily manage your SQL Servers via PowerShell.\u00a0 ...","og_url":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/","og_site_name":"SQL Nuggets","article_published_time":"2018-10-09T15:30:41+00:00","og_image":[{"width":670,"height":250,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","type":"image\/png"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"Using dbatools PowerShell Module To Configure SQL Server","datePublished":"2018-10-09T15:30:41+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/"},"wordCount":650,"commentCount":0,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","keywords":["Automation","dbatools","Server Administration"],"articleSection":["PowerShell"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/","url":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/","name":"Using dbatools PowerShell Module To Configure SQL Server - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","datePublished":"2018-10-09T15:30:41+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","width":670,"height":250},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/using-dbatools-powershell-module-to-configure-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"Using dbatools PowerShell Module To Configure SQL Server"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-ej","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":870,"url":"https:\/\/sqlnuggets.com\/getting-started-with-the-dbatools-powershell-module\/","url_meta":{"origin":887,"position":0},"title":"Getting Started With The dbatools PowerShell Module","author":"Eric Cobb","date":"September 20, 2018","format":false,"excerpt":"If you are looking for a super easy way to start using PowerShell to manage your SQL Servers, then the\u00a0dbatools PowerShell module is for you!\u00a0 For those that aren't familiar, dbatools is a\u00a0free\u00a0PowerShell module with over 400 SQL Server best practice, administration, development and migration commands included. I have to\u2026","rel":"","context":"In &quot;PowerShell&quot;","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":897,"url":"https:\/\/sqlnuggets.com\/using-dbatools-powershell-module-to-set-maxdop-and-max-memory\/","url_meta":{"origin":887,"position":1},"title":"Using dbatools PowerShell Module To Set MAXDOP And Max Memory","author":"Eric Cobb","date":"November 27, 2018","format":false,"excerpt":"As I continue my blog series on using\u00a0the\u00a0dbatools PowerShell module, today I want to discuss using it to set MAXDOP and Max Memory on your SQL Server.\u00a0 This builds directly off of my previous post on\u00a0Using dbatools PowerShell Module To Configure SQL Server, so if you haven't seen that one\u2026","rel":"","context":"In &quot;PowerShell&quot;","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1513,"url":"https:\/\/sqlnuggets.com\/upcoming-geek-sync-practical-powershell-for-the-busy-dba\/","url_meta":{"origin":887,"position":2},"title":"Upcoming Geek Sync: Practical PowerShell For The Busy DBA","author":"Eric Cobb","date":"November 10, 2020","format":false,"excerpt":"Be sure to sign up for my upcoming IDERA Geek Sync, Practical PowerShell For The Busy DBA on November 19th, 2020 at 11:00 AM CT. I will be focusing on using the dbatools PowerShell module, and show just how great it is at handling many of the tasks that SQL\u2026","rel":"","context":"In &quot;PowerShell&quot;","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/11\/EmZ1VIOWMAMIy-r.jpeg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":1049,"url":"https:\/\/sqlnuggets.com\/patching-multiple-sql-servers-with-powershell-and-dbatools\/","url_meta":{"origin":887,"position":3},"title":"Patching Multiple SQL Servers With Powershell and dbatools","author":"Eric Cobb","date":"July 18, 2019","format":false,"excerpt":"Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you're going to be spending a considerable chunk\u2026","rel":"","context":"In &quot;PowerShell&quot;","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1158,"url":"https:\/\/sqlnuggets.com\/backing-up-databases-with-the-dbatools-powershell-module\/","url_meta":{"origin":887,"position":4},"title":"Backing Up Databases With The dbatools PowerShell Module","author":"Eric Cobb","date":"November 20, 2019","format":false,"excerpt":"We do a lot of database backups and restores at work, outside of our regular backup routines. A lot! I've actually done as many as 40 in one day. Our \"enterprise\" backup system is too cumbersome to try to use like this, so we needed something that would allow us\u2026","rel":"","context":"In &quot;Backups&quot;","block_context":{"text":"Backups","link":"https:\/\/sqlnuggets.com\/category\/backups\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/07\/powershell4.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1534,"url":"https:\/\/sqlnuggets.com\/setting-the-database-owner-compatibility-level-and-recovery-model-with-powershell\/","url_meta":{"origin":887,"position":5},"title":"Setting The Database Owner, Compatibility Level, And Recovery Model With PowerShell","author":"Eric Cobb","date":"December 30, 2020","format":false,"excerpt":"One of the great things about dbatool is that it allows you to run commands against multiple things. Sometimes you may need to run the same command on every server, or every database on a server, or every database on every server (not recommended). Today we're going to look at\u2026","rel":"","context":"In &quot;Configurations&quot;","block_context":{"text":"Configurations","link":"https:\/\/sqlnuggets.com\/category\/configurations\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1400%2C800&ssl=1 4x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/887","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=887"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/887\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/841"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}