SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@senecacollege.

ca

SQL710 Lecture Material

[Link]@[Link]

Page | 1
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

DBA Tasks................................................................................................................................................ 8
Installation, configuration, upgrade, and migration ............................................................................. 8
Backup and recovery ........................................................................................................................... 8
Database security ................................................................................................................................ 8
Storage and capacity planning ............................................................................................................. 8
Performance monitoring and tuning .................................................................................................... 9
Troubleshooting .................................................................................................................................. 9
High availability ................................................................................................................................... 9
Data Extraction, Transformation, and Loading (ETL or DTS).................................................................. 9
Very Large Databases (VLDBs) ............................................................................................................. 9
SQL Server Editions (2012)..................................................................................................................... 10
SQL Server Tools (selected).................................................................................................................... 11
SQL Server Management Studio (SSMS) ............................................................................................. 11
SQL Server Configuration Manager .................................................................................................... 11
SQL Server Profiler............................................................................................................................. 11
Database Engine Tuning Advisor ........................................................................................................ 11
RAID Levels (Redundant Array of Independent Disks) ............................................................................ 12
RAID 0 ............................................................................................................................................... 12
RAID 1 ............................................................................................................................................... 12
RAID 5 ............................................................................................................................................... 13
RAID 10 (or 1+0) ................................................................................................................................ 13
How Parity Works .............................................................................................................................. 14
Capacity Planning (OLTP) ....................................................................................................................... 16
Installing and Upgrading SQL Server ...................................................................................................... 18
Service Accounts ............................................................................................................................... 18
Account Privileges and Permissions ................................................................................................... 19
Checks and Conditions ....................................................................................................................... 20
Network Security ............................................................................................................................... 21
Windows Version............................................................................................................................... 21
Server Reboot.................................................................................................................................... 21
Windows Management Instrumentation (WMI) ................................................................................ 21
Default and Named Instances ............................................................................................................ 21
GUI Installation.................................................................................................................................. 22

Page | 2
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Command Line Install ........................................................................................................................ 28

System Databases ................................................................................................................................. 29
master ............................................................................................................................................... 29
model ................................................................................................................................................ 29
msdb ................................................................................................................................................. 29
tempdb ............................................................................................................................................. 30
resource database ............................................................................................................................. 30
Configuration ........................................................................................................................................ 31
Authentication Modes ....................................................................................................................... 31
Protocol Selection ............................................................................................................................. 32
Shared Memory ............................................................................................................................. 32
Named Pipes.................................................................................................................................. 32
TCP/IP............................................................................................................................................ 32
VIA................................................................................................................................................. 32
Static and Dynamic TCP Ports ............................................................................................................ 33
Firewalls ............................................................................................................................................ 33
Encryption ......................................................................................................................................... 35
Implement Least Privilege.................................................................................................................. 35
DATABASE ROLES........................................................................................................................... 35
APPLICATION ROLES ...................................................................................................................... 35
FIXED SERVER ROLES...................................................................................................................... 35
FIXED DATABASE ROLES ................................................................................................................. 36
Accounts ............................................................................................................................................... 37
Server Login....................................................................................................................................... 37
Database User ................................................................................................................................... 37
Roles ..................................................................................................................................................... 38
Database Level Roles ......................................................................................................................... 38
Fixed-Database Roles ..................................................................................................................... 38
Permissions assigned to the fixed-database roles:.......................................................................... 39
Schemas ................................................................................................................................................ 40
What do they do? .............................................................................................................................. 40
Side Bar Discussion on Older Object Ownership and Schemas............................................................ 40
Old Object Ownership.................................................................................................................... 40

Page | 3
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

New Schemas ................................................................................................................................ 41

T-SQL To Determine Ownership ......................................................................................................... 42
System Wide.................................................................................................................................. 42
Specific Database........................................................................................................................... 42
Default Schema ................................................................................................................................. 42
DDL Schema-Related Statements ....................................................................................................... 42
Example: Create a schema and use it to control database security ................................................ 43
ALTER SCHEMA .............................................................................................................................. 43
DROP SCHEMA............................................................................................................................... 43
ALTER AUTHORIZATION ................................................................................................................. 43
Ownership Chains.................................................................................................................................. 44
How Permissions Are Checked in a Chain ........................................................................................... 44
Example of Ownership Chaining ........................................................................................................ 45
Cross-Database Ownership Chaining.................................................................................................. 46
Potential Threats ........................................................................................................................... 46
Backup and Restore............................................................................................................................... 47
Things to consider in a multi-terabyte database that is used 24/7...................................................... 47
Backup Types..................................................................................................................................... 47
Full Backup .................................................................................................................................... 47
Example T-SQL of a Full Backup to Disk ...................................................................................... 48
Hypothetical Transaction Scenario ............................................................................................. 49
Example T-SQL to Restore from Disk .......................................................................................... 51
Differential backup ........................................................................................................................ 51
Example T-SQL of a Differential Backup to Disk .......................................................................... 51
Example T-SQL to Restore from Disk .......................................................................................... 53
Transaction log backup .................................................................................................................. 54
Example T-SQL Backup Transaction Log to Disk .......................................................................... 54
Log Chain ................................................................................................................................... 55
TRANSACTION LOG BACKUP FREQUENCY ................................................................................... 56
TAIL LOG BACKUPS..................................................................................................................... 56
Example T-SQL to recover a database to a point in time ............................................................. 57
COPY_ONLY backups...................................................................................................................... 58
Example T-SQL to Perform a COPY ONLY Transaction Log Backup............................................... 58

Page | 4
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server – Backup Syntax (T-SQL)................................................................................................... 58

Recovery Models ............................................................................................................................... 59
Schedule Backups Using SQL Server Agent (T-SQL Method) ............................................................... 60
Example T-SQL to Create an SQL Server Agent Job to do Backups................................................... 61
System Databases Backups ................................................................................................................ 62
Example T-SQL Server Agent Job to Backup MSDB System Database .............................................. 62
SQL Server – Understanding Indexes ..................................................................................................... 63
Pages................................................................................................................................................. 63
Clustered Indexes .............................................................................................................................. 64
Clustered Index Architecture.......................................................................................................... 65
Nonclustered indexes ........................................................................................................................ 66
NonClustered Index Architecture ................................................................................................... 66
Index Structure .................................................................................................................................. 66
Index Guidelines ................................................................................................................................ 69
High Availability ..................................................................................................................................... 70
Failover Clustering .............................................................................................................................. 71
Simple 2 Node Cluster Without MNS.............................................................................................. 72
SQL Server clustering advantages ................................................................................................... 73
SQL Server clustering limitations .................................................................................................... 73
Clustering in Windows Server 2003 and Above ............................................................................... 73
Quorum models............................................................................................................................. 74
Failover rules.................................................................................................................................. 75
Preferred Owners, Possible Owners, and Failback settings............................................................... 75
Failover Best Practice..................................................................................................................... 76
Benefits of Failover Clustering........................................................................................................ 77
Transaction Log Shipping ................................................................................................................... 78
Database Mirroring ........................................................................................................................... 79
Mirroring Process .......................................................................................................................... 81
High Performance (Asynchronous Mode) ................................................................................... 82
High Safety (Synchronous Mode) ............................................................................................... 83
Replication ........................................................................................................................................ 85
Replication Models ........................................................................................................................ 85
Forms of Replication ...................................................................................................................... 85

Page | 5
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

High Availability Summary ................................................................................................................. 89

Replication..................................................................................................................................... 89
Replication Topologies ................................................................................................................... 89
Clustering ...................................................................................................................................... 90
Transaction Log Shipping ............................................................................................................... 90
Mirroring ....................................................................................................................................... 91
Replication..................................................................................................................................... 91
SQL Files ................................................................................................................................................ 92
Primary data files............................................................................................................................... 92
Secondary data files........................................................................................................................... 92
Transaction Log files .......................................................................................................................... 92
SQL Filegroups ................................................................................................................................... 93
Best Practices for Files and Filegroups ............................................................................................... 94
SQL Partitioning..................................................................................................................................... 96
What is Partitioning in SQL Server? .................................................................................................... 96
Usefulness of Partitioning .................................................................................................................. 96
Horizontal partitioning....................................................................................................................... 97
Vertical partitioning ........................................................................................................................... 97
Partitioning Best Practices ................................................................................................................. 98
Encrypting Your Database...................................................................................................................... 99
Database Level Encryption – Transparent Data Encryption (TDE) ....................................................... 99
Implementing Transparent Data Encryption .................................................................................... 100
Detailed Operation of TDE Implementation ..................................................................................... 101
Considerations................................................................................................................................. 103
Monitoring SQL Server ........................................................................................................................ 104
Activity Monitor .............................................................................................................................. 104
The graphs show the following information ................................................................................. 105
Activity User Processes Pane ........................................................................................................... 105
Resource Waits Pane ................................................................................................................... 106
Data File I/O Pane ........................................................................................................................ 107
Recent Expensive Queries Pane ................................................................................................... 108
SQL Server Profiler........................................................................................................................... 109
Event ....................................................................................................................................... 109

Page | 6
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Server Side Trace ..................................................................................................................... 110

Database Engine Tuning Advisor ...................................................................................................... 110
Capabilities .............................................................................................................................. 110
Workload................................................................................................................................. 111
SQL Server System Views..................................................................................................................... 112
Catalog Views .................................................................................................................................. 112
Commonly Used Catalog Views .................................................................................................... 113
Examples ..................................................................................................................................... 114
How do I find the names of the columns of a specified table? .................................................. 114
How do I find the data types of the columns of a specified table? ............................................ 114
How do I find all the entities that have been modified in the last N days? ................................ 115
How do I find the columns of a foreign key for a specified table? ............................................. 115
Information Schema Views .............................................................................................................. 116
Examples ..................................................................................................................................... 116
How do I find the names of the columns of a specified table? .................................................. 116
How do I find the data types of the columns of a specified table? ............................................ 116
Dynamic Management Views .......................................................................................................... 117
Some dynamic management views .............................................................................................. 117
Examples ..................................................................................................................................... 118
How to find out some basic information about the server? ...................................................... 118

Page | 7
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

All notes and concepts are directed to OLTP relational databases.

DBA Tasks
Installation, configuration, upgrade, and migration Although system administrators are
generally responsible for the hardware and operating system on a given server, installation of the
database software is typically up to the DBA. This job role requires knowledge of the hardware
prerequisites for an efficient database server, and communicating those requirements to the system
administrator. The DBA then installs the database software and selects from various options in the
product to configure it for the purpose it is being deployed. As new releases and patches are
developed, it's the DBA's job to decide which are appropriate and to install them. If the server is a
replacement for an existing one, it's the DBA's job to get the data from the old server to the new
one.

Backup and recovery DBAs are responsible for developing, implementing, and periodically
testing a backup and recovery plan for the databases they manage. Even in large shops where a
separate system administrator performs server backups, the DBA has final responsibility for making
sure that the backups are being done as scheduled and that they include all the files needed to
make database recovery possible after a failure. When failures do occur, the DBA needs to know
how to use the backups to return the database to operational status as quickly as possible, without
losing any transactions that were committed. There are several ways the database can fail, and the
DBA must have a strategy to recover from each. From a business standpoint, there is a cost to doing
backups, and the DBA makes management aware of the cost/risk trade-offs of various backup
methods.

Database security Because databases centralize the storage of data, they are attractive targets
for hackers and even curious employees. The DBA must understand the particular security model
that the database product uses and how to use it effectively to control access to the data. The three
basic security tasks are authentication (setting up user accounts to control logins to the database),
authorization (setting permissions on various parts of the database), and auditing (tracking who did
what with the database).

Storage and capacity planning The primary purpose of a database is to store and retrieve data,
so planning how much disk storage will be required and monitoring available disk space are key DBA
responsibilities. Watching growth trends are important so that the DBA can advise management on
long-term capacity plans.

Page | 8
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Performance monitoring and tuning The DBA is responsible for monitoring the database
server on a regular basis to identify bottlenecks (parts of the system that are slowing down
processing) and remedy them. Tuning a database server is done on multiple levels. The capacity of
the server hardware and the way the operating system is configured can become limiting factors, as
can the database software configuration. The way the database is physically laid out on the disk
drives and the types of indexing chosen also have an effect. The way queries against the database
are coded can dramatically change how fast results are returned. A DBA needs to understand which
monitoring tools are available at each of these levels and how to use them to tune the system.
Proactive tuning is an attitude of designing performance into an application from the start, rather
than waiting for problems to occur and fixing them. It requires working closely with developers of
applications that run against the database to make sure that best practices are followed so good
performance will result.

Troubleshooting When things do go wrong with the database server, the DBA needs to know how
to quickly ascertain the problem and to correct it without losing data or making the situation worse.

High availability With the advent of the Internet, many databases that could have been available
only during the day are now required to be available 24 hours a day, 7 days a week. Web sites have
changed from static, pre-defined content to dynamically created content, using a database to create
the page layout at the time a page is requested. If the Web site is available 24x7, so must the
underlying database. Managing a database in this environment requires an understanding of which
types of maintenance operations can be done online (with the database available to users) and
which must be scheduled for a maintenance "window" when the database may be shut down. It also
requires planning for redundant hardware and/or software components, so that when one fails,
others will keep the total system available to its users. Techniques like online backups, clustering,
replication, and standby databases are all tools the DBA can use to ensure higher availability.

Data Extraction, Transformation, and Loading (ETL or DTS) In data warehouse

environments, a key task is efficiently loading the data warehouse or data mart with large volumes
of data extracted from multiple existing production systems. Often these production systems have
different formats than the standardized definitions in the data warehouse, so data must be
transformed (or "cleansed") before loading. Extracting the data may or may not be the DBA's
responsibility in a given company, but making sure what is extracted is useful is, and the DBA is a key
part of the team.

Very Large Databases (VLDBs) As companies have found more and more uses for database
technology, they tend to save more data. Managing a VLDB requires special skills of the DBA. The
time required to do simple operations like copying a table can be prohibitive unless done correctly.
The DBA needs to understand techniques like table partitioning, federated databases, or replication
to enable a database to scale to large sizes while still being manageable.

Page | 9
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server Editions (2012)

• Compact (32) – free, meant for mobile devices

• SQL Express (32) – free, for laptops/desktops to support distributed apps
• Workgroup (32/64) – Lowest cost, 2 processors, 4G RAM(64 bit), small to medium sized
businesses
• Web (32/64) – web site owners or hosting companies
• Standard (32/64) – contains high availability clustering and BI, small to medium sized businesses
• Enterprise, Evaluation, Developer (32/64) – higher end clustering, more advanced mirroring and
log shipping options, costly, evaluation works for 180 days, developer is for development, testing,
and demos and not to be used in production

Selected Features are Below (more detailed listing)

