Install and Configure SQL Server for MDM

  • Article

10/3/2008

To plan your Microsoft® SQL Server® installation and configuration for System Center Mobile Device Manager (MDM), follow the best practices in this section.

In MDM, Microsoft SQL Server 2005 Standard Edition with Service Pack 2 is the supported and tested database management implementation. SQL Server Express Edition and Microsoft SQL Server 2008 are not supported for MDM. For more information about the requirements to install SQL Server together with MDM, see System Requirements for MDM Servers and Managed Devices. For more information about how to deploy SQL Server, see the SQL Server 2005 Readme and supporting documentation at this Microsoft Web site: https://go.microsoft.com/fwlink/?LinkId=105613.

Required Language Version for SQL Server

For MDM, we recommend that you install the English or local language version for SQL Server 2005. You should install SQL Server in the same language in which you install MDM.

If you install MDM Enrollment Server in a language that differs from that of the Microsoft SQL Server installation, and you connect to the computer that is running SQL Server remotely, you must install MDM Enrollment Server at a command prompt. During the installation, you must specify the name of the Anonymous account on the computer that is running SQL Server by using the ENWEB_SVC_ACC property. The following example shows you how to run Setup if you install the English version of MDM Enrollment Server but use a remote connection to a computer that is running the German version of SQL Server:

msiexec /i Enrollment.msi ENWEB_SVC_ACC="NT-AUTORITÄT\ANONYMOUS-ANMELDUNG"

If you install MDM Device Management Server or MDM Enrollment Server, while Anonymous, Network Service, or Local Service accounts are already present in the SQL logins on the computer that is running SQL Server in a language that differs from that of the computer on which you want to install MDM Device Management Server or MDM Enrollment Server, the installation will fail. Delete these accounts from the computer that is running SQL Server before you install MDM Device Management Server or MDM Enrollment Server or use a different SQL instance.

MDM Databases Installation Considerations

MDM databases are created as part of the installation process. MDM uses three SQL databases: AdminServices, MobileEnrollment, and TEEDB. The MobileEnrollment database is created during MDM Enrollment Server installation. The TEEDB database is created during MDM Device Management Server installation. The AdminServices database is created during the first installation of either MDM Device Management Server or MDM Enrollment Server.

Database Naming and Location

As you plan for naming and locating MDM databases, consider the following:

  • The three MDM databases must reside in a single instance of SQL Server, on one computer that is running SQL Server
  • MDM supports using SQL with clustering to help enable high availability
  • For optimal SQL database performance, establish data and transaction logs on separate physical disks
  • If you install MDM databases on a computer running Microsoft Windows Server 2000, the name of the computer that is running Microsoft SQL Server and the name of the computer that is running MDM Device Management Server, or MDM Enrollment Server, cannot begin with the same characters. For example, if you install MDM databases on a computer that is running SQL Server named mdmsql.contoso.com, and MDM Device Management Server or MDM Enrollment Server is named mdm.contoso.com, the database installation may fail. We recommend that you install SQL Server on Windows Server 2003 with SP2.

Database Size and Growth Planning

You can use the following information about MDM database size, growth, and capacity to help create the best configuration for your environment.

Database size at installation: The initial size requirement is small—each database uses 120 megabytes (MB) of hard disk space at installation. This means that you should reserve 360 MB of hard disk space as a minimum for the three MDM databases.

Number of transactions or operations per second running against each database: The number of transactions that the MDM databases must support depends on the following:

  • The number of managed devices in the MDM system
  • How often each device connects to the MDM system
  • How many tasks run each time the device connects to the MDM system

As an example, if your MDM system includes 5,000 managed devices, each device connects once every eight hours, and about ten tasks run each time the device connects, then there will be about two transactions per second running in the database, on average. The number of transactions per second scales linearly with the number of devices.

Note

This example is an estimate based on an average load. To be safe, you should add a factor (for example, a factor of five) to accommodate peak loads and the simultaneous load on other databases.

Expected database growth: Of the three MDM databases, the TEEDB database expands the most as devices are added and usage increases. Growth depends on the following:

  • The number of mobile devices in the MDM system
  • How many days of device history you configure the system to keep

As an example, if you have 5,000 managed devices and keep 30 days of device history data, the TEEDB database will grow to approximately 20 gigabytes (GB). The database growth scales linearly with the number of managed devices. This means, for example, that if you have only 1,000 devices, the size of the TEEDB would be about 4 GB.

Configure SQL Server Permissions

You must enable permissions in SQL Server to allow MDM administrators to work with databases and configure user access for MDM. You should grant administrators who are installing MDM permission to create and remove databases and accounts in SQL Server. You can grant permission to each administrator individually or grant permission to the SCMDM2008ServerAdministrators group.

In addition, to make sure that MDM has the required SQL Server user permissions to operate successfully, we recommend that you configure SQL Server for Windows integrated security.

Note

Complete the following procedure after you run MDM Setup to create the MDM databases.

To configure SQL Server for Windows integrated security

  1. On the Start menu, choose Microsoft SQL Server, and then choose SQL Server Management Studio.

  2. Expand the server, and then navigate to Security, then Logins.

  3. Add the required user or users to Logins.

  4. Expand the database for which you want to give users permissions, and then navigate to Security, then Users.

  5. Right-click Users, and then select New User.

  6. In the Database User Properties dialog box, type the domain and username of the required user or users in the Login name box, and then choose OK. Configure the SQL database to enable all domain users to access the database.

Set SQL Server Agent to Automatic Start

The SQL Server Agent service must be on the computer that is running SQL Server 2005 during MDM operation to clean up the local cache. In Administrative Tools, in the Services console, set this service to Automatic start.

When you restart the computer, or restart SQL Server, make sure that the SQL Server Agent service starts.

Configure Recovery Interval Option

When you set up the SQL Server database, keep the recovery interval set to 0. This is the default setting. A 0-recovery interval means that SQL Server automatically configures the recovery interval. Every time that an SQL database starts, SQL Server recovers each database. In practice, this means a recovery time of less than one minute, and, for active databases, a checkpoint approximately every one minute.

If you notice that checkpoints are impairing performance because they occur too frequently, increase the value in small increments until you are satisfied with the performance.

For more information about the recovery interval option, see this Microsoft Web site: https://go.microsoft.com/fwlink/?LinkId=105609.

Configure Remote SQL Connections

You must configure SQL Server to enable remote connections, even if the MDM databases are on the same computer with other MDM components. For more information about remote SQL database connections, see How to configure SQL Server 2005 to enable remote connections at this Microsoft Web site: https://go.microsoft.com/fwlink/?LinkId=105610.