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

Replication Scripts to get Replication Configuration Information :

The scripts can be run from various databases to pull this information and if you have multiple servers for replication like we do, you can use the Multi Server Query feature to query several servers at one time.  Execute same SQL Server query across multiple servers at the same time using Central Management Servers .

These scripts work for SQL 2005 , SQL 2008 and SQL 2012.

General Overview

I have three different scripts that you can use:

  • Script to run on Distribution database – This script returns completed setup replication information.
  • Script to run on Publisher database – This script returns what publications have been setup.
  • Script to run on Subscriber database – This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find any orphaned subscribers.

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information. I recommend making this a stored procedure and then creating a Reporting Services report, so that anyone can easily access this data.

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 run on Publisher database

This script returns what publications have been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = ‘Y’ it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName

— 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 Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

— Run from Subscriber Database

Use Subscriber Database

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

 

Settingup linked server between SQL Server and Oracle


In most cases, our installation and configuration processes do not change from 32-bit to 64-bit: setting up logins, creating SQL Agent jobs; nothing inherently different there.  Inconsistencies do exist however.  Take for example linked servers – more specifically linked servers to Oracle databases.  In 32-bit environments we simply install the Oracle client software, create the linked server, add remote logins and move on to the next project.  In the 64-bit world the process is slightly more difficult – and in the case of one critical step it impacts any existing users on the instance!

Process Creating Oracle Linked Server on a 64-bit SQL Instance:

  • Install Oracle 10g Release 2 64-bit client software (available from Oracle website)
  • Install Oracle 10g Release 2 64-bit ODAC software (available from Oracle website)
  • Restart SQL services
  • Configure OraOLEDB.Oracle provider
  • Create linked server
  • Add remote logins for linked server

The complete process is presented below:

Step One:  Installation of the Oracle 10g Release 2 64-bit client software

I will leave it up to you to secure the software by either download from their site or media available via any licensing agreements you may have with Oracle.  The important steps are outlined below.  You only need the barest install of the client.  Simply go ahead and select the InstantClient option from the opening dialog form and click Next.

The following screens simply require you to click Next when enabled.  These are in place to present the internal checks and summaries of work to be completed once you accept the install parameters.  Proceed through the screens, click Install, and then Finish once the installation is complete.

Step Two:  Installation Oracle 10g Release 2 64-bit ODAC software

Once you have an installed 64-bit Oracle Home you can proceed with installing the ODAC drivers for a 64-bit environment.  The OraOLEDB.Oracle driver installed in this step is what you need to set up your linked server.  You’ll find the setup.exe for this installation located in the unzipped Install folder.  I strongly suggest you review your previous installation of the client software via selecting the Installed Products button.  You want to verify that you have a valid 64-bit Oracle Home that you’ll be installing against during this process.  We would not be DBAs if we were not overly cautious.  It’s quite Darwinian:  the brash and risky don’t last long as Database Administrators.

If you accepted the defaults during the client installation, your “Installed Products” Inventory should appear as follows.  If so, then close the dialog and click Next to continue.  If not, then I suggest you revisit your installation of the client software, possibly de-installing and performing all steps presented previously once again.

We will be installing the OraOLEDB.Oracle driver that is included in the Oracle Data Access Components option .  Select Next to continue.

Using the drop-down box provided, select the existing Oracle Home you just verified was created as a part of your 64-bit Oracle client installation.  The Path will automatically change accordingly.  Continue by clicking Next.

You will be presented with the following sub-components.  I’ve selected all three (you never know when these items may be useful and they are extremely lightweight) though you only really need to install the first two items.

If you do decide to install the Oracle Services for Microsoft Transaction Server component, you’ll be prompted for the port number to use.  The default is 2030.

Click Next and you’ll be presented with the familiar summary screen, listing all components to be installed.  Review, make changes if necessary and then click Install.  Once completed we can move back to familiar territory:  SQL Server Management Studio.