Feature Enterprise Standard Web Workgroup Express
Partitioning Yes
Data compression Yes
Multi-instance support Yes Yes Yes Yes Yes
Log shipping Yes Yes Yes Yes
Database mirroring Yes (Full) Yes (safety full Witness only Witness only Witness only
only)
Failover clustering Operating 2 nodes
system
maximum
Failover without client Yes Yes
configuration
Database snapshots Yes
Backup compression Yes
Transparent database Yes
encryption (TDE)
Merge replication Yes Yes Subscriber Subscriber Subscriber
only only only
Transactional Yes Yes Subscriber Subscriber Subscriber
replication only only only
Snapshot replication Yes Yes Subscriber Subscriber Subscriber
only only only

SQL Server supports 50 instances on a stand-alone server for all SQL Server editions. SQL Server
supports 25 instances on a failover cluster.

Asynchronous database mirroring is supported only by SQL Server 2005 Enterprise Edition SP1 and later
versions.

Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a
maximum of 16 failover cluster nodes.

Page | 10
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server Tools (selected)

SQL Server Management Studio (SSMS)

The main GUI-based management tool Many tasks, such as executing scripts, backing up and
restoring databases, and checking logs

SQL Server Configuration Manager

Used to configure network protocols, service accounts and startup status

SQL Server Profiler

Used for performance and troubleshooting tasks, detects blocked/deadlocked processes

Database Engine Tuning Advisor

Used to analyze a captured workload file and recommend various tuning changes

Page | 11
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

RAID Levels (Redundant Array of Independent Disks)

RAID 0

RAID 0 – Striping. No Mirror. No Parity

Following are the key points to remember for RAID level 0.

· Minimum 2 disks.
· Excellent performance (as blocks are striped).
· No redundancy (no mirror, no parity).
· Don’t use this for any critical system only for speed.
Definition of Striping: is the process of dividing a body of data into blocks and spreading the data blocks
across several partitions on several hard disks.

RAID 1

Raid 1 – No striping, Mirroring. No Parity

Following are the key points to remember for RAID level 1.

· Minimum 2 disks.
· Good performance (no striping. no parity).
· Excellent redundancy (as blocks are mirrored).

Page | 12
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

RAID 5

RAID 5 – Striping. No Mirror. Distributed Parity

Following are the key points to remember for RAID level 5.

· Minimum 3 disks.
· Good performance (as blocks are striped).
· Good redundancy (distributed parity).
· Best cost effective option providing both performance and redundancy. Use this for DB that
is heavily read oriented. Write operations will be slow.

RAID 10 (or 1+0)

RAID 1 RAID 0 RAID 1

RAID 10- Striping. Mirroring. No Parity

Following are the key points to remember for RAID level 10.

· Minimum 4 disks.
· Excellent performance (as blocks are striped)
· Excellent redundancy (as blocks are mirrored)
· If you can afford the dollar, this is the BEST option for any mission critical applications
(especially databases).

Page | 13
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

How Parity Works

The magic of parity is the binary operator "XOR". XOR is a binary operator that takes two inputs and
produces one output. The rules are:

1 xor 1 = 0

1 xor 0 = 1

0 xor 1 = 1

0 xor 0 = 0

In English xor means "if there's a difference, the result is 1".

Parity in RAID 5 involves reserving some space for parity information. Parity data is an additional digit of
information that helps you recover lost data.

1 1 parity 0

1 0 parity 1

0 1 parity 1

0 0 parity 0

Page | 14
Updated: November 11, 2019
 SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Parity is calculated across the cylinders. (It's not calculated within a single byte, the way it is on So
networks.) if

cylinder 1 on disk 1 looks like this:

10010011101110110001101...

And cylinder 1 on disk 2 looks like this:

10000001000000010000000...

Then the parity looks like this:

00010010101110100001101...

Here are the three cylinders again, but closer together so it makes sense:

10010011101110110001101...

10000001000000010000000...

00010010101110100001101...

Notice how there are an even number of 1s in each column of bits. The parity is even.

That is how RAID can recover from a lost hard disk. If you replace a disk, you can rebuild it because you
know that there should always be an even number of bits in each column.

If you lost the second disk, your data suddenly looks like this:

10010011101110110001101... DISK 1

_______________________... DISK 2 - failed

00010010101110100001101... DISK 3

You can rebuild the second disk by setting the 1 and 0 bits based on our parity rule. Here's an example
of regenerating the first four bits:

10010011101110110001101...

1000___________________...

00010010101110100001101...

All we do is repeat this calculation several billion times, and our data is rebuilt.

Page | 15
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Capacity Planning (OLTP)

OLTP applications usually have of a high volume of simple, short transactions from a large number of
users. Such transactions generate what’s referred to as random I/O, where the physical disks spend a
significant amount of time retrieving data from various parts of the disk.

When designing your disk and controller capacity it is more important to design for performance
first and then worry about capacity second.

1. The first thing we want to figure out is the number of database disk reads and writes generated
by an application per second (volume of workload).
• If a production system is already in place this value can easily be obtained by using
the Windows Performance Monitor. Use the tool to record the physical disk reads
and writes for each logical data or log volume during peak usage and then take the
average.
• For applications that are not yet in production use you can build a test setup and
estimate the disk read/writes. Do not forget to adjust for multi user access.

2. Second we need to know a value known as the I/O per second capacity (IOPS) of the individual
disks. The IOPS value of a disk depends on many factors, such as the type of disk, spin speed,
seek time, and I/O type. You could use the SQL Server tool such as SQLIO or you can just use a
value of 125 for the IOPS. The value of 125 is a reasonable value to use without getting an exact
value.
Now we are ready to calculate the number of disks and controllers we will need for maximum
throughput of our database application.

Page | 16
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Note: The calculations are for direct attached storage (DAS) solutions using traditional RAID storage.

To calculate the number of disks required for maximum throughput:

Required # Disks = (Reads/sec + (Writes/sec * RAID adjuster)) / Disk IOPS

Example: We need to design a RAID 10 storage system to support 1,200 reads per second and 400 writes
per second. Using our formula, the number of required disks (assuming 125 IOPS per disk) can be
calculated as follows:

Required # disks = (1200 + (400 * 2)) / 125 = 16 DISKS

