Product Documentation

Microsoft SQL Services

Jun 05, 2015
Updated: 2012-11-07
CloudPortal Services Manager Microsoft SQL Services host SQL servers from the cloud. Microsoft SQL Services require no installation and use a remote connection (typically TCP/IP) to Microsoft SQL Server 2005 and 2008.

Prerequisites

  • Microsoft SQL Server 2008 SP2, Microsoft SQL Server 2008, or Microsoft SQL Server 2005:
    • Member of a CloudPortal-managed domain.
    • Set Authentication mode to SQL Server and Windows Authentication.
    • Enable remote connection.
    • Enable protocols for remote connection (for example, TCP/IP).
    • Ensure the SQL Server Browser service is running and set to start automatically. This ensures Services Manager can locate the SQL Server and enumerate the instances installed when you configure the SQL service in the control panel.
  • Install the SQL Native Client component on the CloudPortal Services Manager Provisioning server.

    The 32- and 64-bit clients for each supported version of Microsoft SQL Server are available from the Microsoft downloads site.

To configure Microsoft SQL Services

  1. Enable the service (top level) and create a default customer plan:
    1. From the main menu, choose Configuration > System Manager > Service Deployment and expand Microsoft SQL Server Hosting.
    2. Click Customer Plans, enter a Name such as Default, click Create, and then click Save.
  2. Enable and configure the service (location level):
    1. Under Service Filter, select Active Directory Location Services, choose a Location Filter if applicable, expand Microsoft SQL Server Hosting, and click Service Settings.
    2. In Connection String Pattern, specify the connection string used to connect to SQL Server instances. If you are using SQL authentication, use the string from the Connection String Pattern for SQL Authentication setting in this field. If you are using Windows authentication, use the string from the Connection String Pattern for Windows Authentication setting in this field. When editing the strings, specify the values for DatabaseName and, if using SQL authentication, the SQL user name and password.

      For example:

      • SQL authentication: Data Source={ServerInstanceName};Initial Catalog=Master;User ID=sa;Password=secret
      • Windows authentication: Data Source={ServerInstanceName};Initial Catalog=Master;Integrated Security=SSPI
      Note: Services Manager automatically supplies the value for {ServerInstanceName} when the database is provisioned. Therefore, this value does not require editing.
    3. Specify the Database File Path and the Database Log File Path. Example: C:SQLhosting
    4. Specify the User Domain Name such as lab4, click Apply changes, and then click Save.
  3. Assign server roles:
    1. From the main menu, choose Configuration > System Manager > Server Roles and then expand the entry for the SQL hosting server.
    2. Under Server Roles, select Microsoft SQL Server 2005 Hosting and then click Save.
  4. Create a server collection:
    1. From the main menu, choose Configuration > System Manager > Server Collections.
    2. If the Location Filter appears, select the relevant location from the list.
    3. Click New Server Collection.
    4. Enter a Name for the collection, such as SQLHosting.
    5. From the Service list, choose Microsoft SQL Server Hosting.
    6. In the Servers list, select each SQL hosting server to be managed under this server collection and then click Save.
  5. Verify server settings for the default customer plan:
    1. From the main menu, choose Configuration > System Manager > Service Deployment, select Active Directory Location Services, choose a Location Filter if applicable, expand Microsoft SQL Server Hosting, and click Customer Plans.
    2. Expand the default customer plan and verify that the correct Server Collection is selected, specify the database and log file size settings, click Apply changes, and click Save.
  6. Retrieve SQL server instances:
    1. From the main menu, choose Configuration > System Manager > Server Resources > SQL Servers, expand a SQL server entry, and click Retrieve. Repeat this step for each SQL server.
    2. Verify that all required SQL server instances appear in the list. To manually add a server instance that already exists on the SQL server, click Add. To specify the default instance, enter only the server name. To specify a nonstandard instance and port, use the following form: servernameinstance,port. Example: lab4-SQL01INST01,1450

      At least one server instance must be configured per server.

    3. To restrict an instance so that it is not available in the Services Manager, click Edit and then select the Reserved check box.