{"id":4618,"date":"2015-05-18T09:58:27","date_gmt":"2015-05-18T07:58:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/"},"modified":"2015-05-18T09:58:27","modified_gmt":"2015-05-18T07:58:27","slug":"get-sql-server-services-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/","title":{"rendered":"Get SQL Server services with PowerShell"},"content":{"rendered":"

SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete.
\nBut as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell!<\/p>\n

This blog introduces a first step towards an automation process of SQL Server administration. I will retrieve all SQL Server services related to a specific instance name.
\nThe process will always be similar by using the SMO WMI server PowerShell object.<\/p>\n

Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!<\/p>\n

SQL Engine<\/h3>\n

To retrieve the SQL Engine service for a specific instance name:<\/p>\n

\"Get-SQLEngine_function.png\"<\/a><\/p>\n

SQL Agent<\/h3>\n

To retrieve the SQL Agent service for a specific instance name:<\/p>\n

\"Get-SQLAgent_function.png\"<\/a><\/p>\n

SQL Full-text Filter<\/h3>\n

To retrieve the SQL Full-text Filter service for a specific instance name:<\/p>\n

\"Get-SQLFullTextFilter_function.png\"<\/a><\/p>\n

SQL Browser<\/h3>\n

To retrieve the SQL Browser service:<\/p>\n

\"Get-SQLBrowser_function.png\"<\/a><\/p>\n

SQL Analysis<\/h3>\n

To retrieve the SQL Analysis service for a specific instance name:<\/p>\n

\"Get-SQLAnalysis_functionpng.png\"<\/a><\/p>\n

SQL Reporting<\/h3>\n

To retrieve the SQL Reporting service for a specific instance name:<\/p>\n

\"Get-SQLReporting_function.png\"<\/a><\/p>\n

SQL Integration<\/h3>\n

To retrieve the SQL Integration service:<\/p>\n

\"Get-SQLIntegration_function.png\"<\/a><\/p>\n

Service Object<\/h3>\n

Each function returns an object with the following properties and methods:<\/p>\n

\"service_properties_and_methods.png\"<\/a><\/p>\n

You are able to start, restart or stop your service. But you can also retrieve specific information such as the Service Account, the Start Mode or the Service Account.<\/p>\n

Next Step<\/h3>\n

If you do not want to proceed just for a specific instance, but for all instances, you can list all instance names in that way:<\/p>\n

\"Get-SQLInstances_function.png\"<\/a><\/p>\n

Then, with your list of instance names, you loop by calling each function. Do not forget to test if the service returned exists (by testing if it is null).<\/p>\n

To finish my article, all these functions are part of our Database Management Kit<\/a> (DMK) developed by our team. We use it to access faster to common and standards information, but also to automate processes.<\/p>\n

For example, the DMK is able (in just one command!) to make a security audit of your SQL Server environment, by following the best practices from Microsoft and from our experts. A report is generated at the end of the audit to list all the security points to review.<\/p>\n","protected":false},"excerpt":{"rendered":"

SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete. But as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell! This blog introduces a first […]<\/p>\n","protected":false},"author":14,"featured_media":4619,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[559,272,51],"type_dbi":[],"class_list":["post-4618","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-dmk","tag-powershell","tag-sql-server"],"acf":[],"yoast_head":"\nGet SQL Server services with PowerShell - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get SQL Server services with PowerShell\" \/>\n<meta property=\"og:description\" content=\"SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete. But as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell! This blog introduces a first […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T07:58:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png\" \/>\n\t<meta property=\"og:image:width\" content=\"742\" \/>\n\t<meta property=\"og:image:height\" content=\"276\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Get SQL Server services with PowerShell\",\"datePublished\":\"2015-05-18T07:58:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\"},\"wordCount\":390,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png\",\"keywords\":[\"DMK\",\"PowerShell\",\"SQL Server\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\",\"name\":\"Get SQL Server services with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png\",\"datePublished\":\"2015-05-18T07:58:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png\",\"width\":742,\"height\":276},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get SQL Server services with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Get SQL Server services with PowerShell - dbi Blog","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":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Get SQL Server services with PowerShell","og_description":"SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete. But as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell! This blog introduces a first […]","og_url":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2015-05-18T07:58:27+00:00","og_image":[{"width":742,"height":276,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png","type":"image\/png"}],"author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Get SQL Server services with PowerShell","datePublished":"2015-05-18T07:58:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/"},"wordCount":390,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png","keywords":["DMK","PowerShell","SQL Server"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/","name":"Get SQL Server services with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png","datePublished":"2015-05-18T07:58:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLEngine_function.png","width":742,"height":276},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-services-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Get SQL Server services with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4618"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4618\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/4619"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4618"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}