Note: The doubling of the writes per second figure (400 * 2); in this example, we’re designing a RAID 10
volume (two physical writes are required for each logical. Also, note that this assumes the disk volume
will be dedicated to the application’s database. Combining multiple databases on the one disk will
obviously affect the calculations.

After determining the number of disks required, we need to ensure the I/O bus has adequate bandwidth
to support the number of disks calculated.

In a direct-attached SCSI disk enclosure, the typical bus used today is Ultra320, with a maximum
throughput of 320MB/second per channel.

Using our example 2,000 disk transfers per second (1200 + (400*2)) and 8K I/O transfers (the SQL Server
transfer size for random I/O – ie OLTP), the bandwidth requirements can be calculated as 2,000 times
8K, which is a total of 16MB/second, well within the capabilities of either Ultra320 SCSI (note the
Ultra320 has a 16 drive capacity).

Drive Capacity

The previous calculations and examples did not take into account drive capacity but only the number of
disks and disk controllers needed to obtain efficient throughput.

In our previous example (where we calculated the need for 16 disks), assuming we use 73GB disks, we
have a total available capacity of 1.1TB. Usable space, after RAID 10 is implemented, would come down
to around 500GB.

Page | 17
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Installing and Upgrading SQL Server

Service Accounts
• SQL Server will create several new Windows services during installation
• Each new service will require an account under which it will run.
• These accounts are created during installation, so they do not need to be created in advance.

Depending on which features are installed, SQL Server setup creates the following services for each
installed instance:

• SQL Server
➢ The service for the SQL Server relational Database Engine.

• SQL Server Agent

➢ Executes jobs, monitors SQL Server, fires alerts, and enables automation of some
administrative tasks.

• SQL Server Analysis Services

➢ Provides OLAP and data mining functionality for business intelligence applications.

• SQL Server Reporting Services

➢ Manages, executes, creates, schedules, and delivers reports.

• SQL Server Integration Services

➢ Provides management support for Integration Services package storage and execution.

IMPORTANT: Always use SQL Server Configuration Manager to change:

1. The account used by the SQL Server or SQL Server Agent services
2. The password for the account. (In addition to changing the account name, SQL Server
Configuration Manager performs additional configuration such as setting permissions
in the Windows Registry so that the new account can read the SQL Server settings.)

Other tools such as the Windows Services Control Manager can change the account name but
does not change associated settings in the registry.

Page | 18
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Account Privileges and Permissions

• Domain accounts
o Domain accounts are a better choice than local server accounts as they enable the SQL
instance to access other SQL Server instances and domain resources, as long as you grant
the necessary privileges.

• Nonprivileged accounts
o The service accounts should not be members of the domain administrators or local
administrator groups. The installation process will grant the service accounts the necessary
permissions to the file system and registry as part of the installation. Additional permissions
beyond those required to run SQL Server, such as access to a directory for data
import/export purposes, should be manually granted for maximum security.

• Additional account permissions

o Two recommended account permissions that SQL Server doesn’t grant to the SQL Server
service account are Perform Volume Maintenance Tasks, required for Instant Initialization,
and Lock Pages in Memory, required for 32-bit AWE-enabled systems and recommended for
64-bit systems.

Page | 19
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Checks and Conditions

• Collation
o SQL Server uses collations to determine how characters are sorted and compared.
o A collation is chosen during installation, and by default, SQL Server setup will select a
collation to match the server’s Windows collation.
• Storage
o You should also run SQLIO and SQLIOSIM to validate storage performance/validity and
driver/firmware versions are up to date.
• Directory Creation
o One of the installation steps is to specify locations for the database data, backup files,
and the tempdb data and log files. For maximum performance, create each of these
directories on partitions that are physically separate from each other—that is, they
don’t share the same underlying disks. Directories for these objects should be created
before installation.

Page | 20
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Network Security

• SQL Server should be secured behind a firewall, and unused network protocols should be
disabled.

Windows Version
• SQL Server 2008 requires at least Windows Server 2003 Service Pack 2
• Windows Server 2008 is recommended for the best performance and security.
• SQL Server should not be installed on a primary or backup domain controller; the server should
be dedicated to SQL Server.

Server Reboot
• SQL Server won’t install if there are any pending reboots; therefore, reboot the server prior to
installation.

Windows Management Instrumentation (WMI)

• This service must be installed and working properly before SQL Server can be installed. This
service is installed and running by default on both Windows Server 2003 and 2008.

Default and Named Instances

• Since SQL Server 2000, multiple instances (copies) of SQL Server can be installed on one server
• This allows us to control the amount of memory and CPU resources granted to each instance,
and the option to maintain different collation and service pack levels per instance.
During installation of SQL Server you will be given the opportunity to install a default instance or a
named instance.

• Only one default instance per server is allowed

• The Enterprise edition of SQL Server 2008 supports the installation of up to 50 named instances
• When connecting to a SQL Server, the instance name is specified in the connection string; for
example, HOMER\SALES will connect to the SALES instance on the HOMER server
• Connecting to the default instance requires the server name only – HOMER
• Each named Instance has a different location for program files and data files that is different
from that of the other Instances.
If you only have one instance then just install a default instance. When you start installing more
instances they must be named instance since only one default instance is allowed per SQL server.

Page | 21
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

GUI Installation
Start the installation by running [Link] from the SQL Server DVD. The setup process begins with a
check on the installed versions of the Windows Installer and the .NET Framework. If the required
versions are missing, the setup process offers the choice to install them. After these components are
verified (or installed), setup begins with the SQL Server Installation Center.

1. Click the Installation tab and then selecting the “New SQL Server stand-alone installation or add
features to an existing installation” option. Setup begins with a check for potential problems
that may prevent an installation from completing successfully. Address any problems preventing
installation, or click OK to continue.
2. Click Install to install the required setup support files.
3. In the Setup Support Rules screen, additional checks are processed before installation continues;
for example, the installer warns of the presence of Windows Firewall with a warning to unblock
appropriate ports. Review the warnings/failures (if any) and click Next.
4. The Installation Type screen lets you choose between installing a new instance or adding
features to an existing instance. Let’s choose the default (Perform a New Installation) and click
Next.
5. The Product Key screen asks you to select between a free edition (Enterprise Evaluation or
Express) or the option to enter a product key (supplied with the purchase of SQL Server). Make
the appropriate choice and click Next.
6. At the license terms screen, review the terms, check the “I accept the license terms” box, and
click Next.
7. On the Feature Selection screen, select the appropriate features and choose an installation
directory (or accept the default). You can display additional information on each feature by
clicking on the feature name. Click Next.

Page | 22
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

8. In the Instance Configuration screen, choose between a default or a named instance, enter the
instance ID and root directory (or accept the default settings), and click Next.

Page | 23
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

9. The Disk Space Requirements screen confirms the existence (or absence) of the necessary disk
space for installation to proceed. Review the summary of required and available space and click
Next to continue.

Page | 24
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

10. In the Server Configuration screen, enter the account names and passwords for the SQL
services, and optionally change the startup type. These accounts should be created as standard
privilege accounts prior to installation. Before clicking Next to continue, click the Collation tab to
review (and optionally modify) the default collation.

Page | 25
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

11. On the first tab of the Database Engine Configuration screen you select the authentication mode
for the instance: Windows or Mixed Mode. Windows authentication mode is the most secure
option, and is therefore the default (and recommended) option. If you choose Mixed Mode, be
sure to enter a strong system administration (SA) account password. Regardless of the selected
authentication mode, click either Add Current User or Add to select a user to add to the SQL
Server administration group. To continue, click the Data Directories tab.

Page | 26
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

12. The Data Directories tab, lets you specify default directories for data, log, tempdb, and backup
directories. As covered earlier, separate physical disks for these directories is important. After
entering the directory locations, click the FILESTREAM tab to continue.

13. Use the FILESTREAM tab to configure the instance for FileStream access. FileStream is a new
option for binary large object (BLOB) management. FileStream can be configured as a post
installation task. After reviewing the options on this tab, click Next.
14. In the final installation steps, you will:
a. Specify whether to send error reports and feature usage data to Microsoft
b. Review final installation rules checks
c. View the summary of installation choices, and click Install to execute the installation
based on the previous selections
d. View the installation progress
e. On the Completion screen, view the installation log file
f. When installation is complete, SQL Server saves the choices you made during setup in
[Link], which you’ll find in the C:\Program Files\Microsoft
SQLServer\100\Setup Bootstrap\Log\yyyymmdd_hhmmss directory. You can use this
file to confirm the installation proceeded with the required options, as well as use it as a
base for subsequent unattended installations via the command prompt.

Page | 27
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

15. After installation, you should set the size the tempdb database, and set the minimum and
maximum memory values.

Command Line Install

• In addition to using the GUI installation wizard, you can install SQL Server 2008 from the
command prompt.
• The [Link] is created at the end of a GUI-based installation. This file should be
preserved in its original state for later analysis, but you can make a copy and use it for
subsequent installations at the command prompt.
• Command prompt installations with configuration files are ideal in standardizing and
streamlining installation, particularly when installations are performed by those without the
appropriate SQL Server knowledge.
• When installing through the command prompt, SQL Server supports full quiet mode by using the
/Q parameter, or Quiet Simple mode by using the /QS parameter. The /QS switch only shows
progress, does not accept any input, and displays no error messages if encountered.

C:\[Link] /q /ACTION=Install /Configurationfile=c:\SQLIntsallTemplates\Sales2\[Link]

Page | 28
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

System Databases
Each SQL Server instance (including the Express Edition) has its own set of SQL Server system
databases. As such, if a single Windows server has multiple SQL Server instances installed, a change to
one system database only impacts the single instance, not all instances on the Windows server.

master
The master database contains all of the system level information for SQL Server – all of the logins, linked
servers, endpoints, and other system-wide configuration settings. The master database is also where
SQL Server stores information about the other databases on this instance and the location of their files.
If the master database is not present, SQL Server cannot start.

The Master database consists of two physical files, namely [Link] (data file) and [Link] (log
file).

model
The model database is used as a template whenever a new user database is created. You can change
most database properties, create users, stored procedures, tables, views, etc – whatever you do will be
applied to any newdatabases.

The nice thing is that you can create a guaranteed set of users, stored procedures, and options
(including collation options) by making changes to the model database. Once those changes are in place,
they are applied to every new database.

The Model database consists of two physical files namely [Link] (data file) and [Link] (log
file).

msdb
msdb is used by the SQL Server Agent, database mail, Service Broker, and other services. If you aren’t
actively working with things like jobs, alerts, log shipping, etc you can pretty safely ignore msdb… sort of.

One important item is that msdb holds backup history. Using the msdb tables (you can start by taking a
look at [Link]), it’s possible to determine when each database and filegroup was last
backed up.

The MSDB database consists of two physical files namely [Link] (data file) and [Link]
(log file).

Page | 29
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

tempdb
It is the workspace that SQL Server uses to store the intermediate results of query processing and
sorting. Outside of storing temporary results, tempdb is also used during snapshot isolation and for user
created temporary tables.

tempdb is re-created every time the SQL Server service is started. Any objects that you have created in
tempdb will be gone once the server restarts. If you want specific tables or stored procedures to always
be available in tempdb, you will need to add them to the model database or else use a stored procedure
to create them in tempdb when the SQL Server is started.

A properly sized and configured tempdb is vital for effective SQL Server performance. By default tempdb
starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized
too small, system resources will be used growing the tempdb file. This overhead can be avoided by
increasing the default size of tempdb.

The TempDB database consists of two physical files namely [Link] (data file) and [Link] (log
file).

You cannot backup or recover the tempdb.

resource database
The Resource database is a read only, hidden system database that contains all the SQL Server system
objects such as [Link] which are physically available only in the Resource database, even though
they logically appear in the SYS schema of every database.

Resource Database does not contain any user data or any user metadata. By design, Resource database
is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases
Node.

The physical file names of Resource database are [Link] and

[Link].

In a cluster environment, the Resource database basically exists in the \Data folder on a shared disk
drive. The ID for the Resource Database will be always 32767. The DBA shouldn’t be renaming or moving
the Resource Database file. If the files are renamed or moved from their respective locations then the
SQL Server will not start.

Another important thing to be considered is not to put the Resource Database files in compressed or
encrypted NTFS file system folders as it will hinder the performance and will also prevent upgrades.

Page | 30
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Configuration

A database should meet four key criteria:

• Security
• Availability
• Reliability
• Recoverability
You must balance between a convenient system and a secure system.

A convenient system, is one where developers, users, and database administrators are free to go about
their business unhindered, is usually an insecure one that often ends in disaster, intentional or
otherwise.

A secure system is one in which complaints about “how long it takes to get stuff done” aren’t
uncommon.

Authentication Modes
You have two options for login authentication:

• Windows Authentication Mode

o SQL Server will only accept connections from sessions that have a security token
assigned during a successful Windows login.
o This mode simplifies SQL Server login management (logins are already managed in
Windows)
o Windows logins can be configured for powerful password complexity and expiration
policies
o Passwords are never sent across the network
• SQL Server and Windows Authentication Mode (Mixed Mode)
o SQL Server authentication works by validating a username and password supplied by the
connecting process.
▪ For example, in Windows Authentication mode, a process connecting from the
AcmeInc\JDoe account would be automatically accepted if the Windows
account is defined as a SQL Server login with access to the appropriate
database. No password needs to be supplied because Windows has already
validated the login.
▪ But, a SQL Server authentication session needs a username and password for
validation by SQL Server.
o For applications using SQL Server authentication, SQL passwords are commonly included
in connection strings stored in clear text in configuration files or registry entries. This
makes them susceptible to prying eyes.
o Although the login credentials are encrypted during the SQL Server login process, the
encryption is typically performed using a self-signed certificate. While such encryption is
better than nothing at all, it’s susceptible to man-in-the-middle or identity spoofing
attacks.

Page | 31
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

o While password expiration and complexity policies are available in SQL Server
authentication, such policy enforcement isn’t mandatory, and each SQL Server’s policies
could potentially have varying degrees of strength, compared to a policy defined and
enforced at a domain level using Windows authentication
o This mode is most often used for legacy systems and applications

Protocol Selection
To connect to SQL Server Database Engine you must have a network protocol enabled.

• Microsoft SQL Server can service requests on several protocols at the same time.
• Clients connect to SQL Server with a single protocol.
• If the client program does not know which protocol SQL Server is listening on, configure the
client to sequentially try multiple protocols.
• Use SQL Server Configuration Manager to enable, disable, and configure network protocols.

Shared Memory
Clients using the shared memory protocol can only connect to a SQL Server instance running on the
same computer, it is not useful for most database activity (use for trouble shooting when you suspect
other protocols are configured improperly).

Named Pipes
Named Pipes is a protocol developed for local area networks and should not be used over WANs.

TCP/IP
TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected
networks of computers that have diverse hardware architectures and various operating systems. This
will be the most common protocol today.

VIA
A specialized protocol developed for use with specific hardware.

The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL
Server. Avoid using this feature in new development work, and plan to modify applications that
currently use this feature.

Page | 32
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• You should disable all unused protocols

• Using a single network protocol is common and preferred

You enable and disable network protocols using the SQL Server Configuration Manager.

• An upgraded instance of SQL Server will preserve the pre-upgrade

network configuration settings.
• Banyan VINES, Multiprotocol, AppleTalk, and NWLink IPX/ SPX are no
longer supported in SQL Server 2008. Clients previously connecting
with these protocols must select a different protocol to connect to SQL
Server.

Static and Dynamic TCP Ports

• Each SQL Server instance “listens” for client requests on a unique TCP/IP address/port number
combination.
o To support the installation of multiple named instances per server, SQL Server uses
dynamic ports
o Rather than having to manually assign each named instance a unique port number, you
can use dynamic ports. SQL Server will automatically choose a free port number when
an instance starts up.
o By default, each named SQL Server instance is configured to use dynamic TCP/IP ports.
This means that each time a named instance of SQL Server is started, the TCP port used
may be different. (default port number is 1433 and is used for default instances)

Firewalls
• Dynamic ports present a problem for firewall configuration.
• An attempt to secure a SQL Server instance behind a firewall by only opening a specific port
number will obviously fail if the port number changes, due to the dynamic port option.
• Static ports are the best (and most secure) choice when SQL Server is behind a firewall.

Page | 33
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• When assigning a static TCP port, avoid using ports currently (and commonly) used by other
services and applications.
o Refer to: [Link]/assignments/port-numbers

The SQL Server Browser service responds to client connection requests with the port number
that the requested instance is running on, thereby avoiding the need for client applications to be
aware of the port number an instance is currently using.

You can set an instance to use a static TCP port by using the SQL Server Configuration Manager tool.
Simply delete the TCP Dynamic Ports entry and enter a port number in TCP Port.

• The SQL Server Browser service runs on port 1434.

o If the browser service is stopped, or port 1434 is closed on the firewall, the port number
needs to be included in the connection request.
o For example, to connect to a SQL Server instance called SALES running on port 35100 on
the HOMER server, we’d connect using: HOMER\Sales,35100

Page | 34
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Encryption
• Transparent Data Encryption (TDE). TDE automatically encrypts and decrypts data as it’s read
from and written to the database without the need for any application changes.
• Even with TDE the network transmission of data is unencrypted, meaning packet sniffers could
be used to intercept data.
• For maximum data security, the network transmission of SQL Server data can be encrypted
using either Internet Protocol Security (IPSec – done at OS level – easiest, no SQL Server
configuration needed) or Secure Sockets Layer (SSL).

Implement Least Privilege

This involves reducing the permissions of user and service accounts to the absolute minimum required.

You should achieve this goal from four perspectives:

1. Separate Windows and database administrator privileges

In most cases,
• DBAs should not be local administrators
• Windows administrators should not be SQL Server sysadmins.
• The SQL Server service accounts should not be members of the local
administrators group.
2. Reduce the permissions of the SQL Server service accounts
• During installation, SQL Server will assign the necessary file, registry, and system
permissions to the accounts nominated for the services.

3. Use proxies and credentials to limit the effective permissions of SQL Server Agent jobs
• An SQL Server Agent proxy account defines a security context in which a job step can
run.
4. Use role-based security to simplify and tighten permissions management

Remember, if the security account needs to be changed post-installation, the SQL

Server Configuration Manager tool should be used.

DATABASE ROLES – contains users (Windows or SQL Server logins) and is assigned permissions
to objects (schema, tables, views, stored procedures, and so forth) within a database.
APPLICATION ROLES – In some cases, access to database objects is provided and managed as
part of an application rather than direct database permissions granted to users. In such cases,
there’s typically an application management function where users are defined and managed on
an administration screen. The application role is granted the superset of the permissions
required, with the individual user permissions then managed within the application itself.
FIXED SERVER ROLES – In environments with lots of server instances and many DBAs, some
sites prefer to avoid having all DBAs defined as members of the sysadmin role and lean toward a
more granular approach whereby some DBAs are allocated a subset of responsibilities. SQL
Server provides a number of fixed server roles in addition to the sysadmin role, which grants the

Page | 35
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

highest level of access to the server instance. An example of a fixed server role is the
processadmin role, used to grant users permissions to view and kill running server processes,
and the dbcreator role, used to enable users to create, drop, alter, and restore databases.
FIXED DATABASE ROLES – fixed database roles come with predefined permissions that enable
a subset of database permissions to be allocated to a specific user. Commonly used fixed
database roles are the db_datareader and db_datawriter roles, used to grant read and
add/delete/modify permissions respectively to all tables within a database.

More on Roles can be found in the following links

Server Level Roles

Database level Roles
Application Roles

Page | 36
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Accounts

Server Login
A server login is a security principal, or an entity that can be authenticated by a secure system. Users
need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a
domain user or a Windows domain group) or you can create a login that is not based on a Windows
principal (such as an SQL Server login).

As a security principal, permissions can be granted to logins. The scope of a login is the whole Database
Engine. To connect to a specific database on the instance of SQL Server, a login must be mapped to a
database user. Permissions inside the database are granted and denied to the database user, not the
login.

Server login link

Database User
A user is a database level security principal. Server Logins must be mapped to a database user to
connect to a database. A server login can be mapped to different databases as different users but can
only be mapped as one user in each database.
As a security principal, permissions can be granted to users. The scope of a user is the database. To
connect to a specific database on the instance of SQL Server, a login must be mapped to a database
user. Permissions inside the database are granted and denied to the database user, not the login.
Database Users link

Page | 37
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Roles

Database Level Roles

To easily manage the permissions in your databases, SQL Server provides several roles which are
security principals that group other principals. They are like groups in the Microsoft Windows operating
system. Database-level roles are database-wide in their permissions scope.

There are two types of database-level roles: fixed-database roles that are predefined in the database
and user-defined database roles that you can create.

Fixed-Database Roles

Name Description
Members of the db_owner fixed database role can perform all configuration
and maintenance activities on the database, and can also drop the database
db_owner in SQL Server. (In SQL Database and SQL Data Warehouse, some
maintenance activities require server-level permissions and cannot be
performed by db_owners.)
Members of the db_securityadmin fixed database role can modify role
db_securityadmin membership and manage permissions. Adding principals to this role could
enable unintended privilege escalation.
Members of the db_accessadmin fixed database role can add or remove
db_accessadmin access to the database for Windows logins, Windows groups, and SQL
Server logins.
Members of the db_backupoperator fixed database role can back up the
db_backupoperator
database.
Members of the db_ddladmin fixed database role can run any Data
db_ddladmin
Definition Language (DDL) command in a database.
Members of the db_datawriter fixed database role can add, delete, or
db_datawriter
change data in all user tables.
Members of the db_datareader fixed database role can read all data from
db_datareader
all user tables.
Members of the db_denydatawriter fixed database role cannot add,
db_denydatawriter
modify, or delete any data in the user tables within a database.
Members of the db_denydatareader fixed database role cannot read any
db_denydatareader
data in the user tables within a database.

Page | 38
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Permissions assigned to the fixed-database roles:

Page | 39
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Schemas
The name of a database object can generally contain four parts in the form:

server_name.db_name.schema_name.object_name

The permissions assigned to the fixed-database roles cannot be changed.

• server_name = name of the server to which the database object belongs

• db_name = name of database to which the database object belongs
• schema_name is the name of the schema to which the object belongs
• object_name is the name of the database object (ie: tables, indexes, stored proc., etc.)
Note: Table names, combined with the schema name, must be unique within the database (ie: You can
have the same table name in different schemas but not in the same schema). Similarly, column names
must be unique within the table.

What do they do?

• Schemas simplify the relationship between users and objects.
• Logically group database objects together
• Assign permission to users or groups of users based on schemas
• Can provide naming collision protection

Side Bar Discussion on Older Object Ownership and Schemas

Old Object Ownership
Up to and including SQL Server 2000 schemas did not exist. Only Object Ownership existed.

When an object is created in SQL Server 2000 or earlier, the object must have an owner. Most of the
time, the owner is “dbo”, also known as the database owner. But it is possible that an object can be
owned by any user account in the database.

If you use SSMS you can see who owns an object by the qualifier. For instance if the name of a table is
called orders and is owned by dbo you will see the table name as [Link]. If the table’s ownership is
transferred to user Bob, the table will now be named [Link].

The object ownership is dependent on who created it. It is also possible for someone in the db_owner
fixed server role to create an object owned by any user in the database. Only user accounts in the
db_owner fixed server role can create objects owned by dbo.

Users in the database will be able to access any object owned by dbo without specifying the owner as
long as the user has appropriate permission.

If an object is owned by an account other than dbo, the ownership must be transferred to another user
if the original account is to be deleted. For example, if a non-dbo database user called “ted” creates the

Page | 40
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

sales table, it will be called [Link]. In order for users other than Ted to see the table, it must be
referred to by the fully qualified name. If Ted leaves the company or department and his account must
be removed from the database, the ownership of the table must be transferred to another user account
using the sp_changeobjectowner stored procedure before Ted’s account can be removed.

