Microsoft SQL Server

Microsoft SQL Server

Richard Harris Richard Harris

Richard Harris

DevOps, Online & Mobile at TD Bank Group #devops #agile #cloud #java #js #csharp

发布日期: 2021年6月3日
+ 关注

Introduction 

MS SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It provides GUI, command line, and other interfaces (i.e. JDBC, ADO.NET, etc.). Programmers write queries using SQL and execute them on SQL Server. 

This tutorial is designed for readers who want to learn the fundamentals of SQL Server. It is recommended that you have prior familiarity with database concepts prior to proceeding with this tutorial. It is also good to have SQL Server installed on your computer, as it might assist you in executing the examples yourself and get to know how it works (see: Getting Started). Alternatively, rather than having SQL Server installed locally, you may instead have the SSMS client software running locally and the server running across a network. 

Watch Video 


Contents

  • Introduction
  • Installation
  • Scripts (Schemas, CREATE / USE / DROP Database, Login & User, Assign Permissions, Creating / Restoring Backups)
  • Summary
  • Microsoft SQL Server Tutorial 
  • Resources (Microsoft.com, Downloads, Microsoft Docs, Wikipedia)
  • Further Reading (Free Database eBooks, SSRS, Backups, HA Technologies, Other)


Installation

Editions 

SQL Server is available in various editions:

  • Enterprise: The premium offering, SQL Server Enterprise edition delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence - enabling high service levels for mission-critical workloads and end-user access to data insights.
  • Standard: SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud - enabling effective database management with minimal IT resources. Recommended when there is no requirement of advanced features. 
  • Web: SQL Server Web edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
  • Developer: SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications. It can be easily upgraded to Enterprise without re-installation. 
  • Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers, and hobbyists building client applications. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to other higher end versions of SQL Server. SQL Server Express LocalDB is a lightweight version of Express that has all of its programmability features, runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB. 

 SQL Server 2019 Requirements: 

SQL Server 2016 and 2017 Requirements:

Windows 7: 

"Your Operating System is not supported by SQL Server 2016. You may download the installation media but you will not be able to install it on this machine. For more information see this link: https://go.microsoft.com/fwlink/?LinkID=826321"

"The operating system on this computer or its service pack does not meet the minimum requirements for SQL Server 2016. To determine the minimum required operating system supported for this SQL Server release, see Hardware and Software Requirements for installing SQL Server 2016: https://go.microsoft.com/fwlink/?LinkID=398123"

See Also 

Downloads 

Other Downloads:

Installation Steps 

  1. Download SQL Server Developer edition
  2. Run setup application 
  3. Click 'Installation' on the left side of the 'SQL Server Installation Center' screen
  4. Proceed through the SQL Server Setup screens including Feature Selection, Service Accounts, Collation, and Authentication Mode (e.g. 'Windows authentication mode' or 'Mixed Mode' w/ SQL Server authentication), and Data Directories configuration. For paid versions the Product Key and Named Instance settings would be applicable. 

SQL Server Management Studio 

Server Management Studio is a workstation client tool. It allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line. 

In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc. 

To open SQL Server Management Studio, go to: Start -> All Programs -> MS SQL Server 20XX -> SQL Server Management Studio 


Scripts

Schemas

/* List Databases Names with Owner Names */ 
SELECT suser_sname( owner_sid ) AS "Owner", * FROM sys.databases

/* List Schemas in SQL Server database */
select s.name as schema_name, 
 s.schema_id,
 u.name as schema_owner
from sys.schemas s
 inner join sys.sysusers u
 on u.uid = s.principal_id

order by s.name

Create Database 

Syntax

CREATE DATABASE <yourdatabasename> 
 
RESTORE DATABASE <yourdatabasename> FROM DISK='<Backup file location + file name>'

Example 

CREATE DATABASE Testdb 

RESTORE DATABASE Testdb FROM DISK='D:\Backup\Testdb_full_backup.bak' 

Example > WITH MOVE

Restore Backup.bak with customized Logical Name and Physical File Names (i.e. you are NOT overriding the original data file, and are instead creating a new database). 

RESTORE DATABASE RestoreTest FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Backup.bak' 
WITH 
 MOVE '_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\RestoreTest.mdf',
 MOVE '_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\RestoreTest.ldf'

