SQL Server DBA Tutorial – Part 2

by

SQL Server DBA Tutorial – Part 2 from Coding compiler. This is the second tutorial in the series of SQL server dba tutorials. Here you are going to learn about service accounts, SQL server 2012 installation, SQL server databases and database properties. Before going to learn this tutorial, we recommend you to go through the SQL Server DBA Tutorial – Part 1

Topics Covered in this SQL Server DBA Tutorial

  • Service Accounts
  • Installing SQL Server 2012
  • Post Installation Steps
  • Introduction to SQL Server Database
  • How to create database in SQL server?
  • SQL Server Database Properties

SQL Server Service Accounts

Services used for better control on sql server one program may have multiple services. Enter sql server run using these services. We have,

  1. Sql server service
  2. Sql agent service
  3. Sql browser service
  4. Full-text service
  5. MSDTC service

SQL Server Service

If this service is running. SQL Server will run most important service. All other 4 services depend on this service. DB engine run under influence of sql server service.

SQL Agent Service

Entire automation process run under this service. agent service will look at scheduled time of jobs and invoke tasks automatically

SQL Browser Service

In Production service this service will be disabled due to security reasons.

Full Text Service

We keep most repeated words in catalog for faster search of data from database

MSDTC

Microsoft distribution transaction coordinator useful for handling distributed transactions from other server.

Installing SQL SERVER 2012

Before installing sql server, server needs the following prerequisites.

Prerequisites:

    1. .Net framework 4.0
    2. Windows powershell 2.0
    3. .Net 3.5 with service pack 1

SQL Server 2012 Installation Process

SQL server 2012 installation is almost same as sql server 2008 menu of sql server installation center. Planning, Installation, Maintenance, Tools, Resources, Advanced, and Options

  • Planning
  • Installation
  • Maintenance
  • Tools
  • Resources
  • Options

Planning helps in reviewing

  1. Hardware and software requirements
  2. Security considerations
  3. Online release notes
  4. Install upgrade advisor
  5. How to get started with sql server 2012 failover clustering

Planning various hardware and software requirements of the server. System configuration checker (SCC) scans the machine where installation of sql server exists and provide results that to avoid obstacles while installing upgrade advisor will help while upgrading.

Installation:

  • New sql server installation (or) add features
  • New sql server failover cluster installation
  • Add node to sql server failover cluster
  • Upgrade from sql server 2008 R2

Installation provides various installation options

We can install new sql server for the first time (or) we can add additional features to an existing installation.

  • We can install clustering and  add a node to the cluster
  • We can upgrade to sql server 2012 from old versions.

Maintenance:

  • Edition upgrade
  • Repair
  • Remove node from sql server failover cluster

We can upgrade and repair the sql server from here.

Tools

  • System configuration checker
  • Installed sql server features discovering report
  • Upgrade integration services packages
  • Power pivot configuration tool

System configuration checker scans sql server installation. Installed sql features provides detailed report about sql products installed on server.

Upgrade integration packages is a wizard steps in upgrading lower version SSIS packages to sql server 2012 packages

Resources

  • Sql server 2012 books online
  • Sql server tech center

Resources will help in knowledge on sql server 2012

Advanced

  • Install based on configuration file
  • Advanced cluster preparation/completion
  • Image preparation of standalone server

Configuration file install configuration file on existing file.

Advanced cluster provides wizards for failover clustering. Image preparation gives install methods in sql server 2012.

In option we can specify architecture of sql server install (or) to change location of install files.

Once all these details provided we can start actual installation

SQL Server 2012 Setup

  • setup support rules
  • setup role
  • features selection
  • disk space requirements
  • server configuration
  • distributed replay controller
  • distributed replay client
  • error reporting
  • install configuration rules
  • ready to install
  • installation progress
  • complete

Installation process start with system checks and validations setup role is new screen in sql server 2012, to choose install all features with default settings.

  1. features selection → provides all sql components to select
  2. install rules → need to select default (or) named instance.
  3. disk space requirements → provide details whether we have specified space (or) not.
  4. server configuration → what are the services available in server
  5. distribution replay controlled and distributed replay client are new features in sql server 2012, allows us to replay our captured trace files of sql server
  6. Error reporting → provides feedback to microsoft if want to send
  7. Ready to install → provides all the features what we have selected
  8. Installation progress → actual installation starts
  9. Complete → completes sql server installation

Sql server 2012 installation completed

SQL Server Post Installation Steps

  1. Verify protocols enables (or) not

Microsoft sql server 2012 — sql server configuration manager — sql server network configuration — protocols for MS sql server

  1. Open services in sql server configuration manager select sql services and verify whether they are started (or) not.
  2. From run command type services .msc and verify the ms sql services status and mode.
  3. Verify the advanced options using

Sp_configure “show advanced option” 1

Go and reconfigure

  1. Specify maximum and minimum server memory

Sp_configure ‘max server memory (MB), 8192;

Sp_configure ‘Min server memory (MB), 1024;

  1. Enable backup compression, if we want sp_configure ‘backup compression default’ 1

Go

Reconfigure with override

Go

SQL Server Databases

Database:  Database is a container which store data and data objects. It manages data and allows fast storage and retrieval of that data.