If the table has been used in applications or referred to in any definitions such as stored procedures,
changing the owner will now break all the code. If the dbo had owned the table from the start, there
would have been no problem removing Ted’s account. The code would not have to use the fully
qualified name, though there is a slight performance gain in doing so and is considered a best practice.

New Schemas

• Tables are organized by department or function.

• Objects are not tied to any user accounts; you do not have to worry about changing the owner
of objects when an account is to be removed.
• Schemas can be used to simplify managing permissions on tables and other objects.
• The schema has an owner, but the owner is not tied to the name. So, if an account owns a
schema and the account must be removed from the database, the owner of the schema can be
changed without breaking any code.
***** End Side Bar Discussion *****

Page | 41
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Database Engine supports named schemas using the notion of a principal, an entity that can access
objects. A principal can be either of the following:

• An indivisible principal (user)

• A group principal (users assigned to a group)
An indivisible principal represents a single user, such as a login or Windows user account. A group
principal can be a group of users, such as a role or Windows group. Principals are ownerships of
schemas, but the ownership of a schema can be transferred easily to another principal and without
changing the schema name.

T-SQL To Determine Ownership

System Wide
select suser_sname(owner_sid) from [Link]

Specific Database
select suser_sname(owner_sid) from [Link] where name = ‘YourDatabaseName’

Default Schema
• Each database has a default schema, which is used to resolve the names of objects that are
referred to without their fully qualified names.

• The default schema specifies the first schema that will be searched by the database server when
it resolves the names of objects.

• The default schema can be set and changed using the DEFAULT_SCHEMA option of the CREATE
USER or ALTER USER statement.

• If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.

DDL Schema-Related Statements

• CREATE SCHEMA
• ALTER SCHEMA
• DROP SCHEMA
• ALTER AUTHORIZATION

Page | 42
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example: Create a schema and use it to control database security

USE AdventureWorks2012;
GO
CREATE SCHEMA my_schema AUTHORIZATION john
GO
CREATE TABLE product
(product_no CHAR(10) NOT NULL UNIQUE,
product_name CHAR(20) NULL,
price MONEY NULL);
GO
CREATE VIEW product_info
AS SELECT product_no, product_name
FROM product;
GO
GRANT SELECT TO bob;
DENY UPDATE TO bob;

Note: The CREATE SCHEMA statement is atomic. In other words, if any error occurs during the execution
of a CREATE SCHEMA statement, none of the Transact-SQL statements specified in the schema will be
executed.

Note: You use the AUTHORIZATION option to define the principal of a schema. A database-level
principal could be a database user, role, or application role.

ALTER SCHEMA
The ALTER SCHEMA statement transfers an object between different schemas of the same database.

Example: Transfer the Address table from the person schema into the humanresources schema.

USE AdventureWorks2008;
ALTER SCHEMA humanresources TRANSFER [Link];

Note: This statement will modify the ownership of an entity unless the ownership was explicitly set.

DROP SCHEMA
Removes a schema from the database. You cannot drop the schema if the schema contains any objects.

ALTER AUTHORIZATION
Change the ownership of a schema by using the ALTER AUTHORIZATION statement

Example: Change ownership of the humanresources schema to bob.

USE AdventureWorks2008;
ALTER AUTHORIZATION ON SCHEMA :: humanresources TO bob;

Page | 43
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Ownership Chains
When multiple database objects access each other sequentially, the sequence is known as a chain.
Although such chains do not independently exist, when SQL Server traverses the links in a chain, SQL
Server evaluates permissions on the constituent objects differently than it would if it were accessing the
objects separately. These differences have important implications for managing security.

Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting
permissions on one object, such as a view. Ownership chaining also offers a slight performance
advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain,

• SQL Server first compares the owner of the object to the owner of the calling object. This is the
previous link in the chain.
• If both objects have the same owner, permissions on the referenced object are not evaluated.
If the current object has a different owner than the calling object then permissions will be
checked.

Page | 44
Updated: November 11, 2019
 SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example of Ownership Chaining

In the following illustration, the July2003 view is owned by Mary. She has granted to Alex permissions
on the view. He has no other permissions on database objects in this instance. What occurs when Alex
selects the view?

1. Alex executes SELECT * on the July2003 view. SQL Server checks permissions on the view and confirms
that Alex has permission to select on it.
2. The July2003 view requires information from the SalesXZ view. SQL Server checks the ownership of the
SalesXZ view. Because this view has the same owner (Mary) as the view that calls it, permissions on
SalesXZ are not checked. The required information is returned.
3. The SalesXZ view requires information from the InvoicesXZ view. SQL Server checks the ownership of
the InvoicesXZ view. Because this view has the same owner as the previous object, permissions on
InvoicesXZ are not checked. The required information is returned. To this point, all items in the
sequence have had one owner (Mary). This is known as an unbroken ownership chain.
4. The InvoicesXZ view requires information from the AcctAgeXZ view. SQL Server checks the ownership of
the AcctAgeXZ view. Because the owner of this view differs from the owner of the previous object (Sam,
not Mary), full information about permissions on this view is retrieved. If the AcctAgeXZ view has
permissions that enable access by Alex, information will be returned.
5. The AcctAgeXZ view requires information from the ExpenseXZ table. SQL Server checks the ownership
of the ExpenseXZ table. Because the owner of this table differs from the owner of the previous object
(Joe, not Sam), full information about permissions on this table is retrieved. If the ExpenseXZ table has
permissions that enable access by Alex, information is returned.

Page | 45
Updated: November 11, 2019
 SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

6. When the July2003 view tries to retrieve information from the ProjectionsXZ table, the server first
checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If cross-
database chaining is enabled, the server will check the ownership of the ProjectionsXZ table. Because
this table has the same owner as the calling view (Mary), permissions on this table are not checked. The
requested information is returned.

Cross-Database Ownership Chaining

SQL Server can be configured to allow ownership chaining between specific databases or across all
databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by
default, and should not be enabled unless it is specifically required.

Potential Threats

Ownership chaining is very useful in managing permissions on a database, but it does assume that
object owners anticipate the full consequences of every decision to grant permission on a securable.

In the previous illustration,

• Mary owns most of the underlying objects of the July 2003 view.

Page | 46
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• Because Mary has the right to make objects that she owns accessible to any other user, SQL
Server behaves as though whenever Mary grants access to the first view in a chain, she has
made a conscious decision to share the views and table it references.
• In real life, this might not be a valid assumption. Production databases are far more complex
than the one in the illustration, and the permissions that regulate access to them rarely map
perfectly to the administrative structures of the organizations that use them.

You should understand that members of highly privileged database roles can use cross-database
ownership chaining to access objects in databases external to their own. For example,

• if cross-database ownership chaining is enabled between database A and database B,

• a member of the db_owner fixed database role of either database can spoof her way into the
other database.
• The process is simple:
o Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart
already exists as a user in database B.
o Diane then creates an object (owned by Stuart) in database A that calls any object
owned by Stuart in database B.
o Because the calling and called objects have a common owner, permissions on the
object in database B will not be checked when Diane accesses it through the object she
has created.

Backup and Restore

Things to consider in a multi-terabyte database that is used 24/7

• How long does the backup take, and what impact does it have on users?
• Where are the backups stored, and what is the media cost?
• How much of the database changes each day?
• If the database failed partway through the day, how much data would be lost if the only
recovery point was the previous night’s backup?

Backup Types

Full Backup

The simplest type of database backup. A full backup is a complete copy of the database at a given time.

Unlike with a normal file backup, you can not back up a database by simply backing up the
underlying .mdf and .ldf files.

One of the classic mistakes made by organizations without appropriate DBA knowledge is using a backup
program to back up all files on a database server based on the assumption that the inclusion of the
underlying database files (.mdf and .ldf) in the backup will be sufficient for a restore scenario. Not only

Page | 47
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

will this backup strategy be unsuccessful, but those who use such an approach usually fail to realize that
fact until they try to perform a restore.

For a database backup to be valid, you must use the BACKUP DATABASE command or one of its GUI
equivalents.

Example T-SQL of a Full Backup to Disk

BACKUP DATABASE [AdventureWorks2012]

TO DISK = 'G:\SQL Backup\[Link]’
WITH INIT
Or (multi file backup for performance)
BACKUP DATABASE [ADVENTUREWORKS2012]
TO DISK = ‘G:\SQL BACKUP\ADVENTUREWORKS_1.BAK’,
DISK = ‘H:\SQL BACKUP\ADVENTUREWORKS_2.BAK’,
DISK = ‘I:\SQL BACKUP\ADVENTUREWORKS_3.BAK

The full backup is restored using the WITH NORECOVERY option. This leaves the database in a recovering
state, and thus able to restore additional backups. We follow the restore of the full backup with the
differential restore.

Page | 48
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

When you issue a RESTORE DATABASE or RESTORE LOG command the WITH RECOVERY option is used by
default. This option does not need to be specified for this action to take place.

If you restore a "Full" backup the default setting it to RESTORE WITH RECOVERY, so after the database
has been restored it can then be used by your end users.
If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY
option for each restore except the last.

If your database is still in the restoring state and you want to recover it without restoring additional
backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to
use.

• You can perform backups in SQL Server while the database is in use and is being modified by
users.
• In order for the backup to be restored as a transactionally consistent database, SQL Server
includes part of the transaction log in the full database backup.

Hypothetical Transaction Scenario

Transaction A completed before the transaction log got included and therefore Transaction A
will be rolled forward or applied when the restore happens.

Transaction B never finished before the transaction log was included and any statements
that were not committed will be rolled back when restored.

By including parts of the transaction log, the restore process is able to roll forward committed changes
and roll back uncommitted changes as appropriate.

Page | 49
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• It’s important to point out here that even though parts of the transaction log are included in a
full backup, this doesn’t constitute a transaction log backup.
• A classic mistake made by inexperienced SQL Server DBAs is never performing transaction log
backups because they think a full backup will take care of it.
• A database in full recovery mode will maintain entries in the transaction log until it’s backed up.
• If explicit transaction log backups are never performed, the transaction log will continue
growing forever (until it fills the disk).

Page | 50
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example T-SQL to Restore from Disk

RESTORE DATABASE [AdventureWorks2012]

FROM DISK = ‘G:\SQL Backup\[Link]'
WITH REPLACE

Differential backup
While a full backup represents the most complete version of the database, performing full backups on a
nightly basis may not be possible (or desirable) for a variety of reasons.

If only using full backups, when a full backup is restored changes since the full backup are
lost.

A differential backup, is one that includes all database changes since the last full backup.

Example T-SQL of a Differential Backup to Disk

BACKUP DATABASE [AdventureWorks2012]

TO DISK = ‘G:\SQL Backup\[Link]'
WITH DIFFERENTIAL, INIT

Page | 51
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

A classic backup design is one in which a full backup is performed weekly, with nightly differential
backups.

• When restoring a differential backup, the corresponding full backup, known as the base backup,
needs to be restored with it.
• In the example above, if we needed to restore the database on Friday morning, the full backup
from Sunday, along with the differential backup from Thursday night, would be restored.

Page | 52
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example T-SQL to Restore from Disk

-- Restore from Disk. Leave in NORECOVERY state for subsequent restores

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = 'G:\SQL Backup\[Link]'
WITH NORECOVERY, REPLACE
GO
-- Complete the restore process with a Differential Restore
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = ‘G:\SQL Backup\[Link]'
GO

Differential backups grow in size and duration the further they are from their
corresponding full backup.

Page | 53
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Transaction log backup

Each database has its own transaction log, which SQL Server uses for several purposes, including the
following:

1. The log records each database transaction, as well as the individual database modifications
made within each transaction.
2. If a transaction is canceled before it completes, either at the request of an application or due to
a system error, the transaction log is used to undo, or roll back, the transaction’s modifications.
3. A transaction log is used during a database restore to roll forward completed transactions and
roll back incomplete ones. This process also takes place for each database when SQL Server
starts up.
4. The transaction log plays a key role in log shipping and database mirroring.

Regular transaction log backups are crucial in retaining the ability to recover a database to a point in
time.

Example T-SQL Backup Transaction Log to Disk

BACKUP LOG [AdventureWorks2012]

TO DISK = 'G:\SQL Backup\[Link]'
WITH INIT

If only using full backups and differential backups you can only restore changes made to the
database since the differential backup will be lost.

Page | 54
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Log Chain
Each transaction log backup forms part of what’s called a log chain. The head of a log chain is a full
database backup, performed after the database is first created, or when the database’s recovery model
is changed.

• To restore a database to a point in time, an unbroken chain of transaction logs is required, from
a full backup to the required point of recovery.
• For example, if we lost backup 4, we would not be able to restore past the end of
backup 3 at 6 a.m. Tuesday. Attempting to restore the transaction log from log backup
5 would result in an error message.

❖ In addition to protecting against potential data loss, regular log backups limit
the growth of the log file.
❖ With each transaction log backup, certain log records are removed, freeing up space for new log
entries.
❖ As mentioned previously the transaction log in a database in full recovery mode will continuing
growing indefinitely until a transaction log backup occurs.

Page | 55
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

TRANSACTION LOG BACKUP FREQUENCY

• Frequent transaction log backups reduce the exposure to data loss.

o Assuming a transaction log backup was performed 15 minutes before the disk
destruction, the maximum data loss would be 15 minutes.
o In contrast, if transaction log backups are only performed once a day (or longer), the
potential for data loss is large, particularly for databases with a high rate of change.
• The more frequent the log backups, the more restores will be required in a recovery situation. In
order to recover up to a given point, we need to restore each transaction log backup between
the last full (or differential) backup and the required recovery point.
o If transaction log backups were taken every minute, and the last full or differential
backup was 24 hours ago, there would be 1,440 transaction log backups to restore.
• You need to get the balance right between potential data loss and the complexity of the restore.
The determining factors are the rate of database change and the maximum allowed data loss.

TAIL LOG BACKUPS

• When restoring a database that’s currently attached to a server instance, SQL Server will
generate an error (Unless the WITH REPLACE option is used) unless the tail of the transaction
log is first backed up.
• The tail refers to the section of log that hasn’t been backed up yet— that is, new transactions
since the last log backup.
• A tail log backup is performed using the WITH NORECOVERY option, which immediately places
the database in the restoring mode, guaranteeing that the database won’t change after the tail
log backup and thus ensuring that all changes are captured in the backup (ie: puts database in
an offline state).

Page | 56
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example T-SQL to recover a database to a point in time

-- Backup the tail of the transaction log

BACKUP LOG [AdventureWorks2012]
TO DISK = ‘G:\SQL Backup\[Link]'
WITH INIT, NORECOVERY

-- Restore the full backup

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = 'G:\SQL Backup\[Link]'
WITH NORECOVERY
GO
-- Restore the differential backup
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = 'G:\SQL Backup\[Link]'
WITH NORECOVERY
GO

