Powershell Script to Get SQL Instances Information Remotely

Retrieves SQL server information from a local or remote servers.
Also Pulls all instances from a SQL server and detects if in a cluster or not.
Local or remote systems to query for SQL information.
If specified, try to pull and correlate WMI information for SQL in matching up the service info to registry info.

Follow below process to execute the PS Script  :

Open Powershell ISE with Run as Administrator and copy the below script in powershell ISE and hit execute . After executing copy below command and paste it in powershell by changing server names :

Example :

Get-SQLInstance -Computername DC1   – for Single server information

Get-SQLInstance -Computername Server1, Server2 -WMI –  For multiple servers Information

PS script below :

Function Get-SQLInstance {
<#
. SYNOPSIS
Gets SQL server data from a local or distant server.

.DESCRIPTION
Gets SQL server data from a local or distant server. Pulls all instances from a SQL server and determines whether they are in a cluster.

.PARAMETER ComputerName
To query for SQL information on local or distant systems.


.FUNCTIONALITY
Computers

.EXAMPLE
Get-SQLInstance -Computername Node1

Caption : SQL Server 2008

Description
-----------
Retrieves the SQL information from Node1

.EXAMPLE
#Get SQL instances on servers 1 and 2 from WMI
Get-SQLInstance -Computername Node1, Node2 -WMI

#>
[cmdletbinding()]
Param (
[parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
[Alias('__Server','DNSHostName','IPAddress')]
[string[]]$ComputerName = $env:COMPUTERNAME,

[switch]$WMI
)
Begin {
$baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server",
"SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server"
}
Process {
ForEach ($Computer in $Computername) {

$Computer = $computer -replace '(.*?)\..+','$1'
Write-Verbose ("Checking {0}" -f $Computer)

#Boe's code is this. He outputs it directly, and I'm assigning it to allInstances later to correlate with WMI.
$allInstances = foreach($baseKey in $baseKeys){
Try {

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer)
$regKey= $reg.OpenSubKey($baseKey)

If ($regKey.GetSubKeyNames() -contains "Instance Names") {
$regKey= $reg.OpenSubKey("$baseKey\\Instance Names\\SQL" )
$instances = @($regkey.GetValueNames())
}
ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
$isCluster = $False
$instances = $regKey.GetValue('InstalledInstances')
}
ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
$isCluster = $False
$instances = $regKey.GetValue('InstalledInstances')
}
Else {
Continue
}

If ($instances.count -gt 0) {
ForEach ($instance in $instances) {
$nodes = New-Object System.Collections.Arraylist
$clusterName = $Null
$isCluster = $False
$instanceValue = $regKey.GetValue($instance)
$instanceReg = $reg.OpenSubKey("$baseKey\\$instanceValue")
If ($instanceReg.GetSubKeyNames() -contains "Cluster") {
$isCluster = $True
$instanceRegCluster = $instanceReg.OpenSubKey('Cluster')
$clusterName = $instanceRegCluster.GetValue('ClusterName')
$clusterReg = $reg.OpenSubKey("Cluster\\Nodes")
$clusterReg.GetSubKeyNames() | ForEach {
$null = $nodes.Add($clusterReg.OpenSubKey($_).GetValue('NodeName'))
}
}
$instanceRegSetup = $instanceReg.OpenSubKey("Setup")
Try {
$edition = $instanceRegSetup.GetValue('Edition')
} Catch {
$edition = $Null
}
Try {
$SQLBinRoot = $instanceRegSetup.GetValue('SQLBinRoot')
} Catch {
$SQLBinRoot = $Null
}
Try {
$ErrorActionPreference = 'Stop'
#Get from filename to determine version
$servicesReg = $reg.OpenSubKey("SYSTEM\\CurrentControlSet\\Services")
$serviceKey = $servicesReg.GetSubKeyNames() | Where {
$_ -match "$instance"
} | Select -First 1
$service = $servicesReg.OpenSubKey($serviceKey).GetValue('ImagePath')
$file = $service -replace '^.*(\w:\\.*\\sqlservr.exe).*','$1'
$version = (Get-Item ("\\$Computer\$($file -replace ":","$")")).VersionInfo.ProductVersion
} Catch {
#Use potentially less accurate version from registry
$Version = $instanceRegSetup.GetValue('Version')
} Finally {
$ErrorActionPreference = 'Continue'
}
New-Object PSObject -Property @{
Computername = $Computer
SQLInstance = $instance
SQLBinRoot = $SQLBinRoot
Edition = $edition
Version = $version
Caption = {Switch -Regex ($version) {
"^12" {'SQL Server 2014';Break}
"^11" {'SQL Server 2012';Break}
"^10\.5" {'SQL Server 2008 R2';Break}
"^10" {'SQL Server 2008';Break}
"^9" {'SQL Server 2005';Break}
"^8" {'SQL Server 2000';Break}
"^7" {'SQL Server 7.0';Break}
Default {'Unknown'}
}}.InvokeReturnAsIs()
isCluster = $isCluster
isClusterNode = ($nodes -contains $Computer)
ClusterName = $clusterName
ClusterNodes = ($nodes -ne $Computer)
FullName = {
If ($Instance -eq 'MSSQLSERVER') {
$Computer
} Else {
"$($Computer)\$($instance)"
}
}.InvokeReturnAsIs()
} | Select Computername, SQLInstance, SQLBinRoot, Edition, Version, Caption, isCluster, isClusterNode, ClusterName, ClusterNodes, FullName
}
}
} Catch {
Write-Warning ("{0}: {1}" -f $Computer,$_.Exception.Message)
}
}

#Get wmi information and correlate it if the wmi param was supplied!
if($WMI){
Try{

#Get the WMI data we're interested in.
$sqlServices = $null
$sqlServices = @(
Get-WmiObject -ComputerName $computer -query "select DisplayName, Name, PathName, StartName, StartMode, State from win32_service where Name LIKE 'MSSQL%'" -ErrorAction stop |
#This  matches MSSQLServer and MSSQL$*
Where-Object {$_.Name -match "^MSSQL(Server$|\$)"} |
select DisplayName, StartName, StartMode, State, PathName
)

#Correlate if we got WMI data and it wasn't empty!
if($sqlServices){

Write-Verbose "WMI Service info:`n$($sqlServices | Format-Table -AutoSize -Property * | out-string)"
foreach($inst in $allInstances){
$matchingService = $sqlServices |
Where {$_.pathname -like "$( $inst.SQLBinRoot )*" -or $_.pathname -like "`"$( $inst.SQLBinRoot )*"} |
select -First 1

$inst | Select -property Computername,
SQLInstance,
SQLBinRoot,
Edition,
Version,
Caption,
isCluster,
isClusterNode,
ClusterName,
ClusterNodes,
FullName,
@{ label = "ServiceName"; expression = {
if($matchingService){
$matchingService.DisplayName
}
else{"No WMI Match"}
}},
@{ label = "ServiceState"; expression = {
if($matchingService){
$matchingService.State
}
else{"No WMI Match"}
}},
@{ label = "ServiceAccount"; expression = {
if($matchingService){
$matchingService.startname
}
else{"No WMI Match"}
}},
@{ label = "ServiceStartMode"; expression = {
if($matchingService){
$matchingService.startmode
}
else{"No WMI Match"}
}}
}
}
}
Catch {
Write-Warning "WMI information could not be retrieved for '$computer':`n$_"
$allInstances
}

}
else {
$allInstances
}
}
}
}