After Completion Of Installation We need to Change Couple of things in TNSNAMES.ora

Edit TNSNAMES.ora

<install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:Oracle\Product\10.2.0\Client\network\ADMIN\TNSNAMES.ora)

There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):

DMDEV =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = FRIENDLYNAME)

)

)

There are a couple of things you need to change:

  • HOST = SERVERNAME. The SERVERNAME should be changed to reflect the actual address or hostname of the target system.
  • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.

 

Step Three:  Restart SQL Service

This is the most unfortunate step in the entire process.  This is usually the last thing a DBA wants to do.  It is the option of last resort whenever there is an issue on a SQL Server instance.  However, it is necessary in this case, so be sure to follow your corporate procedures for downtime notifications and process.  That being said, let’s move past this horrid step!

Step Four:  Configure OraOLEDB.Oracle provider

I confess.  I was not aware of this process existing until I had to do this the first time.  In Object Explorer, navigate down the tree of the SQL instance we’ve been working with until you’re able to view the list of Providers under Linked Servers.  Select OraOLEDB.Oracle and right-click in SQL Server Management Studio.

The Provider Options dialog box appears.  Enable the “Allow inprocess” option and close the dialog.  Now ask yourself:  “Self, what did I just do?  Some Internet Guy said click this and it will work.”  Let’s take a quick look at exactly what enabling this option does for a provider.  By default, OLE DB providers are instantiated outside of the SQL Server process.  While this protects SQL from any OLE DB errors, Microsoft SQL Server requires that the OLE DB provider run in-process for handling BLOB-ish data types (text and images).

Step Five:  Create a linked server to the Oracle Database

In General  TAB :

Linked Server : A name of your choosing which you will use when querying using four-part naming conventions.

Server Type : Other Data Source

Provider: Oracle Provider for OLE DB

Product Name: “Oracle” is fine here

Data Source: This should match the Service_Name Which you defined in TNSNAMES.ora

Create a Linked Server

In Security TAB : Select Be made using this security context Option and supply the remote login and password.

And then TEST the Connection ..

Hope this helps you a lot!!

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

Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

 

Introduction

If you are trying to restore a database on an SQL Server 2005 or a lower version from the database backup which was performed on an SQL Server 2008 instance then it will fail. In this article we will take a look at the reason why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

Error Message received when restoring an SQL Server 2008 database backup on an SQL Server 2005 or lower versions

Msg 3241, Level 16, State 7, Line 1
The media family on device ‘Drive:\BackupFolder\DatabaseName.BAK’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The reason for the failure is the internal version number of SQL Server 2008 is different from that of an SQL Server 2005 or lower versions. Since the internal version number of SQL Server 2008 is higher than that of an SQL Server 2005 database the restore process fails. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.

Important Note: – You will not be able to restore a database backup from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. Downgrade of a database is not supported using the database backup and restore method.

 

Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?

If you try to attach an SQL Server database which was initially created on an SQL Server 2008 instance to an SQL Server 2005 or a lower version then the process will fail with the below mentioned error.

Error Message received when attaching an SQL Server 2008 database on to an SQL Server 2005 or lower versions

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘DatabaseName’. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database ‘DatabaseName’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

The above error message clearly states that the database cannot be opened in SQL Server 2005 instance as the database which you are trying to attach is of the version 655. The SQL Server 2005 instance can only open databases which are of version 611 or earlier. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.

In below table you will able to relate Database Compatibility Levels and Internal Database Version Numbers for different versions of SQL Server.

SQL Server Edition Database Compatibility Level Internal Database Version Number
SQL Server 7 70 515
SQL Server 2000 80 539
SQL Server 2005 90 611/612
SQL Server 2008 100 655
SQL Server 2008 R2 105 660
SQL Server 2012 110 706

Conclusion

In this article you have seen why you can’t restore or attach an SQL Server database from an higher version of SQL Server to a lower version.