;

Example > RESTORE FILELISTONLY

Use RESTORE FILELISTONLY to list the LogicalName & PhysicalName of the Primary Data file and the log contained within a backup file. 

RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Backup.bak'

Select Database

Syntax 

USE <yourdatabasename>

 Example 

USE msdb; 


SELECT * FROM backupset; /* retrieves backup history */

DROP Database

Syntax 

DROP DATABASE <yourdatabasename> 

Example

DROP DATABASE Testdb 

Login & User

Syntax 

CREATE LOGIN yourloginname [WITH password='yourpassword'] [FROM WINDOWS]; 

CREATE USER <username> FOR LOGIN <loginname>;

Example 

/* SQL user with login */
CREATE LOGIN TestLoginNW WITH password='password';

/* Step 2. Grant this login permissions to access a database. */ 
/* Step 3. Grant rights to tables */
USE Northwind; 
CREATE USER TestUserNW FOR LOGIN TestLoginNW; 


EXEC sp_addrolemember 'db_datareader', 'TestUserNW'

Assign Permissions 

Syntax 

USE <database name> 

GRANT <permission name> ON <object name> TO <username\principle> 

Example 

USE TestDB; 

CREATE TABLE TestTable(
	id INT NOT NULL IDENTITY(1,1),
	data VARCHAR(50) NOT NULL, 
	PRIMARY KEY (id)
);

DENY SELECT ON TestTable TO TestUser;


GRANT SELECT ON TestTable TO TestUser; 

Creating Backups 

Syntax

BACKUP DATABASE <yourdatabasename> TO DISK='<location + file name>' 

BACKUP DATABASE <yourdatabasename>TO DISK='<location + file name>' with differential 


BACKUP LOG <yourdatabasename> to disk='<location + file name>' 

Example 

BACKUP DATABASE TestDB TO DISK='D:\TestDB_Full.bak' 

BACKUP DATABASE TestDB TO DISK='D:\TestDB_diff.bak' WITH DIFFERENTIAL 


BACKUP LOG TestDB TO DISK='D:\TestDB_log.trn'

Restoring Backups 

Syntax 

RESTORE DATABASE <yourdatabasename> FROM DISK='<location + file name>' 

Example 

RESTORE DATABASE TestDB FROM DISK='D:\TestDB_Full.bak' WITH REPLACE 

Example > WITH MOVE 

RESTORE DATABASE TestDB FROM DISK = 'D:\TestDB_Full.bak' 
WITH 
 MOVE 'TestDB' TO 'D:\Data\TestDB.mdf', 

 MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'


Summary

Add-On SQL Server Services

  • SQL Server Reporting Services (SSRS): ): A server-based report generating software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with Crystal Reports and other business intelligence tools.
  • SQL Server Integration Services (SSIS): Used to carry out ETL operations through.
  • SQL Server Analysis Services (SSAS): Used to analyze huge amounts of data to help with business decisions. 

Files 

Databases have three types of files - Primary data file (.mdf), Secondary data file(s) (.ndf), and Log file (.ldf). Primary data file is the starting point of the database and points to the other files in the database. Log files hold all of the log information used to recover the database.

SQL Server Logins & Database Users 

A Login is used for authentication into a SQL Instance (server level) while a User is used for authorization into a SQL Database. Logins are assigned to server roles (e.g. public or serveradmin) and Database Users are assigned roles within that database (e.g. db_datareader). Logins must be mapped to a database User to connect to a database. A Login grants the principal entry into the Server. A User grants access into a single Database. One Login can be associated with many Users (one per Database).

Typically, you'll create a Login before creating a User account. However, one of the User types in SQL Server is 'SQL user without login'. In this scenario, a DBA can retroactively assign a login to a user that's already been created. 


Microsoft SQL Server Tutorial

Table of Contents 

  1. Overview 
  2. Architecture 
  3. Create Database 
  4. Select Database 
  5. Drop Database 
  6. Login
  7. Create Users 
  8. Assign Permissions
  9. Creating Backups 
  10. Restoring Backups 
  11. Monitor Database 
  12. High Availability Technologies (HA)
  13. Services
  14. Execution Plans 


1. Overview