Types of Databases in SQL Server:

There are 2 types of databases,

  1. System databases
  2. User databases

1. System Databases:

System databases will be created as a part of sql server installation. Sql server internal operations will be perform using system databases.

User databases are created based on organisation requirement we are creating externally and stores data.

System databases available in sql server from 2000

  1. Master
  2. Model
  3. Msdb
  4. Temp db
  5. Distribution db (only available while replication configuring)

From sql server 2005 along with master, model, msdb and tempdb a new database introduced called resource db.

SQL Server Master Database:

  • The first database loaded by sql server on services start is master db.once master db starts then only other databases will start. it is just like a brain to sql server.
  • Master db stores critical information like sql server configuration such as CPU info memory configurations sp_configure setting
  • Master stores other system databases information in ‘sys databases’ table.
  • In master db we  have ‘sys alt files’ table which contains location of all system databases mdf and ldf will be stored. To start any database it has to come to master and then starts database.
  • Sql logins will be stored in ‘syslogins’ table of master db. If any user connected to sql server externally his login will be authenticated in master db.
  • It stores other server objects linked servers, endpoint set till sql 2000 we store all metadata (data about data) in tables. If any binary file effect enter data will be lost

SQL Server Model Database

Model database will act as a template in creating new user databases. How many files we want to create and how many mdf and ldf, what is initial size, path of binary files all these information we call as a template.

Every new database acquire properties from model database. New db goes to model database and get those properties.if we create any table all the new databases which we create from now on contains same table.

MSDB

Msdb stores all scheduling and automation information. In sql server we are doing automation in the form of job, sql server agent service will be responsible for this.

All sql jobs information will be stored in sysjobs, sysschedules and sys steps tables inside msdb.

Sql server agent service will read msdb schedule tables, based on this it will start and stop the tasks automatically.

History related information like backup, restoration log shipping history will be stored.

SQL Server Temp DB

Temporary database where all temporary objects will be created for performing sql server internal operations.

Temporary objects that are created by sql server internal operations such as temporary tables, temporary stored procedures etc.

Rows versions that are generated by data modifications transaction in database that uses read-committed (or) snapshot isolation stores here.

All the transformation, internal calculations, conditions sorting operations will be performed in Tem db.

Whenever sql server started Tempdb will be deleted and new copy will be created.

SQL Server Resource DB

Resource db is hidden database we can see only mdf and ldf physically. It stores where we keep sql binaries files.

It provides high security to metadata, it contains all system objects such as system objects physically stores.

Resource db makes upgradation to new sql server veron easier and faster. In earlier versions upgrading will delete and create system objects. Now resource database will contain all system objects we take copy of resource database mdf and ldf and completes upgrading easily.

Creating Databases in SQL Server

Open sql server 2012 →  connect to management studio →Select sql server instance → go to database right click → new database

General:

Database Name : SQL test 2012

Owner : SA

Logical Name: Sql test 2012 – data, Sql test – Log

Initial Size : 1 GB

Autogrowth : 500 MB

Path : E:\Database\sql test.mdf .Ldf

Database name: it provide name to database

Owner shows default, if we want we can change

Logical Name is used to refer the physical file in all T-sql statements.

Initial Size will acquire from model db properties we can change as per our requirement. What is the size of database while creating.

Autogrowth means once it reaches the initial how much my database size can increase it will add size.

Path, actually storage location of database, where my database is going to store(location).

SQL Server Database Properties

We require minimum 2 files to create a database 1 mdf and 1 Ldf.

There are 5 properties required to create a database

  1. Name
  2. Filename(path)
  3. Initial Size
  4. Maximum Size
  5. File Growth

Name, what is the database name that we are going to provide for new user database. Logical Mdf and logical Ldf will use for Administrative purpose.

File name, complete path where database is going to store.

Initial Size, we have estimate how much the database size initially we need. We have to specify the max limit and autogrowth means once it reaches the initial size automatically how much size it has to increase we can provide it either in MB (OR) Percentage.

To create database using T-Sql

Create database sql db 2012

It will create new database with default model properties.

We can specify the properties through T-Sql

Create database sql db 2012

On

(
Name = sqldb2012_data
Filename = ‘E:\data\sqldb2012_data.mdf’
Size = 10 GB
Max Size = 500 GB
File Growth = 5GB
)
Log on
(
Name = ‘sqldb2012_log’
Filename = ‘E:\data\sqldb2012_data.ldf’
Size = 1 GB
Max Size = 500 GB
File Growth = 500 MB
)

Rename a file name:

Alter database sql db 2012 modify file name = sql db 2012 – filename =’D:\sqldb_data.mdf’

When database is online we cannot more mdf and ldf. It may lead to database crash.

After creating database, verify properties

Files tab → shows how many Mdf and Ldf are there in database. We have add option in GUI we can add more files.

File group → By default we have only 1 file group as primary. We can group multiple files under one file group

Options

Collation setting →  Latin 1 – General

Compatibility Level → On which version sql server developed show here

For sql server 2012 it is 110

Related SQL Server Tutorials

SQL Server Interview Questions

SQL Server DBA Tutorial

Leave a Comment