-- Restore the transaction logs -------- REPEAT this for all transaction logs -------------------
RESTORE LOG [AdventureWorks2012]
FROM DISK = 'G:\SQL Backup\[Link]'
WITH NORECOVERY
GO
-- Restore the final tail backup, stopping at 11.05AM --- Note: The flag WITH RECOVERY puts database back online -
RESTORE LOG [AdventureWorks2012]
FROM DISK = 'G:\SQL Backup\[Link]'
WITH RECOVERY, STOPAT = 'June 24, 2008 11:05 AM'
GO

Page | 57
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

COPY_ONLY backups

Remember a log chain as the sequence of transaction log backups from a given base. The base for a
transaction log chain, as with differential backups, is a full backup. In other words, before restoring a
transaction log or differential backup, we first restore a full backup that preceded the log or differential
backup.

Take the example presented earlier, where we perform a full backup on Sunday night, nightly
differential backups, and six hourly transaction log backups. To recover to 6 p.m. on Tuesday, we’d
recover Sunday’s full backup, followed by Tuesday’s differential and the three transaction log backups
leading up to 6 p.m.

• Now let’s assume that a developer, on Monday morning, made an additional full backup, and
moved the backup file to their workstation.
• The differential restore from Tuesday would now fail. WHY???
o A differential backup uses a Differential Changed Map (DCM) to track which extents
have changed since the last full backup. The DCM in the differential backup from
Tuesday now relates to the full backup made by the developer on Monday morning.
o In our restore code, we’re not using the full backup from before the failure.
There are a few ways around this problem.

1. We have an unbroken transaction log backup sequence, so we can always restore the full
backup, followed by all of the log backups since Sunday.
2. We can track down the developer and ask him for the full backup and hope that he hasn’t
deleted it.
3. A COPY_ONLY backup, supported for both full and transaction log backups, is used in situations
in which the backup sequence shouldn’t be affected.
o If the developer performed the Monday morning full backup as a COPY_ONLY backup,
the DCM for the Tuesday differential would still be based on our Sunday full backup.
o Similarly, a COPY_ONLY transaction log backup, as in this example, will back up the log
without truncation, meaning that the log backup chain will remain intact without
needing the additional log backup file:

Example T-SQL to Perform a COPY ONLY Transaction Log Backup

BACKUP LOG [AdventureWorks2012]

TO DISK = 'G:\SQL Backup\AdventureWorks-Trn_copy.bak'
WITH COPY_ONLY

SQL Server – Backup Syntax (T-SQL)

[Link]

Page | 58
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Recovery Models
[Link]
Recovery Description Work loss exposure Recover to point in
model time?
Simple No log backups. Changes since the most Can recover only to
recent backup are the end of a backup.
Automatically reclaims log space to unprotected. In the
keep space requirements small, event of a disaster,
essentially eliminating the need to those changes must be
manage the transaction log space. redone.

Operations that require transaction log

backups are not supported by the
simple recovery model. The following
features cannot be used in simple
recovery mode:

-Log shipping

-Always On or Database mirroring

-Media recovery without data loss

-Point-in-time restores
Full Requires log backups. Normally none. Can recover to a
specific point in time,
No work is lost due to a lost or damaged If the tail of the log is assuming that your
data file. damaged, changes since backups are complete
the most recent log up to that point in
Can recover to an arbitrary point in time backup must be redone. time.
(for example, prior to application or
user error).
Bulk Requires log backups. If the log is damaged or Can recover to the end
logged bulk-logged operations of any backup. Point-
An adjunct of the full recovery model occurred since the most in-time recovery is not
that permits high-performance bulk recent log backup, supported.
copy operations. changes since that last
backup must be redone.
Reduces log space usage by using
minimal logging for most bulk Otherwise, no work is
operations. lost.

Page | 59
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Schedule Backups Using SQL Server Agent (T-SQL Method)

Create an SQL Server Agent Job to automate our backups.

• Ensure the SQL Server Agent service is set to automatic start (by default it is set to
manual)
• To create an SQl Server Agent job requires the following steps
1. Execute sp_add_job to create a job.
2. Execute sp_add_jobstep to create job steps.
3. Execute sp_add_schedule to create a schedule.
4. Execute sp_attach_schedule to attach a schedule to the job.
5. Execute sp_add_jobserver to set the server for the job.
• Create a Transact-SQL script file by using Notepad
• Save the file with a .sql extension

Page | 60
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Example T-SQL to Create an SQL Server Agent Job to do Backups

USE msdb ;
GO

-- add a job
EXEC dbo.sp_add_job
@job_name = 'FullBackup' ;
GO

-- add job steps to job

EXEC sp_add_jobstep
@job_name = 'FullBackup',
@step_name = 'Weekly Full Backup',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE RobTest TO DISK =
"E:\data\rob\Seneca\sqlServer2012\sampleSQLData\[Link]" WITH INIT‘,
@retry_attempts = 5,
@retry_interval = 5 ;
GO

-- create a schedule for this job, backups occur once a week each Friday at 11:59 pm
EXEC sp_add_schedule
@schedule_name = 'WeeklyBackup' ,
@freq_type = 8,
@freq_interval = 32,
@active_start_time = 235900 ;
GO

-- attach the schedule to the job

EXEC sp_attach_schedule
@job_name = 'FullBackup',
@schedule_name = 'WeeklyBackup' ;
GO

• To execute the previous script you run sqlcmd

o Sqlcmd -i C:\scriptdirectory\[Link]
▪ This will execute the script file called [Link] stored in the specified
directory on the local server
o sqlcmd -S servername\instanceName -i C:\scriptdirectory\[Link]
▪ This will execute the script file called [Link] stored in the specified
directory on a remote server (change servernameandinstancename)

The msdb system database is used by SQL Server Agent for scheduling alerts and jobs and by
other features such as Service Broker and Database Mail.

• sqlcmd link

Page | 61
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

System Databases Backups

• SQL Server is dependant on the System Databases for functionality.
• Database Administrators should make sure that all the system databases are part of their day to
day backups.
• Most importantly, database administrators should take full backup of all the system databases
immediately after they have applied any Hotfixes or Service Packs.
• Backups of system databases will let you restore and recover the SQL Server system in the event
of system failure, such as the loss of hard disk, database corruption, hardware/operating system
crashes or any natural disaster.
• As a DBA you need to make sure that the system databases are backed up regularly along with
all the user databases

Example T-SQL Server Agent Job to Backup MSDB System Database

USE msdb ;
GO

-- add a job
EXEC dbo.sp_add_job @job_name = ‘MSDBBackupJob' ;
GO

-- add job steps to job

EXEC sp_add_jobstep
@job_name = ‘MSDBBackupJob',
@step_name = ‘MSDB Full Backup',
@subsystem = 'TSQL',
@command = ' BACKUP DATABASE [MSDB] TO DISK
= ”E:\data\rob\Seneca\sqlServer2012\[Link]” WITH INIT’,
@retry_attempts = 5,
@retry_interval = 5 ;
GO

-- create a schedule for this job, backups occurs once a week each Friday at 11:59 pm
EXEC sp_add_schedule
@schedule_name = ‘MSDBBackup' ,
@freq_type = 8,
@freq_interval = 32,
@active_start_time = 235900 ;
GO

-- attach the schedule to the job

EXEC sp_attach_schedule
@job_name = ’MSDBBackup',
@schedule_name = ‘MSDBBackup' ;
GO

• You would do the same for the Master and Model system databases

Page | 62
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server – Understanding Indexes

• Well-designed indexes reduce the time needed to access required data, but designed
and used incorrectly, they slow query performance, lengthen maintenance routines, and
increase database size and storage costs.

• Like the index at the end of a book, indexes within a database enable fast access to table
contents. (SQL Server 2008 can support up to 1000 indexes per table)

• It’s possible for tables to be created without any clustered indexes. Such tables are
known as heaps. Heap tables store their data in no particular physical order.

Pages
Before proceeding let’s look at how SQL Server stores our data in our tables. SQL Server stores
our data using something called pages. Each page in SQL Server is 8K in size. This is fixed and
cannot be changed. A page contains the data that makes up a row in your table. The Page is
the smallest unit of data storage in Microsoft SQL Server.

Of the 8192 bytes on a page, approx. 8060 are available to you as a user. If you can manage to
fit your data rows onto the page nicely, they'll take up a lot less storage.
E.g.: If your data row is 4100 bytes long, only a single row will be stored on a page (and the rest
of the page - 3960 bytes - is wasted space). The important point is: those pages aren't just
relevant on disk, but also in SQL Server main memory --> you want to try to avoid large wasted
blobs of space.

If you can manage to reduce your row to take up 4000 bytes, then suddenly you can store two
rows on a page, and thus significantly reduce the overhead of wasted space (down to 60 bytes
per page).

Previous to SQL Server 2005 you could not create a row that exceeded the page size (page sizes
have increased from 2K to 8K over the different versions of SQL Server). Starting with SQL
Server 2005 you may create row sizes greater than 8K (not recommended) and the row data
will be stored onto overflow pages.

Page | 63
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Clustered Indexes
A clustered index is added to a table using the create clustered index command:
CREATE CLUSTERED INDEX cndxClientSSN ON tblclient(ClientSSN)
GO
After you create the clustered index on the Social Security number (SSN) column, the data
within the client table is physically ordered by SSN. The table can now be considered a
clustered table. In addition to reordering the existing rows in the table, new rows will be
inserted in order, based on the SSN value being inserted.

• A table can have only one clustered index. We can create additional (up to 999)
nonclustered indexes.

• A clustered index is unique. Ie: The data the index is based on must be unique in the
table. If you create a clustered index on a non-unique column, SQL Server will append a
4 byte identifier to a row to force uniqueness.

• A primary key is usually created as a clustered index.

• Clustered indexes contain the table data itself, which is stored at the leaf level of the
index. Ie: The bottom most level contains data pages while all levels above the leaf
contain index pages.

• If a table has no clustered index, it is known as a heap.

Page | 64
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Clustered Index Architecture

• In SQL Server, indexes are organized as B-trees.

• Each page in an index B-tree is called an index node.

o The top node of the B-tree is called the root node.
o The bottom nodes in the index are called the leaf nodes.
o Any index levels between the root and the leaf nodes are collectively known as
intermediate levels.

• In a clustered index
o the leaf nodes contain the data pages of the underlying table
o the root and intermediate level nodes contain index pages holding index rows
▪ Each index row contains a key value and a pointer to either an
intermediate level page in the B-tree, or a data row in the leaf level of the
index. The pages in each level of the index are linked in a doubly-linked
list.

• When a clustered index has multiple partitions, each partition has a B-tree structure that
contains the data for that specific partition. For example, if a clustered index has four partitions,
there are four B-tree structures; one in each partition.

Page | 65
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Nonclustered indexes
A nonclustered index is created using the create nonclustered index command:
CREATE NONCLUSTERED INDEX ndxClientDOBLastname ON tblclient(ClientDOB,
ClientLName)
GO

The above query creates an index on the combination of the DOB and Lastname columns.
NonClustered Index Architecture
• A nonclustered index does not change the physical order of the rows in the table.

• The leaf pages of a nonclustered index consist of an index key plus a bookmark that
point to the storage location of the table data.

o Heap: Traversal of the nonclustered index structure is followed by the retrieval

of the row using the Row ID (RID).
▪ The row ID consists of the file ID, the page number, and the row number
on the page.
o If you have a clustered table: Traversal of the nonclustured index structure is
followed by traversal of the corresponding clustered index. (ie: it retrieves the
data from the Clustered Index leaf page)
o In both cases, the number of I/O operations (ie: inserts, deletes, updates, etc.) is
quite high, so you should design a nonclustered index with care and only when
you are sure that there will be significant performance gains by using it.

• Each time a row is inserted, updated, or deleted from the client table, the corresponding
updates are made to the nonclustered index.

• When running a query that selects data based on a field that has an index, the query
optimizer will determine if the index should be used or table scan is used.

• Each time we add indexes, two things happen.

o The size of the database increases.
o We have additional information to maintain. If anyone changes their last name
or DOB, as well as updating the appropriate data in our table we would need to
update the additional indexes.

Page | 66
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Index Structure
• Indexes are stored in their own pages called Index Pages.

• Each index entry consists of the index key plus a pointer.

• Correspondingly each index entry is significantly shorter than the corresponding data
row. Therefore, the number of index entries per (index) page is significantly higher than
the number of rows per (data) page. Therefore the number of I/O operations required
to traverse the index structure is significantly lower than the number of I/O operations
required to traverse the corresponding data pages.

Indexes with SQL Server (both clustered and nonclustered) are implemented as B-trees. An
index seek starts at the top, or root node, of the tree and traverses through intermediate levels
before arriving at the leaf nodes. In a clustered index, the leaf nodes contain the actual table
data. In a nonclustered index, the leaf nodes contain the row locators.

Page | 67
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Page | 68
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

It is important to understand that

1. The width, or size, (number of columns used in the index) of the clustered index directly
affects the size of each nonclustered index.
Eg:, A single integer-based column is much narrower or smaller than two
char(50)-based columns. Clustered indexes should be as small and as narrow as
possible.

2. The clustered index should be created before the nonclustered indexes.

If a clustered index was created on the table after nonclustered indexes were
created, then each of the nonclustered indexes would need to be updated to include
the clustered index key in place of the row ID.

Index Guidelines
1. Large numbers of indexes on a table affect the performance of INSERT, UPDATE,
DELETE, and MERGE statements because all indexes must be adjusted appropriately as
data in the table changes. For example, if a column is used in several indexes and you
execute an UPDATE statement that modifies that column's data, each index that
contains that column must be updated as well as the column in the underlying base
table (heap or clustered index).

2. Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as
few columns as possible.

3. Use many indexes to improve query performance on tables with low update
requirements, but large volumes of data. Large numbers of indexes can help the
performance of queries that do not modify data, such as SELECT statements, because
the query optimizer has more indexes to choose from to determine the fastest access
method.

4. Indexing small tables may not be optimal because it can take the query optimizer longer
to traverse the index searching for data than to perform a simple table scan. Therefore,
indexes on small tables might never be used, but must still be maintained as data in the
table changes.
5. Use the Database Engine Tuning Advisor to analyze your database and make index
recommendations.

Page | 69
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

High Availability

• Failover Clustering

• Transaction Log Shipping

• Database Mirroring

• Replication

Page | 70
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Failover Clustering

• Built on top of a Windows Server failover cluster

• SQL Server database instance is active on only one physical clustered server
at any given time.

• SQL Server failover clustering is NOT a high-performance solution; in other

words, the performance won’t be any better, or worse, than a nonclustered
implementation.

• SQL Server clustering is purely a high availability solution and you

should not choose it for any other reason.

Page | 71
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Simple 2 Node Cluster Without MNS

Server 2 monitors
Private network link
Server 1 for
(PNL)
failure – Uses the
PNL
SQL
Instance

Shared Data
Source
(RAID)

Data, logs,
quorum, etc.

SQL Server 1 with SQL Server 2 with

Windows clustering –
Windows clustering
– Primary - Active Failover - Passive

