Designing automated administration practices

The automation of administrative tasks is essential when the data platform turns to more complex systems and larger databases. The key task for the Database Administrator is to be able to automate the routine tasks and develop proactive maintenance to ensure that data availability and integrity is maintained without any interruption. The life-cycle of automated processes for SQL Server administration is a large topic, and it's not possible to cover all of them in this recipe alone. Hence, the key aspects of automation have been enlisted. In order to accomplish the automated administration, the SQL Server Services must be setup with startup accounts with necessary privileges that will help to harden the SQL Server platform at the time of deployment.

The evolving business needs and application requirements have made the Database Administrator's role more challenging. Prior to SQL Server 2008, a great deal of time was spent performing routine administrative tasks such as backup/restore operations and monitoring the SQL Server instances. The key ingredient for this recipe is to understand the windows operating system service and account privileges to implement the SQL Server platform's automation methods.

Getting ready

The SQL Server setup wizard follows the process of a windows installer-based installation that provides a single-tree feature to select or de-select SQL Server tools and components. The various components that can be selected are the SQL Server Database Engine, Analysis Services, Reporting Services, Integration Services, Replication, and Management tools. Each of the above services in SQL Server represents a set of processes to manage the authentication of server operations with Windows services, as seen in the following screenshot:

Depending upon the selected component that you decide to install, SQL Server setup will install the following services by default:

  • SQL Server Database Services—relational core database engine
  • SQL Server Agent—scheduling service to manage jobs, fire alerts, and the automation of some administrative tasks

Each service in SQL Server must have an account configured during the installation. The various account types are:

  • Built-in system account
  • Local user accounts
  • Domain user accounts

Each of these account types has various privileges on the local server and network execution services. The built-in system accounts are the local service account, the network service account, and the local system account.

Note

The domain user account is a local server account that has privileges on a domain network. As a best practice, if the service must interact with network services such as file share or be used as linked server connections to other servers running SQL Server, you might use a minimally-privileged domain account to safeguard the system against any individual services or processes which are compromised.

How to do it...

To reduce the interruption on administrative tasks, it is essential to define an indispensible privilege to the account for instance-aware services.

Note

As an administrative best practice, wherever possible, use a Local Server account for a new SQL Server installation. From a security perspective do not install SQL Server on a domain controller.

If the server is a new installation or no local account is available, then it is recommended to create a local user or domain user account to start SQL Server services.

  1. Ensure that the password suffices the enterprise security policies and is a strong password.
  2. As seen in the following screenshot, create the local user account without Windows Administrator privileges:
  3. If only the SQL Server service must interact with the network services or have access to domain resources then create the domain user account, which should be pre-created by the domain administrator with necessary privileges.
  4. Once the account is created, it is essential to grant privileges for that account on SQL Server. You can accomplish this in two ways:
    • Denote the account name and password while performing the setup (as shown in the next screenshot) or
    • Update the SQL Server services using SQL Server Configuration Manager from SQL Server 2008 R2 | Configuration Tools and select the appropriate program group on the server

      Tip

      Always use SQL Server Configuration Manager to attempt any password changes to the SQL Server services account. The advantage is the password for the account is updated immediately and will take effect as soon as the specified service is restarted.

  5. Similarly the best practice is to use a local service account with Administrator privileges on SQL Server and a Local server to perform the necessary tasks on Administration.

How it works...

The role of the SQL Server Agent is to handle the scheduling of tasks and communicate the results to relevant alert resources.

For the purpose of our recipe, the key functionality of SQL Server Agent service is classified as relics, which can be used to combine the alerts and communication to facilitate proactive administration. The SQL Server Agent service is the key control. First, we must understand the functionality of how these relics are governed by the service and then begin the process of configuring the service for automated administration.

The security context of a job step execution has a specific entity. For instance, if the job step specifies a proxy, the job step then runs in the security context of the credential of the proxy. If no proxy account is defined on the SQL Agent, then the default execution of that job step runs in the context of the SQL Server Agent service account. To facilitate the proactive administration methods, the agent functionality is differentiated into jobs, schedules, operators, alerts, and proxies, as shown in the following screenshot:

Inside a 'new job' and the 'job steps' there are different execution methods that are used to achieve the administrative tasks on SQL Server.

The different types of job steps are:

  • Analysis Services tasks
  • Executable programs
  • Integration Services packages
  • Microsoft ActiveX scripts
  • Operating System commands
  • PowerShell Scripts
  • Transact SQL (TSQL) statements

For this recipe, we will use the following types of execution methods:

  • Transact-SQL (TSQL): The default execution type selected is Transact-SQL script. To perform the simple data interactions against any database object, TSQL steps are the most efficient way to handle them. See the following screenshot:
  • ActiveX Script: This is useful when you need to execute a task using scripting languages such as VBScript or JScript. This type is more useful when you need to perform operations using SQL Server Management Object (SMO) that is designed for programming all aspects of management.
  • CmdExec —Operating System: By the name this is useful to execute any shell commands within SQL Server environments.
  • PowerShell: This is a substitute scripting method for both CmdExec and ActiveX scripts. It's a useful scripting shell that automates the administrative and application deployment activities.

MultiServer administration enables automated administration on multiple servers, which are called target servers. In a MultiServer environment, the jobs will be processed on all target servers that are first defined on a master server and then downloaded to the target servers. The security is addressed by default by the full SSL encryption and certification validation, which is enabled for the connection between master server and target server. To create a MultiServer environment, we can use Master Server wizard from SSMS using SQL Server Agent and point to MultiServer Administration.

Schedules allow the SQL Server Agent to execute the single or multiple job steps at defined intervals. The schedule can be used to automate routine administrative tasks as a single-step execution or as a recurring execution of desired steps, such as data ETL operations. The execution schedule can be any one of the following:

  • One-time—to execute the job step(s) at that particular defined time only
  • Recurring—to execute the job step(s) repeatedly, based on a defined time interval

There's more...

To automate the administrative tasks, it is important to set up the job step execution. To do this, you need to complete the following steps:

  • One-time
  • Recurring
  • Execute the job step when SQL Server Agent service starts
  • Execute the job step when CPU is idle

See Also

The Designing SQL Server Agent scheduled jobs for ETL processes recipe covers the usage of 'how to manage the alerts of automated administrative tasks effectively while executing SSIS packages.'

上一章目录下一章