Planning and deploying SQL Server for cloud management DB

All components of Windows Azure Pack and system center uses SQL Server database to store its configuration, run time data, historical usage data etc. Failure of the database is directly proportional to the failure of respective components.

Choosing the database server version and deployment model

In a bare minimum deployment, this SQL Server for management databases shall be used by the following components:

• Windows Azure Pack components
• SCVMM
• SPF
• SMA

While choosing the database version a deployment model, supportability and compatibility has to be aligned with respect to each of the components mentioned previously.

Windows Azure Pack supports the following MS SQL Server versions for managing databases:

• MS SQL Server 2008 Service Pack 3
• MS SQL Server 2008 R2 Service Pack 2
• MS SQL Server 2012 Service Pack 1
• MS SQL Server 2012 Service Pack 2
• MS SQL Server 2014

System Center 2012 R2 has different SQL Server version support differentiating upon components. Taking the common and latest, MS SQL 2012 SP1 is the supported SQL version for Windows Azure Pack and all System Center products, MS SQL 2012 SP2 and MS SQL 2014 is supported for WAP components and all System Center 2012 R2 components except app controller. Organizations or cloud providers can choose between SQL 2012 SP1, SQL 2012 SP2 and SQL 2014 depending upon the components installed.

Tip

SQL Support matrix for System Center 2012 R2 can be accessed at https://technet.microsoft.com/en-us/library/dn281933.aspx

The deployment model in SQL Server can be deployed in multiple ways with respect to placement, failover, and so on. WAP management database deployment is supported in following ways:

• Standalone SQL Server deployment
• SQL Server failover cluster
• SQL Server AlwaysOn availability groups

Choosing the right deployment model for your cloud management databases depends upon deployment strategy. Standalone deployment works fine but becomes a single point of failure. Failure in the management database will cause failure of all cloud management components functionalities.

SQL Cluster (active/passive) and SQL AlwaysOn availability group can be used to add redundancy for management databases eliminating any single point of failure.

While both options provide redundancy for SQL Server, AlwaysOn adds greater flexibility and reliability by enabling clusters without any shared storage and protection at database level, which is at instance level in case of SQL Cluster.

More on SQL Cluster versus SQL can be found at https://msdn.microsoft.com/en-us/library/ff929171.aspx.

Tip

SQL cluster functionalities is available in MS SQL Standard edition where AlwaysOn requires Enterprise edition license.

While installing SQL for management databases, the following features are required to be installed. Default collation will work when deploying for English language. See product specific guidelines for other languages:

• Common features:
  • Database engine
  • Management tools
• Product specific features:
  • Full text search (for SC operations Manager and Service Manager)
  • Reporting services (for SC operations Manager and Service Manager)
  • Analysis services (For SC Service Manager)

Additional consideration while deploying SQL Server for WAP and System Center 2012 R2 include:

• Deploy additional SQL Server/instances in a large production setup to avoid any performance issues.
• Reporting services instances cannot be shared between components.
• Configure pre-size and automatically grow along with monitoring for efficient and undisrupted management databases.
• SQL authentication needs to be enabled on the SQL Server before installing Windows Azure Pack components. If Windows authentication is planned for configuration, the current user has to be added as an administrator on the SQL Server.
• SQL AlwaysOn availability groups functionality is not supported for a named instance for System Center Service Manager Database, it is supported for default server instance only.

Note

SQL Express Edition can be used WAP in Express deployment. For WAP distributed deployment and system center components Standard edition is the minimum edition supported. Choose SQL edition depending upon the features and HA model discussed above.

Installing and configuring SQL Server 2012 SP2

For the purpose of evaluation in this book, we shall be using SQL Server 2012 SP2 standalone installation as the DB server. This database server shall be used by all WAP and system center components.

• Installation prerequisite:
  • A set of hardware and software prerequisite has to be met before deploying SQL Server
• Software requirements:

  Install the following components on SQL Server VM before installing SQL Server:

  • .NET 3.5 SP1 Framework
  • Windows PowerShell (installed by default)
  • Network protocol and libraries (like shared memory, named pipe, TCP IP—available by default)
• Active Directory accounts requirements:
  • Create a user account in AD for SQL Server administration and services login account
    
  • Add created user accounts in the local administrator's group on SQL Server Windows OS.

Installation procedure

The following is the installation procedure to be followed:

1. Download MS SQL Server 2012 SP1 installation media and mount on SQL VM.
2. Execute the setup file from installation media and select Standalone installation.
3. SQL setup will start a valid validation check for installing the SQL Server setup support file. All tests must report green to green to proceed further.
4. Enter the product key (embedded in licensed media) or select evaluation mode installation option as applicable.
5. Accept the license terms and proceed further to install setup files.
6. Verify the setup support rules result. All results must pass successfully to proceed further.
7. Select SQL Server Feature Installation on the Setup Role page.
   
8. Select Database Engine Services, Client Tools Connectivity and Management Tools - Basic on the Features page (select features as per product specific requirements discussed in the last topic).
   
9. Verify the installation rules status, all rules must pass successfully in order to proceed further.
10. Change the instance name to a name of your choice. For the purpose of evaluation we are using the Default instance.
    
11. Verify the disk space requirement and available space to proceed further.
12. Change the default service account for the SQL Server agent and database engine to the Active Directory account created earlier.
    
13. Select Mixed Mode authentication on the Database Engine Configuration page. Provide "sa" as the password and add the current logged in user (SQLDBADMIN) into SQL administrators. Change the data directories location to different drives if applicable.
    
14. Choose the error reporting option if you wish to participate in error reporting to Microsoft.
15. Verify the installation configuration rule status. Each rule must pass successfully to proceed further.
16. Verify the installation option selected summary.
17. SQL Setup will start installing SQL DB Server as per the inputs provided.
18. Close the wizard once installation finishes successfully, review setup logs in case of any errors.

Post installation tasks

Configure the following tasks after successfully installing SQL Server 2012 SP1:

1. Login into SQL Server using SQL Management Studio and verify successful installation. Also verify whether the system databases such as master, tempdb are created.
2. Create a login for SCVMM, SPF, and WAP admin Active Directory user. Permissions can be defined later. Browse through the Security | Logins menu and right click on Logins to create the SQL user.
3. Configure the SQL Server network port for remote access using SQL Server Configuration Manager. By default it's 1433.