What is SQL Server? 

  • It is platform dependent RDBMS software developed by Microsoft.
  • It includes services, GUI (SSMS), and command based software. 
  • It supports the language SQL.

Usage of SQL Server 

  • To create & maintain databases. 
  • To analyze the data through SQL Server Analysis Services (SSAS). 
  • To generate reports through SQL Server Reporting Services (SSRS). 
  • To carry out ETL operations through SQL Server Integration Services (SSIS). 

Versions of SQL Server

  • Versions & Year: 6.0 (1995), 6.5, 7.0, 8.0 (2000), 9.0 (2005), 10.0, 10.5, 11.0, 12.00 (2014), 13.0 (2016), 14.0 (2017), 15.x (2019).
  • Note: Rather than Version X.X, versions of SQL Server are more commonly referenced like SQL Server 20xx.


SQL Server Components

SQL Server works in client-server architecture, hence it supports two types of components: 

  • Workstation components are installed on SQL Server operator's machine. These are just interfaces to interact with Server components. Example: SSMS, Profiler, etc. 
  • Server components are installed in centralized server. These are services. Example: SQL Server, SSIS, SSAS, SSRS, etc. 


SQL Server Databases & Instances 

A database is a systematic collection of data that stores data in tables. 

An instance of the Database Engine is an installation of SQL Server - a copy of the sqlservr.exe executable that runs as an operating system service. If the programmer installed SQL server n times, then n number of instances will be created. A computer can run multiple instances of the Database Engine while one instance can manage multiple databases. 

Each instance manages several system databases and one or more user databases. Each computer can run multiple instances (and versions) of the Database Engine. Stated again, an instance is an installation of SQL Server while a database is a collection of data. There can be multiple instances on a single computer, and there can be one or more databases in a single instance.

Applications connect to the instance in order to perform work in a database managed by the instance. When sending data from a Java or .NET application, it is necessary to connect first to the instance that manages that particular database.


Schemas 

In a database, there are one or multiple object ownership groups. They are called schemas. There are many database objects within each schema such as tables, views and stored procedures.

/* List Databases Names with Owner Names */ 
SELECT suser_sname( owner_sid ) AS "Owner", * FROM sys.databases


/* List Schemas in SQL Server database */
select s.name as schema_name, 
 s.schema_id,
 u.name as schema_owner
from sys.schemas s
 inner join sys.sysusers u
 on u.uid = s.principal_id
order by s.name

See Also


2. Architecture 

We have classified the architecture of SQL Server into the following parts for easy understanding: 

  • General architecture 
  • Memory architecture 
  • Data file architecture 
  • Log file architecture 


General Architecture 

  • Client: Where the request initiated. 
  • Query: SQL query which is high level language. 
  • Logical Units: Keywords, expressions and operators, etc. 
  • Server: Where SQL Server services were installed and databases reside. 
  • Relational Engine: This is where real execution will be done. It contains Query parser, Query optimizer and Query executor. 
  • Query Parser (Command Parser) and Compiler (Translator): This will check syntax of the query and it will convert the query to machine language. 
  • Execution Plan: It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution. 


 Memory Architecture 

  • One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. 
  • Buffer management is a key component in achieving I/O highly efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer pool to reduce database file I/O. 
  • The buffer pool is further divided into multiple sections. The most important ones being the buffer cache (also referred to as data cache) and procedure cache. Buffer cache holds data pages in memory so that frequently accessed data can be retrieved from cache. The alternative would be reading data pages from the disk. Reading data pages from cache optimizes performance by minimizing the number of required I/O operations which are inherently slower than retrieving data from the memory. 
  • The procedure cache keeps the stored procedure and query execution plans to minimize the number of times that query plans have to be generated. 


Data File Architecture 

Data File architecture has the following components: 

  • File Groups: Database files can be grouped together in file groups for allocation and administration purposes. No file can be a member of more than one file group. Log files are never part of a file group. There are two types of file groups in SQL Server, Primary and User-defined. One file group in each database operates as the default file group.
  • Files : Databases have three types of files - Primary data file (.mdf), Secondary data file(s) (.ndf), and Log file (.ldf). Primary data file is the starting point of the database and points to the other files in the database. Log files hold all of the log information used to recover the database. Database must have at least one log file. We can have multiple log files for one database. The location of all the files in a database are recorded in both master database and the primary file for the database. Most of the time, the database engine uses the file location from the master database. 
  • Extents: Extents are basic unit in which space is allocated to tables and indexes. 
  • Pages: It is the fundamental unit of data storage in MS SQL Server.