SQL Server Integration Service (SSIS) 2016 unable to connect from SQL Server Management Studio (SSMS) 17.X.

Error:

The following problem occurred while connecting to the Integration Services service on the computer “”: “The given service does not exist as an installed service.”

When using the current version of the SQL Server tools to connect to a SQL Server 2005 Integration Services service, you may receive this error. Instead, add directories to the service configuration file to allow the SQL Server 2005 instance’s local Integration Services service to manage packages.

Check & Findings:

1) SSIS services is running

2) In comparison to SSIS, I’m utilising a newer SSMS version.

Solution:

When you try to connect SSIS services from a later version of SSMS, you’ll see this error notice.

In my scenario, I’m using SSMS version 17 to connect to SQL Server 2016 SSIS. I downloaded and installed version 16 of SSMS. From SSMS version 16, SSIS connectivity is operating fine.

Download links for various SSMS versions (16.X & 17.X)– https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

After you’ve clicked the above link, look for ” SSMS 16.5.3 ” and select ” English (United States) ” as your preferred language. It will begin downloading; once the download is complete, use SSMS 16.5.3 to instal and connect the SQL 2016 Integration Services.

Please read the following for more information:

The Integration Services service is installed when you install the SQL Server Integration Services component. The Integration Services service is launched by default, and its startup type is set to automatic. To utilize the service to manage stored and running Integration Services packages, you must additionally install SQL Server Management Studio.