1. The SQL instance resides on Server 1

2. Server 1 instance Fails

3. The SQL instance automatically fails over to Server 2

The SQL instance will continue to be accessed using the same virtual server name.

Page | 72
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• Key components of a Windows Server failover cluster solution are

o Shared storage
▪ All servers in the cluster access the same shared data
o Resource arbitration
▪ To prevent more than one server from accessing the shared data
at the same time.
▪ This would cause data corruption
▪ ‘Ownership’ of the shared data is based on the server’s current
role in the cluster

SQL Server clustering advantages

1. In the event of a server failure, the entire instance and all of its resources are moved to a
failover server, a process that usually takes no more than about 90 seconds.
2. Reduce downtime during planned outages.

SQL Server clustering limitations

1. No protection from failure of the disks containing the database files and/or the cluster
quorum resource.
2. Typical clustering solutions don’t offer protection from geographic disasters.

CluClsering in Windows Server 2003 and Above

• A SQL Server failover clustering instance is installed on the underlying Windows cluster.

• Clustering improvements in Windows Server 2003 include the following:

Failover: The transfer of ownership of resources from one server to another.

• An enhanced validation test, which can be used to ensure the validity of the
hardware and software components in forming a cluster
• Support for IPv6 and up to 16 cluster servers (increased from 8)
• The ability of cluster servers to obtain IP addresses via DHCP
• Support for new quorum models, used to determine the number of failures a
cluster can sustain while still operating

Page | 73
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Quorum models
• Each node is assigned unambiguous ownership of a resource.

o For example, in a two-node cluster like the one previously. If the network link
between the two cluster nodes temporarily drops, ownership of the quorum
prevents both servers from assuming that they’re now the owner of the SQL
instance?

• The process to resolve such an occurrence (commonly called the split brain problem) is
referred to as the cluster quorum.

• In earlier versions of Windows clustering (prior to Windows Server 2003), cluster

quorum was maintained using a shared disk resource and a quorum database
containing the resources and owners.

o In our previous example, a link failure between the two nodes would be
resolved by only one of the nodes having ownership of the quorum disk.
o When the link is dropped, the node with quorum disk ownership continues its
role

• Limitation: The quorum disk was a single point of failure.

• Limitation: All cluster nodes need to have access to the shared disk resource containing
the quorum disk, the constraints of typical shared storage hardware prevented the
creation of geographically dispersed clusters.

Windows Server 2003 addressed this with the introduction of the Majority Node Set (MNS)
quorum

• Nodes in an MNS quorum cluster use local copies of the quorum database, avoiding
the limitations of shared storage
• To prevent the split brain problem, a majority of nodes must remain in contact for the
cluster to be considered valid.
• A minimum of three nodes are needed to form a valid MNS cluster.

o Eg: In the case of a five-node cluster, a majority of the nodes (three) would need
to remain in contact for the cluster to continue operating.
• To avoid the cost of idle servers and limit the effects of a failover-induced resource
crunch, a commonly used cluster configuration is where one or more standby servers
exist for more than one working server.
o Eg: In a three node cluster, two nodes may be active, with a third existing as a
failover node for both active nodes.
o Eg: A five-node cluster with three active nodes and two failover nodes.

Page | 74
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Failover rules
• By regularly polling cluster nodes using a series of mechanisms called LooksAlive and
IsAlive checks, a Windows cluster may conclude that a cluster node has failed.
▪ At that point the resources hosted by the node are failed over to another
cluster node

• In a simple two-node cluster, the failover process is straightforward.

• But consider a five-node cluster containing three active and two

passive/standby nodes.

• Which (if any) of the standby nodes will be used for failover purposes when one of the

active nodes fails?

• In large clusters containing many SQL Server instances, you must consider maintaining a
well-balanced cluster, ensuring that a single node doesn’t carry a disproportionate
burden of the load.

Preferred Owners, Possible Owners, and Failback settings

• Possible Owners
o Lets you specify which nodes the instance is permitted to fail over to.
• Preferred Owner
o Used to set the preferred failover node (this may not be chosen in some
situations—for example, if the node is unavailable).
• Failback
o Determines whether or not resources fail back to the original node if the failed
node comes back online

Page | 75
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Failover Best Practice

• Consider the possibility and implications of a resource crunch in the event of failover.

• A clustered SQL Server node should not be a domain controller, or run any
other server applications such as Microsoft Exchange.

• Before installing a SQL Server failover clustering instance, run the Cluster Validation
Wizard to ensure the validity of the cluster components.

• All aspects of cluster nodes should be configured identically, including hardware

components and configuration, operating system versions and service packs, bios and
firmware version, network card settings, directory names, and so forth. Such a
configuration provides the best chance of continued smooth operations in the event of a
failover.

• Do not install antivirus software on clusters

• Do not let your antivirus software scan the quorum disk files.

• When installing a clustered SQL Server instance, set the service startup types to Manual
(which is the default setting) to enable the cluster to stop and start services as required on
the appropriate cluster node.
• The Control Panel Services applet should not be used in clusters for stopping or starting
SQL Server services. If an instance needs to be taken offline (or moved to another node),
use the Failover Cluster Management tool in the Administrative Tools folder or run
[Link] from the Start menu.

• Clustered servers should have at least two network cards, with at least one dedicated
to the cluster ’s private network.

• Bind the public LAN first before the private LAN, and remove File/Print Sharing and Client
for Microsoft Networks from the private LAN bindings.

• The private network should be physically separate from the public network using a
cross-over cable (for two-node clusters), a dedicated switch, or a virtual LAN (VLAN).

• The private network must not have any WINS, DNS, or NetBIOS settings enabled, and
should use TCP/IP as the only protocol.

Page | 76
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Benefits of Failover Clustering

• All databases for a given failover clustering instance are failed over in a
single action.
• SQL Agent jobs, logins, system configuration, and all other items are automatically
moved.
• No client redirection logic is required; a failover clustering instance is accessed over the
network using a virtual server name which automatically maps to the new server
should failover occur.

Page | 77
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Transaction Log Shipping

Source
2 Destination
Database
1 3 Database

1. Transaction log backup

2. Logs copied to destination server
3. Logs restored

The major limitation of failover clustering is no protection of the data files.

• Log shipping has no shared storage and therefore no central point of failure. Each server
in the log shipping pair is completely independent: it has its own storage and could
theoretically be located anywhere in the world.
• The major disadvantage of log shipping is that each database must be log shipped
independently.

• Log shipping has no automatic failover process. It is a fully manual process to bring the
new server online and redirect all clients to the new server

1. OFFSITE RECOVERY POINT

In the event of total environment destruction, the offsite copy can quickly restore
any remaining logs and be brought online and available for application usage.

Compared to database mirroring, log shipping has a much greater transaction

latency. The default frequency of the log backup, copy, and restore jobs is 15
minutes, so the secondary database will typically be between 30 and 45 minutes
behind the primary.

2. REPORTING SOLUTION

Page | 78
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

When restoring the transaction logs on the secondary database, the

standby option can be invoked. The standby option allows the secondary
database to be used for read purposes in between log restores.

3. Multiple backup locations

A log shipping source can be shipped to multiple destinations.

4. UPGRADE SOLUTION
Log shipping can be set up between one version of SQL Server and the next. For
example, a SQL Server 2005 source database can log ship to a 2008 destination.

This could be used to minimize the upgrade time

Database Mirroring

Transactions generated on the source (principal) database are sent to the mirror over an
encrypted, compressed log stream and applied in either a synchronous or asynchronous manner.
You can choose between the two modes to prioritize transaction safety or performance.

Page | 79
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

If the principal instance fails, an optional witness server can be used to automatically bring the
mirror database online with zero data loss

Page | 80
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Mirroring Process

Let’s look at the process behind the delivery of transactions from the principal to the
mirror during a mirroring session, and how this process is performed based on the
mirroring mode.

1. The mirror instance identifies the log sequence number (LSN) of the last transaction
applied to the mirror database.

2. The mirror instance then obtains any outstanding transactions from the
transaction log of the principal database.

Similar to log shipping, servers in a database mirroring session use the transaction
log to move transactions between a principal server and a mirror server.

3. The outstanding transactions received from the principal instance are written to the
transaction log of the mirror database and rolled forward.

4. The outstanding transactions to roll forward are known as the redo queue, and the
depth of this queue determines the catch-up time and therefore the minimum time to
fail over the principal role to the mirror database.

5. As updates on the principal database continue, the transactions are streamed from
the principal’s transaction log to the mirror’s transaction log and rolled forward on the
mirror database. The mirroring mode, asynchronous (high performance) or
synchronous (high safety), determines how the principal’s transactions are sent and
received.

Page | 81
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

High Performance (Asynchronous Mode)

Principal
Database Mirrored
2 Database
1 4

3
5

1. Transaction written to log

2. Log record streamed to mirror
3. Transaction commits on the principal
4. Log record written to transaction log
5. Transaction rolled forward on the mirror DB

Page | 82
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Asynchronous mode does not have auto fail over capabilities like its counterpart synchronous
mode. The failover is generally a manual process.

To ensure closest point of failure you should attempt to backup the tail log from primary
server and restore it on the mirror before switching over.

Asynchronous mode mirroring is typically used in disaster-recovery where the principal and
mirror servers are in different physical locations and the network connectivity between them
may lead to unacceptably large transaction latency under the synchronous mode.

If the possibility of some data loss is accepted as a consequence of the highest performance,
asynchronous mirroring presents a good disaster-recovery option, but for situations, in which
zero data loss is the target, consider high-safety synchronous mirroring.

High Safety (Synchronous Mode)

In synchronous mode, transactions are not committed on the principal database until written
to the transaction log on the mirror database. While this increases transaction latency,
synchronous mirroring ensures each transaction is recoverable on the mirror database, and is
therefore an excellent solution for protecting mission-critical data.

When running in high-safety mode, special consideration needs to be given to long-running

and/or intensive operations such as index rebuilds and bulk loads. The load from these types of
operations often leads to a measurable reduction in the performance throughput.

1. On receiving a transaction from a client, the principal server writes the log for the
transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the
log record to the mirror server. The principal server waits for an acknowledgement from
the mirror server before confirming either of the following to the client: a transaction
commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the
principal server.
4. On receiving the acknowledgement from the mirror server, the principal server sends a
confirmation message to the client.

Page | 83
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• The main advantage of database mirroring is that in synchronous mode the mirror is an
exact copy of the principal at any given moment. In contrast, a log shipping destination
is typically at least 15 minutes behind the source.

• Database mirroring needs to be set up on a database-by- database basis,

• A transaction is committed on the principal as soon as it is sent to the mirror; it

does not wait for an acknowledgment from the mirror that the transaction has
been written to the mirror’s transaction log, nor is the principal affected in any
way by a failure at the mirror (other than a loss of failover capabilities). As such,
asynchronous mirroring is used when transaction performance at the principal
is of prime concern.
• There is no guarantee that the mirror partner receives and applies each
transaction thereby the mirror may not be an exact copy of the primary.

therefore limiting its appeal for instances containing many critical databases.

• Mirroring can be configured with a witness server to initiate automatic failover to the
mirror server.

• Client connections can be automatically redirected to the mirror server on failure of

the principal.
• Only a single mirror can exist for each principal (log shipping allows multiple destinations
for the one source)

• The inability to read the mirror database (unless using a database snapshot), limits the
use of mirroring for reporting purposes.

• Secondary database is in an offline mode while mirroring is in place. This is to ensure

integrity in the secondary database in the event of a failover being required. Nothing
can write to the secondary database, so you know it will exactly match your primary.

• Can replicate schema changes. What this means is if you add a new index, or a new
table, it will automatically be mirrored over. This is not always the case with
replication.

Page | 84
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Replication
The replication model is based in a publisher/subscriber metaphor with three distinct database
roles, namely the publisher, subscriber, and distributor. These roles work together to copy
transaction activity or database snapshots from one server to another to provide for a greater
degree of data distribution.
Replication Roles

Publisher: In most replication scenarios, the publisher is the production server that accepts all
transaction activity and makes that activity available for replication to other servers. The
publisher will generally store transactions to be replicated in its transaction log until the
distributor picks them up for delivery.

Distributor: This is the service that is responsible for delivery of replicated data. In a typical
mode, the distributor will retrieve data from the log of the publisher and store that data in its
own log until it can distribute the data to the subscriber. It is essentially a store-and-forward
service.

When the principal and mirror instances are separated over large distances using a WAN,
asynchronous mirroring is typically used as part of a disaster-recovery solution.

In LANs or low-latency networks, synchronous mirroring is often used.

Note 1: If the network latency increases beyond 50ms use Asynchronous mode.

Note 2: Database mirroring cannot be enabled on databases containing FileStream data

Subscriber: The subscriber receives the replicated data from the distributor. In most cases this
database must be treated as read only because the transaction activity should occur on the
publisher. There are exceptions to this rule, however, including Merge replication models and
updating subscribers

Forms of Replication
1. Transactional - This is the standard replication model. In this approach, the publishing
server stores transactions in its transaction log for replication. Periodically, a log reader
service gathers the transactions and sends them to a distribution server that then
executes the transactions on each of the subscribing databases. This process can be
scheduled to occur at regular intervals or can be configured to execute whenever
transactions commit on the publishing server.

Page | 85
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

2. Snapshot - Takes images of the database at scheduled times of the changes recorded in
your logs since the last synch, and inserts them at a scheduled time. While you can
configure the frequency of replication, this approach works best when you have small
volumes of data that are either nonvolatile or tolerate data latency.

3. Merge - It will merge content between databases so you can update data in both DB's.
You might use this if you have two identical databases from day 1, and you have App 1
talking to DB 1, and App 2 talking to DB 2 for load balancing purposes or something. At
the end of the day you might want to merge the transactions that occurred during the
day to make sure you have two full lists of transactions in each DB.

• Snapshot replication. Synchronization means that the Distribution Agent reapplies the snapshot
at the Subscriber so that schema and data at the subscription database is consistent with the
publication database.
o If modifications to data or schema have been made at the Publisher, a new snapshot
must be generated in order to propagate modifications to the Subscriber.

Transactional replication. Synchronization means that the Distribution Agent transfers updates, inserts,
deletes, and any other changes from the distribution database to the Subscriber.

• Merge replication. Synchronization means that the Merge Agent uploads changes from the
Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber.

Page | 86
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

o Conflicts, if any, are detected and resolved. Data is converged, and the Publisher and all
Subscribers eventually end up with the same data values. If conflicts were detected and
resolved, work that was committed by some of the users is changed to resolve the
conflict according to policies you define.
o View and Resolve Data Conflicts for Merge Publications
o View Data Conflicts for Transactional Publications

Page | 87
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Publisher Distributor
Subscriber

Publisher Distributor
Subscriber

Page | 88
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

High Availability Summary

Replication
Publisher
Push Subscription: This is Distributor
a subscription
Subscriber
model where the distributor
services push data to the subscriber. SQL Agent
jobs running on the distributor will periodically connect to the subscriber
and execute transactions necessary to bring the subscriber current.