Log File Architecture 

Each log file record is identified by Log Sequence Number (LSN). Each log record contains the ID of the transaction that it belongs to. Log records record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed. 

 Different types of operations are recorded in the transaction log. These operations include: 

  • The start and end of each transaction. 
  • Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables. 
  • Every extent and page allocation or de-allocation. 
  • Creating or dropping a table or index. 

Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. This reserved space is freed when the transaction is completed.

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. 

The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size or number of virtual log files cannot be configured or set by administrators. The only time virtual log files affect system performance is if the physical log files are defined by small size and growth_increment values. 

The size value is the initial size for the log file and the growth_increment value is the amount of space added to the file every time new space is required. If the log files grow to a large size because of many small increments, they will have many virtual log files. This can slow down database startup and also log backup and restore operations. 

It is recommended that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value. 


3. Create Database 

A Database is a collection of objects such as table, view, stored procedure, function, trigger, etc. 

In MS SQL Server, two types of databases are available:

  • System Databases 
  • User Databases 

System Databases 

System databases are created automatically when we install MS SQL Server. The following is a list of system databases: 

  • Master 
  • Model 
  • MSDB 
  • Tempdb 

User Databases 

User databases are created by Users - Administrators, developers, and others who have access to create databases. 

Here are two methods that can be used to create a user database. 

Method 1 – Using T-SQL Script or Restore Database 

The following is the basic syntax for creating database in MS SQL Server. 

CREATE DATABASE <yourdatabasename> 
 
RESTORE DATABASE <yourdatabasename> FROM DISK='<Backup file location + file name>'

 Example 

To create database called ‘Testdb’, run the following query:

CREATE DATABASE Testdb 

In this next example 'D:\backup' is location of backup file and 'Testdb_full_backup.bak' is the backup file name):

RESTORE DATABASE Testdb FROM DISK='D:\Backup\Testdb_full_backup.bak' 

Example > WITH MOVE

/* Restore Backup.bak with customized Logical Name and Physical File Names 
 (i.e. you are NOT overriding the original data file, and are instead creating a new database) */

RESTORE DATABASE RestoreTest FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Backup.bak' 
WITH 
 MOVE '_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\RestoreTest.mdf',
 MOVE '_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\RestoreTest.ldf'
;

Tip: Use RESTORE FILELISTONLY to list the LogicalName & PhysicalName of the Primary Data file and the log contained within a backup file. 

RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Backup.bak'


Method 2 – Using SQL Server Management Studio 

Connect to SQL Server instance and right-click on the databases folder. Click on new database and the 'New Database' screen will appear. 

 Enter the database name field with your database name (example: to create database with the name ‘Testdb’) and click OK. Testdb database will be created.


4. Select Database 

Select your database before going ahead with any action. 

Method 1 - Using SQL Server Management Studio 

You can select the Database on the left in the Object Explorer pane. 

If you click 'New Query', the query will be ran on whichever Database is selected in the Available Databases dropdown menu found on the toolbar just above the Object Explorer.

Method 2 – Using T-SQL Script 

USE <your database name> 

Example 

To run your query to select backup history on database called ‘msdb’, select the 'msdb' database by executing the following: 

USE msdb; 

SELECT * FROM backupset; /* retrieves backup history */


5. DROP Database 

To remove your database from MS SQL Server, use drop database command. The following two methods can be used for this purpose. 

Method 1 – Using T-SQL Script 

The following is the basic syntax for removing database from MS SQL Server. 

DROP DATABASE <your database name> 

Example 

To remove database name ‘Testdb’, run the following query. 

DROP DATABASE Testdb 

Method 2 – Using MS SQL Server Management Studio 

Connect to SQL Server and right-click on the database you want to remove. Click the Delete command. Then click the OK button to remove the database. 


6. Login 

A login is a simple credential for accessing SQL Server. Credentials are typically a username and a password. 

SQL Login Types: 

  • Windows authentication 
  • SQL Server authentication 
  • Mapped to certificate 
  • Mapped to asymmetric key 

