Microsoft SQL Server Database Administration.

Topics In This Module. Authentication Types in SQL Server  Types of Login  Windows Login  SQL Login  Creating Users and Logins  Server roles  Password policy  Understanding Database and Server Roles  Permissions  Working on access level issues  Orphan users Finding and Fixing  Important Queries    *** ============================================================================*** Security of SQL Server Instance Authentication Logging into a server or a domain is called authentication, the authentication in SQL server is of two types they are: Windows Authentication SQL Authentication. Windows  Authentication   One who has access to server and the user is in SQL logins as domain login or group login can access the server with this type, simply said Domain accounts or OS accounts are used in Windows authentication. Note : All windows/OS level security policies get applied to this login/user hence this authenticatio...

Topics In This Module. Database Backups  Why we need backups  Types of Backup  Full Backup  Differential Backup  Transaction Log Backup  Copy-only, Mirrored, Split and Tail log Backups  Differences between backups  Backup Strategy  Understanding how the data moving from Log to Data File, CHECKPOINT  Monitoring the space usage of Log File and fixing  Checking the backup files VALID or CORRUPTED  Backup storage tables  Important Queries  Performing  Restorations  Types of Restoration  Attach and Detach  Shrinking files   *** ============================================================================*** Backups Why backups are required? If any hardware or software failures occurs we can recover data or databases from backups from a copy of SQL server data. Modes of Backup There are two modes in which backup can be taken they are: Hot/Online Backup. Cold/Offline Backup. Hot/Online Backups: I...

All SQL Server Agent details are maintained by MSDB databases, which store all the below information All individual jobs and maintenances plan related jobs. Schedules of all jobs. Operator, Database mails. All Alerts related information. All the Error log information is stored here. What is SQL server Agent? SQL Server is an individual service and it’s a default service which comes with the SQL server engine service, All the agent is stored in msdb. Why SQL server Agent? This SQL server agent is used for providing automation for all SQL server administrative tasks. What is operator? All the notifications of a job are received by this person based on the job status. What is Database mail? Database mail is the from address from where and which SMTP the notification to Operator need to send is given while configuring database mail, Only user with DatabaseMailUser role can configure this, By default Database mail is not enabled in older versions, we use SSAC(SQL Surface Area Conf...

What is HA? And Why Is HA used? HA: - High Availability. We use High Availability technique to keep the database always online, and always available to the users, without any human interventions the databases will failover from one node to another to keep the databases available always. What is DR? And Why is DR used? DR: Disaster Recovery. We use Disaster Recovery technique to keep the data in database safe and databases available to the users, only with the human intervention the databases will failover from one node to another to keep the data and database available always. The main difference between HA and DR is, In HA the database will flip/failover  from one node to another without any human interventions, but in DR the  databases will flip/failover only with human interventions. Some of the HA and DR techniques in SQL server are: Log Shipping – DR Plan Mirroring – can be a HA or DR Replication – DR Plan AOAG – Always on Availability Group – Can...

Topics In This Module. Log Shipping  Working with Log Shipping  Terminology  Pre-requisites to implement Log Shipping  Different modes of Log Shipping  Configuring Log Shipping  Monitoring  Manually Performing Failover  Log Shipping Tables  Some Real Time Scenarios    *** ============================================================================*** Log Shipping What is Log Shipping?  Log Shipping is a technique where logs or log backups from primary server(database) are transferred or shipped to secondary server(database).  In this technique the log backup is taken in primary server databases to a network path, and then the file is copied to the available location where secondary server database is accessible, and then the backup is restored to secondary server database.  All the above communication is done using jobs; Hence we can say this as Agent based communication (SQL Server Agent)  T he log ship...

Topics In This Module. Working with Database Mirroring  Terminology  Pre-requisites to implement Mirroring  Pre check mirroring ports ENABLED or DISABLED  Architecture  Operating Modes  Differences between Modes  Port numbers involved in Mirroring  Requirements for Mirroring  Configuring Mirroring  Mirroring failover  Issues on Mirroring     *** ============================================================================*** Mirroring What is Mirroring? Mirroring is a technique where each and all individual transactions from buffer are  transferred from one database (Principal) to another database (Mirror), Through end  points. In this technique whenever a DML is executed, according to WAL concepts the  transaction need to written to buffer as well as log file, from the primary server buffer the  transactions are transferred to other server buffer and log file only then the transaction  shows succes...