SQL Basics and Configuration Requirements for SCCM

What is SQL?

  • SQL stands for Structured Query Language.
  • It is designed for managing data in a relational database management system (RDBMS).
  • SQL is a database language, it is used for database creation, deletion, fetching rows and modifying rows etc.
  • SQL is based on relational algebra and tuple relational calculus.

All DBMS like MySQL, Oracle, MS Access, Sybase, Informix, Postgres and SQL Server use SQL as standard database language.

Why SQL is required

SQL is required:

  • To create new databases, tables and views
  • To insert records in a database
  • To update records in a database
  • To delete records from a database
  • To retrieve data from a database

What SQL does

  • With SQL, we can query our database in numbers of ways, using English-like statements.
  • With SQL, the user can access data from relational database management system.
  • It allows the user to describe the data.
  • It allows the user to define the data in the database and manipulate it when needed.
  • It allows the user to create and drop database and table.
  • It allows the user to create a view, stored procedure, function in a database.
  • It allows the user to set permission on tables, procedure, and view.

What is Database

database is an organized collection of data.

Database handlers create database in such a way that only one set of software program provide access of data to all the users.

The main purpose of database is to operate large amount of information by storing, retrieving and managing.

There are many dynamic websites on the world wide web now a days which are handled through databases. For example, a model to checks the availability of rooms in a hotel. It is an example of dynamic website that uses database.

There are many database available like MySQL, Sybase, Oracle, Mango DB, Informix, Postgre, SQL Server etc.

SQL or Structured Query Language is used to perform operation on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.

Configurations for the SQL Server Site Database

Each System Center 2012 Configuration Manager site database can be installed on either the default instance or a named instance of a SQL Server installation. The SQL Server instance can be co-located with the site system server, or on a remote computer. In a hierarchy with multiple sites, each site can use a different version of SQL Server to host the site database so long as that version of SQL Server is supported by the version of Configuration Manager that you use. For example, if your hierarchy runs Configuration Manager SP1, it is supported to use SQL Server 2008 R2 with SP1 and cumulative update 6 at the central administration site, and to use SQL Server 2012 with no service pack and cumulative update 2 at a child primary site, or vice versa.

When you use a remote SQL Server, the instance of SQL Server used to host the site database can also be configured as a SQL Server failover cluster in a single instance cluster, or a multiple instance configuration. SQL Server cluster configurations that have multiple active nodes are supported for hosting the site database. The site database site system role is the only System Center 2012 Configuration Manager site system role supported on an instance of a Windows Server cluster. If you use a SQL Server cluster for the site database, you must add the computer account of the site server to the Local Administrators group of each Windows Server cluster node computer.

A SQL Server cluster in a Network Load Balancing (NLB) cluster configuration is not supported. Additionally, SQL Server database mirroring technology and peer-to-peer replication are not supported. SQL Server standard transactional replication is supported only for replicating objects to management points that are configured to use database replicas.

When you install a secondary site, you can use an existing instance of SQL Server or allow Setup to install and use an instance of SQL Server Express. Whichever option that you choose, SQL Server must be located on the secondary site server. The version of SQL Server Express that Setup installs depends on the version of Configuration Manager that you use:

  • System Center 2012 Configuration Manager without a service pack: SQL Server 2008 Express
  • System Center 2012 Configuration Manager with SP1: SQL Server 2012 Express

The following table lists the SQL Server versions that are supported by System Center 2012 Configuration Manager.

For more information on SQL Server versions that are supported by System Center 2012 Configuration Manager, follow the link.

1When you use SQL Server Standard for the database at the central administration site, the hierarchy can only support up to 50,000 clients. For more information, see Site and Site System Role Scalability.

2The following limitations apply when you use SQL Server 2014 with this version of Configuration Manager:

  • For System Center 2012 R2 Configuration Manager and System Center 2012 Configuration Manager with SP1: For a site that uses SQL Server 2014, a site restore fails unless you install the update fromKB 3020755. This hotfix is independent of the cumulative update level of the Configuration Manager site.Beginning with System Center 2012 Configuration Manager SP2 this issue no longer applies, and site restores are supported with no additional actions.
  • Configuration Manager does not support using Windows Server Update Services (WSUS) 3 SP2 on the following Windows Server versions when the Server also runs SQL Server 2014:
    • Windows Server 2008 SP2
    • Windows Server 2008 R2 SP1