We are interested in Logins based on Windows credentials and Logins specific to SQL Server. Logins based on Windows credentials allow you to log in to SQL Server using a Windows username and password. If you need to create your own credentials (username and password), you can create a Login specific to SQL Server. 

To create, alter, or remove a SQL Server login, you can take one of two approaches: 

  • Using SQL Server Management Studio
  • Using T-SQL statements

Method #1 – Using SQL Server Management Studio to Create A New Login

  1. After connecting to SQL Server Instance, expand the Security\Logins folder 
  2. Right-click on Users, then click New Login. 
  3. Fill out the fields in the 'Login - New' screen and click OK. 

Example

  • General: Configure Login Type (e.g. 'SQL Server authentication' or 'SQL Server authentication') and Credentials (e.g. Login name, Password, and 'Enforce password policy')
  • Server Roles: public 
  • User Mappings: Select Database, e.g. Northwind. Then select Database role membership, e.g. db_datareader & public 
  • Securables: Grant / Deny database database objects or server, e.g. Connect SQL - Grant. Note, configuring User Mappings automatically adds Securable entry to the SQL Server and Permissions to 'Connect SQL'
  • Status: Grant or Deny 'Permission to connect to database engine' and Enable or Disable Login (e.g. leave default values)

Create A New Login - Method #2 - Using T-SQL Script 

Syntax

CREATE LOGIN yourloginname WITH password='yourpassword'; 

Example

/* SQL user with login */
CREATE LOGIN TestLoginNW WITH password='password';

/* Step 2. Grant this login permissions to access a database. */ 
/* Step 3. Grant rights to tables */
USE Northwind; 
CREATE USER TestUserNW FOR LOGIN TestLoginNW; 

EXEC sp_addrolemember 'db_datareader', 'TestUserNW';

Example > Grant Database Access to another account on your machine or network

/* Pre-requisite: Create Windows account. */ 
/* HARRISR\admin */

/* Step 1. Create a LOGIN to SQL Server for Windows user */
CREATE LOGIN [HARRISR\admin] FROM WINDOWS;

/* Step 2. Grant this login permissions to access a database. */ 
/* Step 3. Grant rights to tables */
USE NorthwindTutorial; 
CREATE USER [admin] FOR LOGIN [HARRISR\admin]; 

EXEC sp_addrolemember 'db_datareader', 'admin';

The Difference Between SQL Server Logins and Database Users 

A Login is used for authentication into a SQL Instance (server level) while a User is used for authorization into a SQL Database. Logins are assigned to server roles (e.g. public or serveradmin) and Database Users are assigned roles within that database (eg. db_datareader).

Logins must be mapped to a database User to connect to a database. If your Login is not mapped to any database user, you can still connect to SQL Server instance using SQL Server Management Studio (SSMS), but you’re not allowed to access any objects in the database. To access any objects in the database, you must have a Login that’s mapped to a User in the database, and that user must be granted appropriate rights in the database.

In short, a Login grants the principal entry into the Server. A User grants access into a single Database. One Login can be associated with many Users (one per Database).


7. Create Users 

User refers to an account in MS SQL Server database which is used to access database. 

Database User Types: 

  • SQL user with login 
  • SQL user without login 
  • User mapped to a certificate 
  • User mapped to an asymmetric key 
  • Windows user 

Users can be created using either of the following two methods. 

Method 1 – Using T-SQL 

Syntax 

CREATE USER <username> FOR LOGIN <loginname> 

Example 

/* SQL user with login */
CREATE LOGIN TestLoginNW WITH password='password';

/* Step 2. Grant this login permissions to access a database. */ 
/* Step 3. Grant rights to tables */
USE Northwind; 
CREATE USER TestUserNW FOR LOGIN TestLoginNW; 
EXEC sp_addrolemember 'db_datareader', 'TestUserNW';

Method 2 – Using SSMS (SQL Server Management Studio) 

  1. Connect SQL Server and expand databases folder. Then expand database called 'Northwind' where we are going to create the user account and expand the security folder. Right-click on users and click on the new user to see the 'Database User - New' screen. 
  2. Enter 'TestUser' in the user name field and click on ellipse to select the Login name called 'TestLogin'.
  3. Click OK to select the login name. Again click OK to create the 'TestUser' user as viewable in the Object Explorer.

