this.toString = function() {
arguments.callee.setAlias('Exception.toStringFull');
var parts = [];
parts.push(this.message);
parts.push('\n');
if (this.innerException) {
var WMIProvider = function(wmiNamespace, computerName) {
arguments.callee.setAlias('WMIProvider.constructor');
this.wmiNamespace = wmiNamespace;
this.computerName = (!computerName) ? computerName : ".";
var SWbemLocator;
try {
SWbemLocator = new ActiveXObject("WbemScripting.SWbemLocator");
}
catch (e) {
throw new Exception("Cannot create 'WbemScripting.SWbemLocator' object.", e);
}
this.SWbemLocator = SWbemLocator;
var wmiService;
try {
wmiService = this.SWbemLocator.ConnectServer(this.computerName, this.wmiNamespace);
}
catch (e) {
throw new Exception("Cannot connect to WMI of computer '" + this.computerName + "' by namespace '" + this.wmiNamespace + "'.", e);
}
this.WMIService = wmiService;
this.ExecQuery = function(query) {
arguments.callee.setAlias('WMIProvider.ExecQuery');
var result = null;
try {
result = this.WMIService.ExecQuery(query, "WQL", 0x0);
}
catch (e) {
throw new Exception("Cannot execute WMI query '" + query + "'.", e);
}
return result;
}
}
// ##### Scripts\DiscoverSQL2012FileGroups.js
//#Include File:DiscoverSQL2012FileGroupsAndFiles.js
//' Parameters that should be passed to this script
//' 0 MPElement ID ($MPElement$)
//' 1 Target Id for ME this rule is running against ($Target/Id$)
//' 2 Computer (FQDN) that SQL instance is hosted on
//' 3 SQL Connection String for the instance that the DBs are being discovered on
//' 4 SQL Instance that this rule is being run for
function EscapeWQLString (wqlString) {
return wqlString.replace(/'/g, "\\'");
}
function IsRunningInstanceSQLServer(sqlInstanceName) {
arguments.callee.setAlias('CheckSQLServerState');
var isRunning = false;
try {
var wmiProvider = new WMIProvider("root\\Microsoft\\SqlServer\\ComputerManagement11");
var sqlService = wmiProvider.ExecQuery("select state from SqlService WHERE (ServiceName = '" + EscapeWQLString(sqlInstanceName) + "' OR ServiceName = 'MSSQL$" + EscapeWQLString(sqlInstanceName) + "') and SQLServiceType ='1'");
if (sqlService.Count > 0) {
var e = new Enumerator(sqlService);
e.moveFirst();
var sqlServiceObject = e.item();
function GetDatabaseExcludeList(InstanceName) {
arguments.callee.setAlias('GetDatabaseExcludeList');
var databaseExcludeList = "";
try {
var opsMgrAPI = new OpsMgrAPI();
var registryKey = opsMgrAPI.ScriptAPI.GetScriptStateKeyPath(ManagementGroupID);
logger.LogDebug(EVENT_ID, "ScriptStateKeyPath: " + registryKey);
var registry = new Registry();
var databaseExcludeListKey = "HKEY_LOCAL_MACHINE\\" + registryKey + "\\" + InstanceName + "\\DatabaseExcludeList";
if (registry.IsKeyExists(databaseExcludeListKey)) {
databaseExcludeList = registry.Read(databaseExcludeListKey);
databaseExcludeList = databaseExcludeList.replace(/(^\s+)|(\s+$)/g, "");
if (databaseExcludeList != '*') {
var excludeDatabases = databaseExcludeList.split(",");
for (var i = 0; i < excludeDatabases.length; i++) {
excludeDatabases[i] = excludeDatabases[i].replace(/(^\s+)|(\s+$)/g, "").split("'").join("''");
}
databaseExcludeList = "'" + excludeDatabases.join("','") + "'";
}
}
}
catch (e) {
throw new Exception("Can't read database exclude list from registry.", e);
}
return databaseExcludeList;
}
// Returns -1: If DB is not in AlwaysOn
// Returns 0: If DB is in AlwaysOn and replica allow connections is NO
// Returns 1: If DB is in AlwaysOn and replica allow connections is YES
function AlwaysOnReplicaAllowConnections(aDbConnection, aDatabaseID)
{
var query = " SELECT columns.id, " +
" CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')";
var result = aDbConnection.ExecuteQuery(query);
result.MoveFirst();
if (!result.EOF)
{
if (result("HasAlwaysOn").Value == 1)
{
query = " SELECT d.name, d.database_id, " +
" CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica, " +
" ar.secondary_role_allow_connections " +
" FROM sys.databases d " +
" JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id " +
" JOIN sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/" +
" WHERE d.database_id = " + aDatabaseID
result = aDbConnection.ExecuteQuery(query);
result.MoveFirst();
if (!result.EOF)
{
if (result("is_replica").Value == 1)
{
if (result("secondary_role_allow_connections").Value == 0)
{
return 0;
}
else
{
return 1;
}
}
}
}
}
return -1;
}
function Main(SourceID, ManagedEntityId, TargetComputer, serverName, instanceName)
{
arguments.callee.setAlias('Main');
var dbMasterConnection;
var opsMgrAPI = new OpsMgrAPI();
var oSQLDiscoveryData = null;
try {
oSQLDiscoveryData = opsMgrAPI.ScriptAPI.CreateDiscoveryData(0, SourceID, ManagedEntityId);
} catch (e) {
throw new Exception("Can't create discovery data.", e);
}
try
{
dbMasterConnection = new ADODB("Server=" + EscapeConnStringValue(serverName) + ";Database=master;Trusted_Connection=yes", "sqloledb", 30);
var databaseExcludeList = GetDatabaseExcludeList(instanceName);
if (databaseExcludeList == '*') {
opsMgrAPI.ScriptAPI.Return(oSQLDiscoveryData);
return;
}
var conditionDatabaseExcludeList = '';
if (databaseExcludeList != '') {
conditionDatabaseExcludeList = " AND name NOT IN (" + databaseExcludeList + ") ";
}
var dbSet = dbMasterConnection.ExecuteQuery("select database_id, name from sys.databases WHERE (state = " + DatabaseState.NORMAL + " OR state = " + DatabaseState.STANDBY + ") and source_database_id is null" + conditionDatabaseExcludeList);
for (dbSet.MoveFirst(); !dbSet.EOF; dbSet.MoveNext())
{
var databaseName = dbSet("name").Value;
var databaseID = dbSet("database_id").Value;
var dbConnection;
try
{
dbConnection = new ADODB("Server=" + EscapeConnStringValue(serverName) + ";Database=" + EscapeConnStringValue(databaseName) + ";Trusted_Connection=yes", "sqloledb", 30);
var dbFileGroupSet = dbConnection.ExecuteQuery("SELECT fg.name as fileGroupName, " +
" fg.data_space_id as fileGroupId, " +
" fg.is_read_only as fileGroupReadOnly " +
" FROM sys.filegroups fg ");
for (dbFileGroupSet.MoveFirst(); !dbFileGroupSet.EOF; dbFileGroupSet.MoveNext())
{
var oFileGroupInstance = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.DBFileGroup']$");
oFileGroupInstance.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputer);
oFileGroupInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", instanceName);
oFileGroupInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", databaseName);
oFileGroupInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.DBFileGroup']/GroupID$", dbFileGroupSet("fileGroupId").Value);
oFileGroupInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.DBFileGroup']/GroupName$", dbFileGroupSet("fileGroupName").Value);
oFileGroupInstance.AddProperty("$MPElement[Name='SQL!Microsoft.SQLServer.DBFileGroup']/ReadOnly$", (dbFileGroupSet("fileGroupReadOnly").Value == 1) ? true : false);
oFileGroupInstance.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", dbFileGroupSet("fileGroupName").Value);
oSQLDiscoveryData.AddInstance(oFileGroupInstance);
}
}
catch (e)
{
var replicaAllowConnections = AlwaysOnReplicaAllowConnections(dbMasterConnection, databaseID);
if (replicaAllowConnections != 0)
{
oSQLDiscoveryData.IsSnapshot = false;
if (IsRunningInstanceSQLServer(instanceName))
{
if (e.innerException && e.innerException.number != 0 && (((e.innerException.number & 0xFFFF) == 16389) || ((e.innerException.number & 0xFFFF) == 3661))) // Login failed
{
logger.LogError(EVENT_ID, "Cannot login to database [" + TargetComputer + "][" + instanceName + ":" + databaseName + "] ");
dbConnection = null; // prevent calling Close()
}
else
{
logger.LogError(EVENT_ID, e.toString());
}
}
}
else // server is AlwaysOn replica and is not readable
{
dbConnection = null; // prevent calling Close()
}
}
finally
{
if (dbConnection)
{
dbConnection.Close();
}
}
}
}
catch (e)
{
oSQLDiscoveryData.IsSnapshot = false;
if (IsRunningInstanceSQLServer(instanceName))
{
if (e.innerException && e.innerException.number != 0 && ( ((e.innerException.number & 0xFFFF) == 16389) || ((e.innerException.number & 0xFFFF) == 3661)) ) // Login failed
{
logger.LogError(EVENT_ID, "Cannot login to database [" + TargetComputer + "][" + instanceName + ":master] ");
}
else
{
throw new Exception("Script '" + WScript.ScriptName + "' failed.", e);
}
}
}
finally
{
if (dbMasterConnection)
{
dbMasterConnection.Close();
}
opsMgrAPI.ScriptAPI.Return(oSQLDiscoveryData);
}
}
var EVENT_ID = 4001;
var logger = new Logger();
logger.LogDebug(EVENT_ID, "Start...")
if (WScript.Arguments.Count() != 5)
WScript.Quit();
var SourceID = WScript.Arguments(0);
var ManagedEntityId = WScript.Arguments(1);
var TargetComputer = WScript.Arguments(2);
var ServerName = WScript.Arguments(3);
var InstanceName = WScript.Arguments(4);