3This version of SQL Server is only supported when you upgrade the install of SQL Server that hosts an existing site database to this version of SQL Server.

 SQL Server Requirements

The following are required configurations for each database server with a full SQL Server installation, and on each SQL Server Express installation that you manually configure for secondary sites. You do not have to configure SQL Server Express for a secondary site if SQL Server Express is installed by Configuration Manager.

Configuration More information
SQL Server version Configuration Manager requires a 64-bit version of SQL Server to host the site database.
Database collation At each site, both the instance of SQL Server that is used for the site and the site database must use the following collation: SQL_Latin1_General_CP1_CI_AS.

Configuration Manager supports two exceptions to this collation to meet standards that are defined in GB18030 for use in China. For more information, see Technical Reference for International Support in Configuration Manager.
SQL Server features Only the Database Engine Services feature is required for each site server.

Configuration Manager database replication does not require the SQL Server replication feature. However, this is required if you will Configure Database Replicas for Management Points.
Windows Authentication Configuration Manager requires Windows authentication to validate connections to the database.
SQL Server instance You must use a dedicated instance of SQL Server for each site.
SQL Server memory When you use a database server that is co-located with the site server, limit the memory for SQL Server to 50 to 80 percent of the available addressable system memory.

When you use a dedicated SQL Server, limit the memory for SQL Server to 80 to 90 percent of the available addressable system memory.

Configuration Manager requires SQL Server to reserve a minimum of 8 gigabytes (GB) of memory in the buffer pool used by an instance of SQL Server for the central administration site and primary site and a minimum of 4 gigabytes (GB) for the secondary site. This memory is reserved by using the Minimum server memory setting under Server Memory Options and is configured by using SQL Server Management Studio. For more information about how to set a fixed amount of memory, see How to: Set a Fixed Amount of Memory (SQL Server Management Studio).

SQL nested triggers SQL nested triggers must be enabled.
SQL Server CLR integration The site database requires SQL Server common language runtime (CLR) to be enabled. This is enabled automatically when Configuration Manager installs. For more information about CLR, see Introduction to SQL Server CLR Integration

 Optional SQL Server Configurations:- 

The following configurations either support multiple choices or are optional on each database server with a full SQL Server installation.

Configuration More information
SQL Server service On each database server, you can configure the SQL Server service to run by using a domain local account or the local system account of the computer that is running SQL Server.

·         Use a domain user account as a SQL Server best practice. This kind of account can be more secure than the local system account but might require you to manually register the Service Principle Name (SPN) for the account.

·         Use the local system account of the computer that is running SQL Server to simplify the configuration process. When you use the local system account, Configuration Manager automatically registers the SPN for the SQL Server service. Be aware that using the local system account for the SQL Server service is not a SQL Server best practice.

For information about SQL Server best practices, see the product documentation for the version of Microsoft SQL Server that you are using. For information about SPN configurations for Configuration Manager, see How to Manage the SPN for SQL Server Site Database Servers. For information about how to change the account that is used by the SQL Service, see How to: Change the Service Startup Account for SQL Server (SQL Server Configuration Manager).

SQL Server Reporting Services Required to install a reporting services point that lets you run reports.
SQL Server ports For communication to the SQL Server database engine, and for intersite replication, you can use the default SQL Server port configurations or specify custom ports:

·         Intersite communications use the SQL Server Service Broker, which by default uses port TCP 4022.

·         Intrasite communication between the SQL Server database engine and various Configuration Manager site system roles by default use port TCP 1433. The following site system roles communicate directly with the SQL Server database:

o    Management point

o    SMS Provider computer

o    Reporting Services point

o    Site server

When a SQL Server hosts a database from more than one site, each database must use a separate instance of SQL Server, and each instance must be configured to use a unique set of ports.

Configuration Manager does not support dynamic ports. Because SQL Server named instances by default use dynamic ports for connections to the database engine, when you use a named instance, you must manually configure the static port that you want to use for intrasite communication.

If you have a firewall enabled on the computer that is running SQL Server, make sure that it is configured to allow the ports that are being used by your deployment and at any locations on the network between computers that communicate with the SQL Server.

For an example of how to configure SQL Server to use a specific port, see How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) in the SQL Server TechNet library.

Source:- https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLDBconfig


Categories: Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s