Note: Typically, you'll create a Login before creating a User account. However, one of the User types in SQL Server is 'SQL user without login'. As its name implies, a user without login cannot log into SQL Server, but the "User" exists as a database object and may therefore be granted or denied permissions like any other user. SSMS will allow you to create the User and assign Membership role and Securables Permissions. In this scenario, a DBA can retroactively assign a login to a user that's already been created. 


8. Assign Permissions 

Permissions refer to the rules governing the access to securables. You can grant, revoke and deny permissions in MS SQL Server. 

To assign permissions either of the following two methods can be used. 

Method 1 – Using T-SQL 

Syntax 

USE <database name> 
GRANT <permission name> ON <object name> TO <username\principle> 

Example 

To assign select permission to a user called 'TestUser' on object called 'TestTable' in 'TestDB' database, run the following query. 

USE TestDB; 
CREATE TABLE TestTable(
	id INT NOT NULL IDENTITY(1,1),
	data VARCHAR(50) NOT NULL, 
	PRIMARY KEY (id)
);

DENY SELECT ON TestTable TO TestUser;

GRANT SELECT ON TestTable TO TestUser; 

Method 2 – Using SSMS (SQL Server Management Studio) 

  1. Connect to instance and expand folders: <DBInstance>\Databases\TestDB\Secruity\Users\TestUser
  2. Right-click on TestUser and click Properties. The 'Database User' screen appears.
  3. Click Search and select specific options. Click Object Types, select Tables, click OK, then click Browse. Select 'TestTable' and click OK.
  4. Set permission on 'TestTable'. Select checkbox for Grant column under Select permission and click OK.


9. Creating Backups 

A backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types - Full, Differential, and Transactional Log. 

Note: Database Properties > Options > Recovery Model - Full, Bulk-logged, Simple. A Dev system may use 'Simple', where as a system in production instead use 'Full'. 

Backup database can be done using either of the following two methods. 

Method 1 – Using T-SQL 

Full Type 

BACKUP DATABASE <Your database name> TO DISK='<Backup file location + file name>' 

Differential Type 

BACKUP DATABASE <Your database name> TO DISK='<Backup file location + file name>' with differential 

Log Type 

BACKUP LOG <Your database name> to disk='<Backup file location + file name>' 

Example 

The following command is used for full backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_Full.bak' 

BACKUP DATABASE TestDB TO DISK='D:\TestDB_Full.bak' 

The following command is used for differential backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_diff.bak' 

BACKUP DATABASE TestDB TO DISK='D:\TestDB_diff.bak' WITH DIFFERENTIAL 

The following command is used for Log backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_log.trn' 

BACKUP LOG TestDB TO DISK='D:\TestDB_log.trn' 

Method 2 – Using SSMS (SQL SERVER Management Studio) 

  1. Connect to database instance and expand Databases folder. 
  2. Right-click on whichever database and select Tasks. Click Backup and the 'Back Up Database' screen will appear. 
  3. Configure the Source, Backup set, Destination, Overwrite media, and other settings, then click OK to create the database backup. 

See Also 


10. Restoring Databases 

Restoring is the process of taking a backup file and turning it back into an operational database. 

To restore a database, use either of the following two methods. 

Method 1 – T-SQL 

RESTORE DATABASE <Your database name> FROM DISK='<Backup file location + file name>' 

Example 

The following command is used to restore database called 'TestDB' with backup file name 'TestDB_Full.bak' which is available in 'D:\' location if you are overwriting the existed database. 

RESTORE DATABASE TestDB FROM DISK='D:\TestDB_Full.bak' WITH REPLACE 

If you are creating a new database with the RESTORE command you may use the MOVE option like the following command. 

/* BACKUP DATABASE TestDB TO DISK='D:\TestDB_Full.bak'; */


RESTORE DATABASE TestDB FROM DISK = 'D:\TestDB_Full.bak' 
WITH 
 MOVE 'TestDB' TO 'D:\Data\TestDB.mdf', 
 MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

Method 2 – SSMS (SQL Server Management Studio) 

  1. Connect to database instance, right-click on Databases folder, and click Restore. 
  2. Select Device radio button and click on ellipse and then Add to select the backup file, then click OK. 
  3. Click OK to restore the database. 