.

Note

You must use the version of SQL Server Management Studio (SSMS) that corresponds to the version of SQL Server on which the legacy Integration Services Service is operating to connect directly to an instance of the legacy Integration Services Service. To connect to the historical Integration Services Service running on a SQL Server 2016 instance, for example, you must use the SSMS version issued for SQL Server 2016.. Download SQL Server Management Studio (SSMS).

You cannot enter the name of a server running an earlier version of the Integration Services service in the SSMS Connect to Server dialogue box. To handle packages stored on a remote server, however, you do not need to connect to that distant server’s instance of the Integration Services service. Instead, modify the Integration Services service’s configuration file so that SQL Server Management Studio shows the packages stored on the remote server.

On each machine, only one instance of the Integration Services service can be installed. The service is not tied to a single Database Engine instance. You use the service to connect to it.

The SQL Server Configuration Manager or Services snap-ins in the Microsoft Management Console (MMC) can be used to manage the Integration Services service. You must first start the service before you can handle packages in SQL Server Management Studio.

The Integration Services service is set up by default to handle packages in the msdb database of the Database Engine instance that was installed with Integration Services. The Integration Services service is set to manage packages in the msdb database of the local, default instance of the Database Engine if an instance of the Database Engine is not installed at the same time. You must edit the service’s configuration file to handle packages stored in a named or remote instance of the Database Engine, or in many instances of the Database Engine.

When the Integration Services service is halted by default, all running packages are terminated. The Integration Services service, on the other hand, does not wait for packages to terminate, and some packages may continue to run after the Integration Services service has been terminated.

You can still run packages using the SQL Server Import and Export Wizard, the SSIS Designer, the Execute Package Utility, and the dtexec command prompt utility if the Integration Services service is stopped (dtexec.exe). However, you are unable to keep track of the running packages.

The NETWORK SERVICE account is used by default to run the Integration Services service.

The Windows event log is written to by the Integration Services service. SQL Server Management Studio allows you to view service events. The Windows Event Viewer can also be used to view service events.

Scripts to get Replication Configuration Information

Using Replication Scripts to Get Replication Configuration Information:

The scripts may be run from a variety of databases to pull this data, and if you have a lot of servers for replication, as we do, you can use the Multi Server Query feature to query numerous servers at once. Run the same SQL Server query on multiple servers at the same time using Central Management Servers.

SQL 2005, 2008, and 2012 are all supported by these scripts.

A Summary of the Situation

There are three scripts to pick from:

This script should be performed on the Distribution database and delivers completed setup replication information.
This script queries the Publisher database and produces a list of newly produced publications.
This script retrieves the article(s) that are currently being replicated to the subscriber database. I also use this method to locate any orphaned subscribers.


Script to be executed on the Distribution database.

This script returns information about the completed replication setup. Unless an orphan article exists, this will return a complete set of replication information. To demonstrate how straightforward it is to pull in extra configuration data, I also included the name of the distribution agent task. This should be turned into a stored process and then into a Reporting Services report so that anybody can see it.

USE Distribution
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.subscriber_id = s.subscriber_id
ORDER BY 1,2,3

Script to be executed on the Publisher database

The publications that have been created are displayed by this script. This command will walk through all of the published databases and provide information whether replication is enabled in the database. I sometimes just want to see the publication name and subscriber server names (no articles) to see which servers are being used for replication, but other times I want all of the information, thus I created a variable called @Detail, which returns data with the article list when set to ‘Y’. If any other option is specified, only the publisherDB, publisherName, and SubscriberServerName will be returned.

-- Run from Publisher Database
-- Get information for all databases
DECLARE @Detail CHAR(1)
SET @Detail = 'Y'
CREATE TABLE #tmp_replcationInfo (
PublisherDB VARCHAR(128),
PublisherName VARCHAR(128),
TableName VARCHAR(128),
SubscriberServerName VARCHAR(128),
)
EXEC sp_msforeachdb
'use ?;
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
insert into #tmp_replcationInfo
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
'
IF @Detail = 'Y'
SELECT * FROM #tmp_replcationInfo
ELSE
SELECT DISTINCT
PublisherDB
,PublisherName
,SubscriberServerName
FROM #tmp_replcationInfo
DROP TABLE #tmp_replcationInfo