Pull Subscription: This is a subscription model where the subscriber services retrieve
transactions from the distributor for execution to keep the subscriber current.
1st
Pull
subscriptions allow some of the overhead of replication to move from the distributor to the
2nd
subscriber, which may load balance the model better.

Article: An individual collection of replicated data usually associated with a table. Creating an
article from a table allows the administrator to filter out columns or rows that they want to
exclude from the replication scenario.

Publication: A collection of articles usually associated with a database. A subscriber can either
subscribe to an individual article or to the entire publication.

Replication Topologies
Central Subscriber: It is possible to have multiple publishers replicate data to a single
subscriber. The data for each publisher article may even be published to the same table in the
subscriber as long as each publisher’s data is distinct. This pattern is most common where there
is a centralized data need for reporting or decision making.
Central Publisher: You can also configure a single publisher that can partition data to various
subscribers. Assuming that each subscriber only needs a subset of the data, the administrator
creates multiple articles, each one filtering out data for a particular subscriber. Each subscriber
can then receive the relevant data for them. This approach is useful when data is centrally
collected but must be distributed locally for decentralized processing.
Distributed Subscriber/Republisher: Use this model when data must replicate to more than
one subscriber, but those subscribers are geographically located where it is either expensive or
impractical to perform a direct replication. All data will replicate to a single subscriber that has
more convenient access to the ultimate subscribers. The subscriber then republishes that data
to the subscribers that need the data. In this model the central server is responsible for both
subscription and publication services.

Page | 89
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Clustering
Advantages:
• Snapshot publications completely refresh the schema at the Subscriber every time
1. Clustering can provide effective protection from hardware failures other than disk
synchronization occurs, so all schema changes are applied to the Subscriber.
failures
2.• Automatic
Transactional replication and merge replication also support the most common schema
detection and failover
changes
3. Ability to perform manual failover
4. Transparency to the client of failover redirection
o ALTER TABLE
Disadvantages:
1. Clustering operates at the server level of scope. You cannot failover an individual
o ALTER TABLE SET LOCK ESCALATION should not be used if schema change
database
replication is enabled and a topology includes SQL Server 2005 or SQL
2. There is no protection against disk failure. You should continue to use RAID for disk fault
Server Compact 3.5 [Link] VIEW
tolerance
o ALTER PROCEDURE
3. The cluster performs no load balancing. Only the active node can be queried
o ALTER FUNCTION
4. The cluster requires signed hardware capable of working with the Windows version that
o ALTER TRIGGER
you are targeting
o ALTER TRIGGER can be used only for data manipulation language
[DML] triggers because data definition language [DDL] triggers
Transaction Log Shippingcannot be replicated.
Advantages:
1. You can use the warm standby for reporting purposes
2. You can configure multiple target servers
3. You can mix SQL versions for primary and target servers
Disadvantages:
1. The latency between the time that the transaction commits on the master server and
the time when the master ships and restores the log to the target server(s)
2. The potential of some data loss
3. No automatic failover – the failover is a manual process thus impacting uptime

Page | 90
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Mirroring
Advantages:
1. Can apply transactions to the mirror server as soon as they are committed on the
principal server
2. Can provide automatic failover (must use a high-safety mode (synchronous) and include
a witness server)
3. Operates at the database level, so individual databases can be mirrored to separate
servers for scalability purposes
4. No special hardware is required
5. Supports transparent client redirection
6. When using the Enterprise edition of SQL Server 2008: if a single database corrupts on
either the principal or the mirror, the partner server can copy the page to the other
server to eliminate the corruption
Disadvantages:
1. The overhead of running in a high-safety mode (ie: performance)
2. Increased hardware requirement to support witnessing
3. The mirror database cannot be queried (ie: no reporting on mirror)
4. Mirror can only be sent to one remote location

Replication
Advantages:
1. You can run queries against your secondary database
2. Excellent data distribution tool for allowing various servers access to data and thus
client applications
3. Can have minimal transactional latency
4. No special hardware required
5. You can mix SQL versions
6. Replicate at the table level
Disadvantages:
1. Cannot replicate schema changes in certain situations
2. No automatic failover or transparent client redirection
3. Requires additional internal server metadata (ie: more overhead – performance issues)
4. Increases transaction logging overhead
5. Complex setup and maintenance (on multiple subscribers)
6. Complex failback
7. Potential transaction loss

Page | 91
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Files
The organization of a database involves many different objects. All objects of a database can be physical
or logical. The physical objects are related to the organization of the data on the physical device (disk).
Database Engine’s physical objects are files. Logical objects represent a user’s view of a database.
Filegroups, Databases, tables, columns, and views (virtual tables) are examples of logical objects.

An SQL Server database consists of multiple operating-system files. Data and log information are never
mixed in the same file, and individual files are used only by one database.

SQL Server databases have three types of files:

Primary data files

Every database has one primary data file. Data files contain data and objects such as tables, indexes,
stored procedures, and views. The recommended file name extension for primary data files is .mdf (this
extension is not enforced).

The locations of all the files in a database are recorded in the primary file of the database and in the
master database. SQL Server uses the file location information from the master database most of the
time. However, the Database Engine uses the file location information from the primary file to initialize
the file location entries in the master database in the following situations:

• When attaching a database using the CREATE DATABASE statement with either the FOR ATTACH
or FOR ATTACH_REBUILD_LOG options.
• When upgrading from SQL Server version 2000 or version 7.0.
• When restoring the master database.

Secondary data files

Secondary data files are optional, are user-defined, and store user defined objects (such as user data).
Secondary files can be used to spread data across multiple disks by putting each file on a different disk
drive (like striping). Additionally, if a database exceeds the maximum size for a single Windows file, you
can use secondary data files so the database can continue to grow. The recommended file name
extension for secondary data files is .ndf (this extension is not enforced).

Transaction Log files

Log files hold all the log information that is used to recover the database. There must be at least one log
file for each database, although there can be more than one. The recommended file name extension for
log files is .ldf (this extension is not enforced). Note: Space is really the only reason to have multiple log
files. SQL writes to a log file sequentially, so you don’t get the benefit of spreading the log across multiple
disks.

Page | 92
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Filegroups
Physical data files (primary and secondary files as above) can be organized into logical containers called
filegroups for better performance and manageability.

• When a new SQL Server database is created, the primary file group is created and the primary
data file is included in the primary file group. The primary group is marked as the default group.
As a result, every newly created user object is automatically placed inside the primary file group
(more specifically, inside the files in the primary file group).

• Every database has a primary filegroup. This filegroup contains the primary data file and any
secondary files that are not put into other filegroups.

• You can choose which file group you want to associate a database object to, and SQL Server
then stores the data, indexes, views, etc. in the associated file or files in that group.

• Transaction logs are not included in filegroups.

For example:

• Three files, [Link], [Link], and [Link], can be created on three disk drives,
respectively, and assigned to the filegroup fgroup1.
• A table can then be created specifically on the filegroup fgroup1.
• Queries for data from the table will be spread across the three disks; this will improve
performance.
• The same performance improvement can be accomplished by using a single file created on a
RAID (redundant array of independent disks) stripe set. However, files and filegroups let you
easily add new files to new disks.

Page | 93
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Best Practices for Files and Filegroups

• The primary file group should be separate from user defined objects, and should be left to have
only system objects, and no user defined object should be created on the primary file group.
Do not set the primary file group as the default file group. Separating the system objects from
other user objects will increase performance and enhance the ability to access tables in cases
of serious data failures.

• SELECT * FROM [Link]

o System Objects Per Database

Syscolumns Syscomments Sysconstraints

Sysdepends Sysfilegroups Sysfiles
Sysforeignkeys Sysfulltextcatalogs Sysindexes
Sysindexkeys Sysmembers Sysobjects
Syspermissions Sysprotects Sysreferences
Systypes sysusers

User Defined Objects per Database

Tables Views Procedures

Functions Etc.

• If there are N physical disk drives available in the system, then try to create N files per file
group and put each one in a separate disk. This will allow distributing disk I/O load over
multiple disks, and will increase performance.

• For frequently accessed tables containing indexes, put the tables and the indexes in separate
file groups. This would enable reading the index and table data faster.

• For frequently accessed table containing Text or Image columns, create a separate file group
and put the text and image columns in that file group on different physical disks, and put the
tables in a different file group. This would enable faster data retrieval from the table with
queries that don't contain text or image columns.

• Put the transaction log file on a different physical disk that is not used by the data files. The
logging operation (Transaction log writing operation) is more write-intensive, and hence it is
important to have the log on the disk that has good I/O performance.

Page | 94
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

• Consider assigning "Read only" tables into a file group that is marked as "Read only". This
would enable faster data retrieval from these read only tables. Similarly, assign "Write only"
tables in a different file group to allow for faster updates.

• Do not let SQL Server fire the "Auto grow" feature too often because it is a costly operation. Set
an "Auto grow" increment value so that the database size is increased less frequently (say,
once per week). Similarly, do not use the "Auto shrink" feature for the same reason. Disable it,
and either shrink the database size manually, or use a scheduled operation that runs in a timed
interval (say, once a month). I recommend that shrinking a database should always be a last
resort option. Shrinking can cause data fragmentation.

Page | 95
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Partitioning

What is Partitioning in SQL Server?

Table partitioning is a way to divide a large table into smaller, more manageable parts without having to
create separate tables for each part. Data in a partitioned table is physically stored in groups of rows
called partitions and each partition can be accessed and maintained separately. Partitioning is not visible
to [Link]
end users; a partitioned table behaves like one logical table when queried.
[Link] [Link]

Usefulness of Partitioning
• Backup/restore at the partitionDisk
level.
2 Ie: can backup or restoreDisk
a single
3 partition rather than the
Disk 1
entire table.
• On a large table, only a subset of data will be volatile/read-write and the remainder (possibly >
90%), will be read-only data. With varying access patterns, you need more indexes for read-only
data whereas you need fewer Filegroup: fgroup1
indexes for read-write data.
• Create and rebuild indexes at the partition level rather than on the entire table.
• Implement compression at the partition level. Less access partitioned data (historical) can be
compressed while more frequently access data can be uncompressed. Compressing data takes
longer to access but takes up less hard disk space.
• Eliminate the possibility of more frequent lock escalation issues at the table level.

Page | 96
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

The two following options are available to partition a table:

Horizontal partitioning
Suppose we have a table containing 10 million rows. For easy understandability, let's assume that the
table has an auto-increment primary key field (say, ID). We can divide the table's data into 10 separate
partition tables where each partition will contain 1 million rows and the partition will be based upon the
value of the ID field. That is, the first partition will contain those rows which have a primary key value in
the range 1-1000000, and the second partition will contain those rows which have a primary key value in
the range 1000001-2000000, and so on.

As you can see, we are partitioning the table by grouping the rows based upon a criteria (ID range),
which seems like we have a stack of books in a box from where we are horizontally splitting the stack by
taking a group of books from the top and putting it in smaller boxes. Hence this is called horizontal
partitioning.

Vertical partitioning
Suppose we have a table with many columns and also millions of rows. Some of the columns in the table
are very frequently accessed in some queries, and most of the columns in the table are less frequently
accessed in some other queries.

As the table size is huge (in terms of number of columns and rows), any data retrieval query from the
table performs slowly. So, this table could be partitioned based on the frequency of access of the
columns. That is, we can split the table into two or more tables (partitions) where each table would
contain a few columns from the original tables. In our case, a partition of this table should contain the
columns that are frequently accessed by queries, and another partition of this table should contain the
columns that are less frequently accessed by other queries. Splitting the columns vertically and putting
them in different thinner partitions is called vertical partitioning.
Another good way for applying vertical partitioning could be to partition the indexed columns and non-
indexed columns into separate tables. Also, vertical partitioning could be done by splitting BLOB or
VARCHARMAX columns into separate tables.

Like horizontal partitioning, vertical partitioning also allows to improve query performance (because
queries now have to scan less data pages internally, as the other column values from the rows have
been moved to another table), but this type of partitioning is to be done carefully, because if there is
any query that involves columns from both partitions, then the query processing engine would require
joining two partitions of the tables to retrieve data, which in turn would degrade performance.

Page | 97
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Partitioning Best Practices

• Consider partitioning large tables into different file groups where each file inside the file group is
spread into separate physical disks (so that the table spans across different files in different
physical disks). This would enable the database engine to read/write data operations faster.

• For historical data, consider partitioning based on "Age". For example, suppose a table has order
data. To partition this table, use the Order date column to split the table so that a partition is
created to contain each year's sales data.

Figure 1 – A Single table containing 5 years’ worth of sales data.

Sales data for 2009, 2010, 2011, 2012, 2013

Figure 2 – A single table with 5 partitions - each partition contains one years’ worth of data.

2009 2010 2011 2012 2013

Page | 98
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Encrypting Your Database

Protecting your data from illegal access should be a TOP priority for any DBA in today’s environment.
Implementing a firewall and access privileges are not sufficient to protect your data. As can be seen
from the daily news of data breaches from companies around the world it is imperative to protect the
data from illegal breaches that occur from behind the firewall.

A multi tier approach to security is needed – Firewalls, User Privileges, Physical Access, and Encryption
(database level, transmission level, and backups).

Database Level Encryption – Transparent Data Encryption (TDE)

TDE encrypts data at rest. This means that should someone manage to take a physical copy of your data
files, they would not be able to decipher their contents without the proper keys. When an SQL Server
instance mounts an encrypted data file, it uses the keys to decrypt the data as it is extracted from the
file during use, and then to encrypt it again before it is written to the drive. TDE is only available for
Enterprise versions of SQL Server.

It is called Transparent Data Encryption because the client or server applications that access the
database do not need to be changed in anyway.

The encryption uses a database encryption key (DEK), which is stored in the database boot record for
availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the
master database of the server or an asymmetric key protected by an EKM module. Both the data and log
files will be encrypted.

Encryption of the database file is performed at the page level. The pages in an encrypted database are
encrypted before they are written to disk and decrypted when read into memory. TDE does not increase
the size of the encrypted database.

Page | 99
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

After it is secured, the database can be restored by using the correct certificate.

Implementing Transparent Data Encryption

To implement TDE, follow these steps.

• Create a master key

• Create or obtain a certificate protected by the master key

• Create a database encryption key and protect it by the certificate

• Set the database to use encryption

The following example illustrates encrypting and decrypting the AdventureWorks2012 database using a
certificate called MyServerCert.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

Page | 100
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Detailed Operation of TDE Implementation

The Windows Data Protection API (DPAPI) is used to encrypt the service master key (SMK), a symmetric
key that resides in the master database. SQL Server creates the SMK the first time the instance is
started. You can use the key to encrypt credentials, linked server passwords, and the database master
keys (DMKs) residing in different databases.

SQL Server uses the SMK and a user-supplied password to encrypt the DMK with the 256-bit AES
algorithm.