Method 2 – SSMS (SQL Server Management Studio) 

  1. Connect to database instance, right-click on Databases folder, and click Restore. 
  2. Select Device radio button and click on ellipse and then Add to select the backup file, then click OK. 
  3. Click OK to restore the database. 


11. Monitor Database 

Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc. 

SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc. 

Database status can be checked whether it is online or in any other state as shown in SQL Server Management Studio. 

As per the Object Explorer, all databases are typically in an 'Online' status. If any database is in any other state, then that state will be shown as 'Offline'. 

Resources


12. High Availability Technologies (HA)

High Availability (HA) is the solution\process\technology to make the application\database available 24x7 under either planned or un-planned outages. 

There are five main options in MS SQL Server to achieve\setup a High Availability solution for SQL Server databases: 

  1. Replication: The source data will be copied to destination through replication agents (jobs). Object level technology. 
  2. Log Shipping: The source data will be copied to destination through Transaction Log backup jobs. Database level technology. 
  3. Mirroring: The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology. 
  4. Clustering: The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. 
  5. AlwaysON Availability Groups: The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage. 

Resources


13. Services 

MS SQL Server provides the SQL Server service which is mandatory for database creation and maintenance. 

Other add-on services are available for different purposes: 

  • SQL Server Reporting Services (SSRS): A server-based report generating software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with Crystal Reports and other business intelligence tools.
  • SQL Server Integration Services (SSIS): This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. 
  • SQL Server Analysis Services (SSAS): This service is used to analyze huge amounts of data to help with business decisions. There are two modes - Native Mode (SQL Server Mode) and Share Point Mode. There are two models - Tabular Model (for Team and Personal Analysis) and Multi-Dimensions Model (for Corporate Analysis). 
  • SQL Server Agent 
  • SQL Server Browser 
  • SQL Server Full Text Search 

Start or Stop Server Services 

To start or stop any of the services, the following methods can be used: 

  1. Services.msc: Go to Run, type services.msc and click OK. The 'Services' screen appears. 
  2. SQL Server Configuration Manager: Start -> All Programs -> MS SQL Server 20XX -> Configuration Tools -> SQL Server Configuration Manager.
  3. SQL Server Management Studio (Stop only): Connect to the instance in SSMS. Then in Object Explorer, right-click on the instance name. Note, we cannot use the SSMS method to start the Services as unable to connect due to services already stopped state.


SSIS Basic Architecture 

Solution (Collection of projects) ? Project (Collection of packages) ? Package (Collection of tasks for ETL and admin operations) 

Under Package, the following components are available: 

  • Control Flow (Containers and Tasks) 
  • Data Flow (Source, Transformations, Destinations) 
  • Event Handler (Sending of messages, Emails) 
  • Package Explorer (A single view for all in package) 
  • Parameters (User interaction) 


14. Execution Plans 

The Execution Plan is generated by the Query Optimizer, which determins how to perform queries. Execution plans are stored in memory called plan cache, so they can be reused. 

There are two different execution plans: 

  • Estimated execution plan indicates optimizer view. 
  • Actual execution plan indicates what executed the query and how was it done. 

To view the Estimated Execution plan: 

  • Connect to SQL Server instance (in SSMS)
  • Click New Query option and write the following query: 
SELECT * FROM [TableName];
  • Then click the 'Display Estimated Execution Plan' button within the Standard toolbar (near the 'Execute' button)
  • Roll the mouse over the icons in the 'Execution plan' pane

To view the Actual Execution Plan: 

  • Connect to SQL Server instance (in SSMS)
  • Click New Query option and write the following query: 
SELECT * FROM [TableName];
  • Click the 'Include Actual Execution Plan' button in the toolbar. 
  • Click Execute 
  • Roll the mouse over the icons in the 'Execution plan' pane


Resources 

Microsoft.com 

Downloads 

Microsoft Docs 

Wikipedia 


Further Reading 

Free Database eBooks

SQL Server Reporting Services(SSRS)

Backups

High Availability Technologies (HA)

Other 

Alfredo garcia jr

Network Operations Center Technician at Fifth Third Bank

3 年

Thanks for sharing. Good refresher

赞
回复
1 次回应

要查看或添加评论，请登录

更多精彩文章

社区洞察

其他会员也浏览了