SQL Cluster Server and non-cluster server asset creation differences in Control Compliance Suite

Products

• Control Compliance Suite Windows

Issue/Introduction

Considerations in configuring SQL Server assets (cluster and non-cluster) in CCS.

 

Resolution

 Importing SQL Servers into CCS 11.

Considerations:    Because network import of SQL Servers cluster servers gives multiple entries in your CCS Asset system for the same SQL Server (one per cluster node that is up and running), it is best to use a CSV import for entering multiple SQL Cluster servers.   For SQL Cluster servers, only agentless data collection is supported for raw data collection (RBC) at the time of this article.

 

Preliminary Work: Credentials for the Windows Assets must be in place prior to using a network method of asset import.

Non-cluster SQL Server Import:

               Two Methods:

1.      Use a network import to do it after first importing Windows Machine assets that the SQL server(s) are on.

a.      Do an LDAP Windows machine asset import to get the Windows machine assets into CCS.

b.      Do another Windows machine asset import but this time use a Network import that targets the Windows machines you just got into the system….this will pull the remaining fields directly from the machines (only the required fields come in on ldap import).

c.      Finally do an SQL Server import using an import type of Network, scoped to those same Windows machines that you imported initially in step a. This should pull in any SQL Server detected on that windows machine.

                                                                                  i.     Each SQL Server instance on a particular host machine should come into the CCS Asset system each having different instance names and ports but being associated with a single Windows machine asset.

2.      Use CSV file with the Windows machine’s resolvable hostname being entered in the Dbif.server.hostName field for each SQL instance that resides on each physical server with that hostname. Therefore this field will be the same for each SQL server instance on a given physical Windows server. 

a.      All fields that begin with the word “Dbif. “ should be filled in. However only some fields are required (use a manual ADD Asset job for SQL Servers to see which fields must be filled in to create an SQL server asset). Fields in the CSV file that begin with the word “Common. “ do not need to be filled in but can be blank. However all field headers must be present in the CSV file.

NOTE: See “Configuring the CSV Data Collector” section inside the CCS 11 User’s Guide in order to specify the location of the CSV file to the CCS Manager being used to import the assets from the CSV file.

 

 Cluster Server Import:

               CSV import:

1.      Ensure first that SQL Server asset type is selected in the Asset Type pull down menu, and then export the CSV Header file using the pull down menu pictured in screen shot below.

              

 

 

2.      Once you have the CSV file created, the difference for SQL Cluster servers is that the Virtual IP Address (VIP) must be targeted in order to pull information from the SQL cluster rather than CCS targeting only one of the hosts in that cluster. To do this, you must have a resolvable  VIP hostname that can be used to populate the Dbif.server.hostName field in the CSV file. By using the resolvable VIP hostname, rather than a physical machine hostname, the SQL data collection targets the VIP when collecting data. This is the only difference. All other fields in the CSV file will be similar to those used in non-cluster server CSV files.

3.      Once you have the CSV file populated and that file setup for the CCS Manager to process (see Note earlier in this document for location of instructions for setting up this file on a CCS manager for consumption), you can create an Asset Import job for SQL Server assets that uses the CSV file method. 

a.      As long as the instance name field, Dbif.server.serverName, is populated with the correct SQL server instance name, and the SQL Cluster server’s SQL Browser service is active, CCS should be able to collect information from each separate SQL instance serviced by the same VIP.

ALL:

               Once the SQL server instances are imported, SQL server credentials (whether generic or server specific) will need to be entered into the CCS credentials management system. Data collections cannot occur until valid SQL credentials have been specified. 

Do not forget that after each new addition of an SQL server asset, if asset specific credentials are being used (i.e. domain wide SQL credentials are not being used), then each new SQL server asset will need to be added to any existing asset specific credentials or new asset credentials for that server must be created.

 

Examples of CSV file (specific fields):

1.      Non-cluster environment. Single Windows machine named “testSQL” in domain named “Test” running three different SQL instances.

a.      Entries in CSV file for Dbif.server.SQLServerDomainName ,Dbif.server.serverName and Dbif.server.hostName fields (Note: Skipping the Common fields but they need to be there)

                                                    i.     Test,testSQL\inst1,testSQL

                                                   ii.     Test,testSQL\inst2,testSQL

                                                  iii.     Test,testSQL\inst3,testSQL

2.      Cluster environment. Five node cluster servicing three SQL instances. Server instances called “TestVIP\<animal>” and resolvable VIP named “TestVIP”. Domain named “Test”

a.      Entries in CSV file for Dbif.server.SQLServerDomainName ,Dbif.server.serverName and Dbif.server.hostName fields (Note: Skipping the Common fields but they need to be there)

                                                    i.     Test,TestVIP\dog,TestVIP

                                                   ii.     Test,TestVIP\cat,TestVIP

                                                  iii.     Test,TestVIP\cow,TestVIP

NOTE: VIPs with names that are meaningful are helpful in recognizing one SQL cluster server asset from another inside of CCS. 

 

 

Applies To

Control Compliance Suite 11.x