MS SQL DBA Training

After completion of this SQL Server DBA training you will be able to gain proficiency in the following areas:

• SQL Server Installation and its components 
• Storage design  and database architecture 
• Configuration of security permissions 
• Test connectivity and feature access 
• SQL Server standby database configuration for reporting purpose
• SQL Server Agent management 
• Designing and implementing the migration strategy 
• SQL Server Instance configuration 
• Back-up strategies configuration and management 
• Database restoring, implementation and maintenance
• Implementing AlwaysOn 

• MS SQL is one of the widely accepted databases by global organizations to manage their large volumes of data. 
• There is a huge demand for database administrators to manage and secure database servers.  
• According to Indeed.com, the average compensation received by a SQL Database Administrator amounts to $91,000 per annum.

Following are the professionals who get benefited from learning this course:

• Database  Developers and Administrators 
• Project Managers, Testers, and Analysts. 
• Hadoop and Bigdata professionals

Yes, upon the successful completion of the MS SQL Server DBA training you will receive the course completion certificate from SK Trainings. This certificate is valid across all the top MNCs and simplifies your job search process.

Yes, you will get all assistance from the MS SQL experts to clear your certification exam. Moreover, you will be provided with the experts designed MS SQL Server DBA Course material to clear your certification exam.

• Configuring SQL Server Instances
• Instance-Level Settings
• Database Configuration and Standardization
• Distributed Transaction Coordinator
• Configuring Database Mail
• Revision on chapter 1 and chapter 2
• Q and A session

• Deploying and Configuring Analysis Services
• Deploying and Configuring Reporting Services
• Deploying and Configuring SharePoint Integration
• Configuring SQL Server Integration Services Security
• Managing Full-Text Indexing
• Configuring FILESTREAM
• Configuring File Tables

• Designing and Managing File groups
• Configuring and Standardizing Databases
• Understanding Contained Databases
• Using Data Compression
• Encrypting Databases with Transparent Data Encryption
• Partitioning Indexes and Tables
• Managing Log Files
• Using Database Console Commands

• Upgrading an Instance to SQL Server 2012
• Migrating a Database to a SQL Server 2012 Instance
• Copying Databases to Other Servers
• Migrating SQL Logins

• Designing and Managing File groups
• Lesson porting Data
• Copying and Exporting Data
• Using the SQL Server Import and Export Wizard
• Using BCP to Import and Export Data
• Importing Data by Using BULK INSERT
• Importing Data by Using OPENROWSET (BULK)
• Using Format Files
• Preparing Data for Bulk Operations
• SELECT INTO

• SQL Logins
• Server Roles
• User-Defined Server Roles
• Credentials

• Database Users
• Database Roles
• Contained Users
• Least Privilege
• Application Roles

• Executing Jobs by Using SQL Server Agent
• Managing Alerts
• Managing Jobs
• Monitoring Multi-Server Environments

• Understanding Backup Types
• Backing up System Databases
• Backing up Replicated Databases
• Backing up Mirrored Databases
• Backing up AlwaysOn Replicas
• Using Database Checkpoints
• Using Backup Devices
• Backing Up Media Sets
• Performing Backups
• Viewing Backup History

• Restoring Databases
• Performing File Restores
• Performing Page Restores
• Restoring a Database Protected with Transparent Data
• Encryption
• Restoring System Databases
• Restoring Replicated Databases
• Checking Database Status

• Understanding Securable
• Assigning Permissions on Objects
• Managing Permissions by Using Database Roles
• Protecting Objects from Modification
• Using Schemas
• Determining Effective Permissions

• Troubleshooting Authentication
• Troubleshooting Certificates and Keys
• Troubleshooting Endpoints
• Using Security Catalog Views

• Using SQL Server Audit
• Configuring Login Auditing
• Using c2 Audit Mode
• Common Criteria Compliance
• Policy-Based Management

• Database Mirroring
• Mirroring Prerequisites
• Configuring Mirroring with Windows Authentication
• Configuring Mirroring with Certificate Authentication
• Changing Operating Modes
• Role Switching and Failover
• Monitoring Mirrored Databases
• Upgrading Mirrored Databases

• Replication Architecture
• Replication Types
• Snapshot Replication
• Transactional Replication
• Peer-to-Peer Transactional Replication
• Merge Replication
• Replication Monitor
• Controlling Replication of Constraints, Columns, and Triggers
• Heterogeneous Data

• Log shipping Prerequisites
• Benefits of Log shipping
• Configuring Log shipping between two servers
• Troubleshooting Log shipping issues

• Fulfilling Edition Prerequisites
• Creating a Windows Server 2008 R2 Failover Cluster
• Installing a SQL Server Failover Cluster
• Multi-Subnet Failover Clustering
• Performing Manual Failover
• Troubleshooting Failover Clusters

• What Are AlwaysOn Availability Groups?
• Meeting Availability Group Prerequisites
• Configuring Availability Modes
• Selecting Failover Modes
• Configuring Readable Secondary Replicas
• Deploying AlwaysOn Availability Groups
• Using Availability Groups on Failover Cluster Instances

• Getting Started with Performance Monitor
• Capturing Performance Monitor Data
• Creating Data Collector Sets

• Capturing Activity with SQL Server Profiler
• Understanding SQL Trace
• Reviewing Trace Output
• Capturing Activity with Extended Events Profiler

• Monitoring Activity
• Working with Activity Monitor

• Capturing and Managing Performance Data
• Analyzing Collected Performance Data

• Monitoring Disk Usage
• Monitoring Memory Usage
• Monitoring CPU Usage

• Understanding the Anatomy of a Balanced Tree (B-Tree)
• Understanding Index Types and Structures
• Designing Indexes for Efficient Retrieval
• Understanding Statistics
• Creating and Modifying Indexes
• Tracking Missing Indexes
• Reviewing Unused Indexes

• Defining Transactions and Transaction Scope
• Understanding SQL Server Lock Management
• Using AlwaysOn Replicas to Improve Concurrency
• Detecting and Correcting Deadlocks
• Using Activity Monitor
• Diagnosing Bottlenecks
• Using Reports for Performance Analysis

SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time. Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store. Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real-time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.

Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built into the index.

SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.

The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table. In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.

1433

DCL stands for Data Control Language.

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created/rebuilt.

The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to the desired value there and click OK to save the changes. The other option of viewing and changing this value is using

There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages of Replication is that it can be configured on databases which are in simple recovery model.

The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. The subscriber is the destination where the publishers data is copied/replicated.

I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.