MSSQL 101
What is SQL?
SQL is a standard language for storing, manipulating and retrieving data in databases
What is MSSQL Server?
a relational database management system (RDBMS).
What is a database?
an organized collection of structured information, or data, typically stored electronically in a computer system
What are server roles?
Fixed server roles, which are built into SQL Server, and do not allow you to modify permissions or user-defined roles. We cover the main fixed server roles below.
User-defined roles, which you can customize for your organization’s security requirements.
What is the Priniciple of Least Priviledge?
concept in cybersecurity that advocates for granting individuals or systems the minimum level of access and permissions necessary to perform their specific tasks or functions, and nothing more
What are the three types of roles you can use to restrict access to data in your database in SQL Server?
server-level roles, database-level roles, and application-level roles.
Details on Server-Level Roles
Server-level roles help manage permissions for the entire SQL Server instance
Fixed server roles cannot be changed
public—default role for server principals who do not have specific securable object permissions. Only assign public permissions to objects that can be made available to all users. You cannot revoke public permission from any server role.
dbcreator—can alter, create, drop, or restore databases.
diskadmin—can manage disk files.
bulkadmin—can execute BULK INSERT
setupadmin—can add/remove linked servers and run Transact-SQL
processadmin—can end running processes in the SQL server instance.
securityadmin—can administer logins, can reset SQL server login passwords, and grant, deny or revoke server-level permissions or database-level permissions
serveradmin—can alter server configuration and shut it down
sysadmin—can perform all server activities.
Database Roles
db_owner—allowed to perform all maintenance and configuration activities on the database, as well as dropping the database
db_securityadmin—can modify custom role memberships and manage permissions. Monitor this role closely as it has the ability to escalate privileges.
db_accessadmin—can add/remove database access for Windows groups and logins, as well as SQL Server logins
db_backupoperator—can perform database backups
db_ddladmin—can run data definition language (DDL) commands
db_datawriter—can add, change, or delete any user table data.
db_datareader—limited to reading data from user tables
db_denydatawriter—are not allowed to add, modify or delete user table data
db_denydatareader—cannot read any of the data in a user table
SQL Server Authentication mode
Users need to specify the login and password while connecting to SQL Server.
Windows Authentication
In Windows authentication, the user should first authenticate himself within Active Directory. SQL Server authenticates users through the Windows principal token in the OS. With that, SQL Server does not ask for a password for identity validation. Therefore, Windows confirms users’ identities for authentication
What does it mean to take a backup?
Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.
What is a backup?
A copy of SQL Server data that can be used to restore and recover the data after a failure.
A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups.
Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.
Recovery Model
A database property that controls transaction log maintenance on a database
The recovery model of database determines its backup and restore requirements.
Three recovery models exist: simple, full, and bulk-logged.
Restore
A multi-phase process that copies all the data and log pages from a specified SQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.
Recovery Model- Simple
The SIMPLE recovery model is the simplest among the available models.
It supports full, differential, and file level backups.
Transaction log backups are not supported. The log space is reused whenever the SQL Server background process checkpoint operation occurs. The inactive portion of the log file is removed and is made available for reuse.
Reasons to choose the simple database recovery model
Most suited for Development and Test databases
Simple reporting or application database, where data loss is acceptable
The point-of-failure recovery is exclusively for full and differential backups
No administrative overhead
Recovery model- Full
In this recovery model, all the transactions (DDL (Data Definition Language) + DML (Data Manipulation Language)) are fully recorded in the transaction log file.
The log sequence is unbroken and is preserved for the databases restore operations.
Unlike the Simple recovery model, the transaction log file is not auto-truncated during CHECKPOINT operations.
Reasons to choose the full database recovery model:
Supporting mission critical applications
Design High Availability solutions
To facilitate the recovery of all the data with zero or minimal data loss
If the database designed to have multiple filegroups, and you want to perform a piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
Allow arbitrary point-in-time restoration
Restore individual pages
Incur high administration overhead
Recovery Model- Bulk_Logged
It’s a special purpose database configuration option and it works similar to FULL recovery model except that certain bulk operations can be minimally logged. The transaction log file uses a technique known as minimal logging for bulk operations. The catch is that it’s not possible to restore specific point-in-time data.
Reasons to choose the bulk logged recovery model:
Use minimal logging technique to prevent log file growth
If the database is subjected to periodic bulk operations
When to use each recovery model?
If the database is a development or a test server, the simple recovery model should mostly be sufficient
However, if a database is a production one, it is generally suggested to go with a full recovery model.
What are the most common types of backups available in SQL Server:
Full
Differential
Transaction log
Tail Log backup
Full Backup
A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup.
This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc.
Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.
A full backup creates a complete backup of the database as well as part of the transaction log, so the database can be recovered.
This allows for the simplest form of database restoration, since all of the contents are contained in one single backup.
Differntial Backip
A differential database backup is the superset of the last full backup and contains all changes that have been made since the last full backup.
So, if there are very few transactions that have happened recently, a differential backup might be small in size, but if you have made a large number of transactions, the differential backup could be very large in size.
Transaction Log Backup
The log backup, as its name implies, backs up the transaction logs. This backup type is possible only with full or bulk-logged recovery models.
A transaction log file stores a series of the logs that provide the history of every modification of data, in a database.
A transaction log backup contains all log records that have not been included in the last transaction log backup.
what is a database administrator
Database administrators (DBAs) are responsible for maintaining the performance, security, and availability of databases.
They install, configure, backup, restore, monitor, and troubleshoot databases, in addition to enforcing policies and standards. DBAs also work with users and developers to provide access and guidance on database issues.
DBAs need to have a solid understanding of database systems, such as relational, NoSQL, or cloud-based databases, as well as the operating systems, networks, and hardware that support them.
They require skills in database administration tools such as SQL Server Management Studio, Oracle Enterprise Manager, or MongoDB Compass. DBAs should also know scripting languages like PowerShell, Python, or Bash