Backup files of databases that have TDE enabled are also encrypted by using the database
encryption key. As a result, when you restore these backups, the certificate protecting the
database encryption key must be available. This means that in addition to backing up the
database, you have to make sure that you maintain backups of the server certificates to prevent
data loss. Data loss will result if the certificate is no longer available.

Page | 101
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

When enabling TDE, you should immediately back up the certificate and the private key associated
with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the
database on another server, you must have backups of both the certificate and the private key or
you will not be able to open the database. The encrypting certificate should be retained even if
TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the
transaction log may still remain protected, and the certificate may be needed for some operations
until the full backup of the database is performed. A certificate that has exceeded its expiration
date can still be used to encrypt and decrypt data with TDE.

Page | 102
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

For maximum data security, the network transmission of SQL Server data can be encrypted using either
Internet Protocol Security (IPSec – done at OS level – easiest, no SQL Server configuration needed) or
Secure Sockets Layer (SSL).

Considerations
• In TDE, all files and filegroups in the database are encrypted. If any filegroups in a database are
marked READ ONLY, the database encryption operation will fail.

• If a database is being used in database mirroring or log shipping, both databases will be
encrypted. The log transactions will be encrypted when sent between them.

• Replication does not automatically replicate data from a TDE-enabled database in an encrypted
form. You must separately enable TDE if you want to protect the distribution and subscriber
databases.

Page | 103
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Monitoring SQL Server

This topic deals with monitoring SQL Server instances for abnormal activity, managing the
response to failure conditions, and carrying out a number of proactive maintenance
tasks.

Activity Monitor
You can use this tool to ability to spot abnormal activity at a glance using the four included graphs.
These graphs display activity in real time.

You can change the default graph refresh rate of 10 seconds by right-clicking any of the four graphs and
selecting the appropriate refresh interval. The menu also allows you to pause or resume the display.

Page | 104
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

The graphs show the following information:

Graph Title Displays
% Processor Time The percentage of elapsed time that the processor spends to execute
non-idle threads for the instance across all CPUs
Waiting Tasks number of tasks that are waiting for processor, I/O, or memory
resources
Database I/O transfer rate, in megabytes per second, of data from memory to disk,
disk to memory, or disk to disk
Batch Requests per Second number of SQL Server batches that are received by the instance

Activity User Processes Pane

This pane shows information for active user connections to the instance, and includes the following
columns:

Column Name Displays

Session ID a unique integer (int) that is assigned to each user connection when the
connection is made
User Process Displays 0 for a system process and 1 for a user process. By default, the filter
setting for this column is 1. This displays only user processes
Login SQL Server login name under which the session is currently executing
Database The name of the database that is included in the connection properties of
processes that are currently running
Task State The state of the task. For tasks in a runnable or sleeping state, the task state is
blank. Otherwise, this can be one of the following values:

• Background
• Running
• Suspended

Command The kind of command that is being processed under the task
Application The name of the application program that created the connection
Wait Time (ms) The time, in milliseconds, in which this task is waiting for a resource. When the
task is not waiting, the wait time is 0
Wait Type name of the last or current wait type
Wait Resource name of the resource that is needed
Blocked By If there are blocking sessions, the ID of the session that is blocking the task
Head Blocker If there are blocking sessions, identifies the session that causes the first blocking
condition. A value of 1 represents a head blocker for other sessions
Memory Use amount of memory, in kilobytes, that is being used by the task
(kb)
Host Name name of the computer that made the connection to the instance of SQL Server
Workload name of the Resource Governor workload group for the session. For more
Group information, see Managing SQL Server Workloads with Resource Governor

Page | 105
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Important: TDE encrypts data a rest. Meaning the data on the physical drive is encrypted.
When a query is executed on the data the pages are read into memory (a buffer pool) and
decrypted. The query is then executed on the unencrypted data in the buffer pool. This
memory buffer could be a potential security flaw as it contains exposed data.

Another solution that may be of interest would be COLUMN level encryption.

Resource Waits Pane

This pane shows information about waits for resources (During periods of widespread poor response,
this view is ideal for spotting resource bottlenecks that may be contributing to a large number of waits).

Column Displays
Wait Category categories that accumulate wait type statistics. The individual wait
types are shown in the Active User Tasks pane. For more information,
see sys.dm_os_wait_stats
Wait Time (ms/sec) wait time in milliseconds per second for all tasks that are waiting for
one or more resources in the wait category since the last update
interval
Recent Wait Time (ms/sec) weighted average wait time in milliseconds per second for all tasks
that are waiting for one or more resources in the wait category since
the last update interval
Average Waiter Count number of tasks that are waiting for one or more resources in the wait
category at a typical moment during the last sample interval
Cumulative Wait Time (sec) total amount of time in seconds that tasks have waited for one or
more resources in the wait category since SQL Server was last started
on the instance, or since DBCC SQLPERF was ran on the instance

Page | 106
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Data File I/O Pane

Shows information about the database files for the databases that belong to the instance.

Column Displays
Database Name of the database
File Name Name of the files that belong to the database
MB/sec Read Recent read activity, for the database file
MB/sec Written Recent write activity for the database file
Response Time (ms) Average response time of recent read-and-write activity to the database file

Page | 107
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Recent Expensive Queries Pane

Shows information about the most expensive queries that have been run on the instance over the last
30 seconds. Includes queries in process and queries that finished during the time period.

Column Displays
Query query statement that is being monitored
Executions/min executions per minute for the query
CPU (ms/sec) rate of CPU use by the query
Physical Reads/sec rate per second of physical reads by the query (is writing from memory to
disk)
Logical Writes/sec rate per second of logical writes by the query (is writing to the page in
memory)
Logical Reads/sec rate per second of logical reads by the query (is reading the page from
memory)
Avearge Duration (ms) Average duration in milliseconds of running this query
Plan Count number of cached query plans for this query. A large number might indicate
a need for explicit query parameterization. For more information, see
Specifying Query Parameterization Behavior by Using Plan Guides

Page | 108
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server Profiler

Activity Monitor provides an excellent, high-level overview of system activity. For a
more detailed statement-level view, you can use SQL Server Profiler.

Use this tool to create and manage traces and analyze and replay trace results. Events are saved in a
trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a
problem.

Important: This tool will no longer be available for Database Engine after SQL Server 2016. It
will still be available for Analysis Services.

Going forward you should start using Extended Events to capture your trace.

Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database
Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze
later. For example, you can monitor a production environment to see which stored procedures are
affecting performance by executing too slowly. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.

• Finding and diagnosing slow-running queries.

• Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can
then be used to replicate the problem on a test server where the problem can be diagnosed.

• Monitoring the performance of SQL Server to tune workloads. For information about tuning the
physical database design for database workloads, see Database Engine Tuning Advisor.
• Correlating performance counters to diagnose problems.

Event
An event is an action generated within an instance of SQL Server Database Engine. Examples of these
are:

• Login connections, failures, and disconnections.

• Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
• Remote procedure call (RPC) batch status.
• The start or end of a stored procedure.
• The start or end of statements within stored procedures.
• The start or end of an SQL batch.
• An error written to the SQL Server error log.
• A lock acquired or released on a database object.
• An opened cursor.
• Security permission checks.

All of the data generated by an event is displayed in the trace in a single row. This row is intersected by
data columns that describe the event in detail.

Page | 109
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Server Side Trace

Using SQL Server Profiler to trace database activity is frequently referred to as clientside tracing, that is,
events are streamed from the server, over the network, to the Profiler application. Even if the Profiler
application is being run on the SQL Server itself, this is still considered a client-side trace.

One of the worrisome aspects of client-side tracing with SQL Profiler is that under certain conditions,
events can be dropped, therefore invalidating event sequencing and performance analysis. Further,
depending on the server load, the overhead of streaming events can impact server performance, in
some cases quite dramatically.

SQL Server Profiler is a GUI-based client interface to SQL Trace, the event-tracing service within the
database engine. As an alternative to using SQL Profiler, we can create a server-side trace using a
number of SQL Trace system stored procedures and in doing so avoid both the performance problems
and dropped events commonly associated with client-side traces.

A Profiler trace can be exported to a file using the File > Export menu item after the trace has been
defined with the required events, columns, and filters. The resultant T-SQL code can then be executed
against the required instance, which creates the server-side trace.

Database Engine Tuning Advisor

Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views,
and partitions without requiring an expert understanding of the structure of the database or the
internals of Microsoft SQL Server.
Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more
databases. A workload is a set of Transact-SQL statements that execute against a database or databases
that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL
scripts as workload input when tuning databases. You can create Transact-SQL script workloads with the
Query Editor in SQL Server Management Studio. You can create trace file and trace table workloads by
using the Tuning Template in SQL Server Profiler.
Capabilities
The Database Engine Tuning Advisor can:

• Recommend the best mix of indexes for databases by using the query optimizer to analyze
queries in a workload.
• Recommend aligned or non-aligned partitions for databases referenced in a workload.
• Recommend indexed views for databases referenced in a workload.
• Analyze the effects of the proposed changes, including index usage, query distribution among
tables, and query performance in the workload.
• Recommend ways to tune the database for a small set of problem queries.
• Allow you to customize the recommendation by specifying advanced options such as disk space
constraints.
• Provide reports that summarize the effects of implementing the recommendations for a given
workload.
• Consider alternatives in which you supply possible design choices in the form of hypothetical
configurations for Database Engine Tuning Advisor to evaluate.

Page | 110
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Workload
To build a recommendation of the optimal set of indexes, indexed views, or partitions for your
databases, Database Engine Tuning Advisor requires a workload.

• A workload consists of a Transact-SQL script or a SQL Server Profiler trace saved to a file or
table.
• If you do not have an existing workload for Database Engine Tuning Advisor to analyze, you can
create one using the Tuning Template in SQL Server Profiler.
• You can also use the following as workloads:
o Benchmarks specific to your organization or your industry.
o Problem queries that take a long time to run

Page | 111
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

SQL Server System Views

System views give you a fast way to uncover all sorts of facts about the database, its tables, columns
keys and indexes, files and filegroups, etc.

We will be reviewing three (3) of the most common types of system views –

• catalog views
o let you retrieve information about a wide range of system and database components-
from table columns and data types to server-wide configurations
• information schema views
o are similar to some of the catalog views in that they provide access to metadata that
describes database objects such as tables, columns, domains, and check constraints.
However, information schema views conform to the ANSI standard, whereas catalog
and dynamic management views are specific to SQL Server
• dynamic management views
o return server state data that can be used to monitor and fine-tune a SQL Server instance
and its databases

Catalog Views
Microsoft Docs - System Catalog Views

Sample Code

Of the various types of system views available in SQL Server, catalog views represent the largest
collection and most diverse. You can use catalog views to gather information about such components as
AlwaysOn Availability Groups, Change Data Capture, change tracking, database mirroring, full-text
search, Resource Governor, security, Service Broker, and an assortment of other features-all in addition
to being able to view information about the database objects themselves.

Microsoft recommends that you use catalog views as your primary method for accessing SQL Server
metadata because they provide the most efficient mechanism for retrieving this type of information.

Page | 112
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Commonly Used Catalog Views

• [Link]
o provides quick and easy access to all user-defined objects in your database, including
tables, views, triggers, functions, and constraints
o [Link] does not show DDL triggers, because they are not schema-scoped. All
triggers, both DML and DDL, are found in [Link].
• Catalog views that are distinct to a specific object type
o [Link]
o [Link]
o [Link]

Page | 113
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Examples

How do I find the names of the columns of a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name>
with valid names.

USE [database_name];
GO

SELECT name AS ColumnName

FROM [Link]
WHERE object_id = OBJECT_ID('schema.table_name');

How do I find the data types of the columns of a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name>
with valid names.

USE <database_name>;
GO

SELECT [Link] AS column_name

,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,[Link] AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,[Link]
,[Link]
FROM [Link] AS c
JOIN [Link] AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;
GO

Catalog views do not contain information about replication, backup, database maintenance
plan, or SQL Server Agent catalog data.

Page | 114
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

How do I find all the entities that have been modified in the last N days?

Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;
GO

SELECT name AS object_name

,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM [Link]
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO

OBJECT_ID - Returns the database object identification number of a schema-scoped object.

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using
OBJECT_ID. For objects that are not found in the [Link] catalog view, obtain the object
identification numbers by querying the appropriate catalog view. For example, to return the
object identification number of a DDL trigger, use
SELECT OBJECT_ID FROM [Link] WHERE name = 'DatabaseTriggerLog'''

How do I find the columns of a foreign key for a specified table?

Before you run the following query, replace <database_name> and <schema_name.table_name>
with valid names.

USE <database_name>;
GO

SELECT
[Link] AS foreign_key_name
,OBJECT_NAME(f.parent_object_id) AS table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
,OBJECT_NAME (f.referenced_object_id) AS referenced_object
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
,is_disabled
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');

Page | 115
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Information Schema Views

Information Schema Views

Information schema views provide a standardized method for querying metadata about objects within a
database. Although much more limiting in scope than catalog views, information schema views have the
advantage of being ANSI-compliant. This means you can theoretically migrate your code to different
database systems without having to update your view references. If portability is important to your
solution, you should consider information schema views.

Examples

How do I find the names of the columns of a specified table?

Before you run the following query, replace <database_name> and <table_name> with valid names
USE [database_name]
GO

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table_name'
GO

How do I find the data types of the columns of a specified table?

Before you run the following query, replace <database_name> and <table_name> with valid names.
USE [database_name]
GO

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE

FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table_name';
GO

When you reference the information schema views, you must use a qualified name that
includes the INFORMATION_SCHEMA schema name. As in:
INFORMATION_SCHEMA.COLUMNS

Page | 116
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Dynamic Management Views

Dynamic management views and functions

Dynamic management views and functions return server state information that can be used to monitor
the health of a server instance, diagnose problems, and tune performance.

There are two types of dynamic management views and functions:

• Server-scoped dynamic management views and functions. These require VIEW SERVER STATE
permission on the server.

• Database-scoped dynamic management views and functions. These require VIEW DATABASE
STATE permission on the database.

In information schema views, a domain is a user-defined data type, and a catalog is the database
itself.

Some dynamic management views

• sys.dm_os_sys_info
o returns miscellaneous information about the computer and its resources
• sys.dm_db_index_usage_stats
o retrieves information about indexes
• sys.dm_os_threads
o focuses on the SQL Server Operating System (SQLOS), which manages the operating
system resources specific to SQL Server
• sys.dm_db_file_space_usage
o returns space usage information for each file in the database
• sys.dm_repl_schemas
o returns information about table columns published by replication

Page | 117
Updated: November 11, 2019
SQL710 – Database Administration Using SQL Server Robert Stewart – [Link]@[Link]

Examples

How to find out some basic information about the server?

SELECT cpu_count, physical_memory_kb, virtual_memory_kb

FROM sys.dm_os_sys_info;

Output:

As you can see my server has 4 CPU (Dual core with Hyper-Threading), 8G physical memory and a lot of
virtual memory.

Page | 118
Updated: November 11, 2019