Script to run on the database of Subscribers

This script displays which article(s) are currently being replicated to the subscriber database. This is also how I locate orphaned subscribers. Because there isn’t much data to pull, this is an easy task.

— Use the Subscriber Database as a starting point.

Use Subscriber Database
GO
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

Issues when restoring or attaching a database from higher to Lower SQL Server Versions

Why can’t a database from a higher version of SQL Server be restored to a lower version?

Introduction

It will fail if you attempt to restore a database from a database backup created on a SQL Server 2008 instance to a SQL Server 2005 or lower version. We’ll look at why a SQL Server Database from a higher version can’t be restored onto a lower version of SQL Server in this article.

When restoring a SQL Server 2008 database backup on SQL Server 2005 or earlier versions, an error message appears.

The media family ‘Drive:\BackupFolder\DatabaseName.BAK’ on device, the database name ‘DatabaseName.BAK’ was wrongly generated. This media family cannot be processed by SQL Server.
RESTORE DATABASE is quitting unexpectedly.

The internal version number of SQL Server 2008 differs from that of SQL Server 2005 or lower versions, which is the cause of the issue. The restore process fails because the internal version number of SQL Server 2008 is higher than that of a SQL Server 2005 database.

Important Note: -By Microsoft’s design, you will not be able to restore a database backup from a higher version of SQL Server to a lower version of SQL Server. The database backup and restore procedure does not allow you to downgrade a database.

Why can’t a SQL Server database from a higher version be attached to a SQL Server database from a lower version?

If you try to attach a SQL Server database that was built on a SQL Server 2008 instance to a SQL Server 2005 or lower edition, the process will fail with the error below.

When joining a SQL Server 2008 database to a SQL Server 2005 or lower edition, an error message appears.

The database ‘DatabaseName’ was unable to be created. The CREATE DATABASE operation has been failed.
Since it is version 655, the database ‘DatabaseName’ cannot be created. Versions 611 and before are supported on this server.

The error notice above clearly states that the database cannot be opened on a SQL Server 2005 server because the database you are attempting to attach is version 655. Only databases with a version of 611 or earlier can be opened by SQL Server 2005. Check out the following article to learn more about a database’s internal version number.

Database Compatibility Levels and Internal Database Version Numbers for different SQL Server versions are shown in the table below :

Conclusion :

You learned why you can’t restore or attach a SQL Server database from a higher version to a lower version in this post.

Difference between Security Patch, Hotfix and Service Pack

Security Patch – Publicly released update to fix a known bug/issue
A security patch is a change applied to an asset to correct the weakness described by a vulnerability. This corrective action will prevent successful exploitation and remove or mitigate a threat’s capability to exploit a specific vulnerability in an asset.

Security patches are the primary method of fixing security vulnerabilities in software. Currently Microsoft releases their security patches once a month, and other operating systems and software projects have security teams dedicated to releasing the most reliable software patches as soon after a vulnerability announcement as possible.

Hotfix – update to fix a very specific issue, not always publicly released
A hotfix is a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug). Typically, hotfixes are made to address a specific customer situation and may not be distributed outside the customer organization.

A hotfix package might contain several encompassed bug fixes, raising the risk of possible regressions. An encompassed bug fix is a software bug fix which is not the main objective of a software patch, but rather the side-effect of it. Because of this some libraries for automatic updates like Stable Update also offer features to uninstall the applied fixes if necessary.

In a Microsoft Windows context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. These are small files, often automatically installed on the computer with Windows Update (although some may only be able to be obtained via Microsoft Support) and could contain a hot patch eliminating the need for a reboot.

Service Pack – Large Update that fixes many outstanding issues, normally includes all Patches, Hotfixes, Maintenance releases that predate the service pack.

A service pack (in short SP) is a collection of updates, fixes and/or enhancements to a software program delivered in the form of a single installable package. Many companies, such as Microsoft or Autodesk, typically release a service pack when the number of individual patches to a given program reaches a certain (arbitrary) limit. Installing a service pack is easier and less error-prone than installing a high number of patches individually, even more so when updating multiple computers over a network. Service packs are usually numbered, and thus shortly referred to as SP1, SP2, SP3 etc