Administering Microsoft SQL Server 2012/14/16 Databases Course

Course Details:

Length: 5 days

Price: $2,925/person (USD)

Group Price: Request Quote

Training Reviews

Course Features:

Live Instructor Teaching

Certificate of Completion

Digital Badge

Courseware: Print

Free 6 Month Online Retake

Hands-On Learning?: Yes

Software Lab Included?: Yes

Delivery Methods:

Live Online

Individuals and Groups
@ Your Location

Onsite for Teams

Group Teams
@ Your Organization

This is an instructor-led course. It is taught by an instructor live online or at organizations for groups.
For team training, we can teach onsite at your office or private live online.


Course Overview

You have created databases and executed queries on them. You are now ready to perform database administration tasks such as installation of SQL Server instances, configuration of SQL Server databases and settings, monitoring database performance, controlling user access to databases, and database maintenance. In addition to these tasks, SQL Server 2012 provides many advanced features that enable you to manage databases efficiently. By familiarizing yourself with these tasks and features, you can plan, deploy, administer, and maintain SQL Server databases efficiently. In this course, you will perform database administration tasks.

Register Early: Registration Deadline is 2 Weeks Prior to Class Start.

Learning Objectives

Upon successful completion of this course, students will be able to administer SQL Server 2012 databases.
You will:

  • Identify the SQL Server Platform and Tools
  • Deploy SQL Server
  • Configure SQL Server
  • Manage Databases
  • Implement Advanced Security Settings
  • Apply Encryption and Compression
  • Work with Indexes and Log Files
  • Work with Backup and Restore
  • Implement High Availability
  • Optimize Server Performance
  • Troubleshoot Issues and Recover Databases
  • Perform Advanced Management Tasks

Course Notes

Course Taught With: 2012 software
SQL Server Versions That Can Attend: 2016, 2014, and 2012

Target Audience

This course is designed for Database Administrators who administer and maintain SQL Server databases. This course will be attended by database professionals who are familiar with SQL Server databases and who are responsible for administering and maintaining SQL Server databases as their primary job responsibility. In addition, this course can be attended by individuals who develop applications that access SQL Server database.


Course Topics

Lesson 1: Identifying the SQL Server Platform and Tools
Topic A: Overview of SQL Server Features
Topic B: Create Filegroups and Partitions


Lesson 2: Deploying SQL Server
Topic A: Install SQL Server
Topic B: Migrate SQL Server From a Previous Version


Lesson 3: Configuring SQL Server
Topic A: Configure Server Properties
Topic B: Configure Memory and CPU
Topic C: Configure Database Mail


Lesson 4: Managing Databases in SQL Server 2012
Topic A: Create a Contained Database
Topic B: Configure Database Options


Lesson 5: Managing SQL Server Security
Topic A: Configure Server Security
Topic B: Create Server Roles
Topic C: Configure Database Security


Lesson 6: Implementing Advanced Security Settings
Topic A: Create Certificate-Based Logins
Topic B: Apply Auditing and Policy-Based Management Strategies


Lesson 7: Applying Encryption and Compression
Topic A: Apply Encryption on Databases
Topic B: Compress a Database Table


Lesson 8: Working with Indexes and Log Files
Topic A: Work with Full Text Indexes
Topic B: Configure Advanced Storage and Indexing Settings
Topic C: Configure Log Files


Lesson 9: Working with Backup and Restore
Topic A: Perform a Backup
Topic B: Restore a Database
Topic C: Import and Export Data
Topic D: Configure the SQL Server Agent


Lesson 10: Implementing High Availability
Topic A: Work with Clustering
Topic B: Work with Replication
Topic C: Work with Mirroring and AlwaysOn
Topic D: Work with Central Management Servers


Lesson 11: Optimizing Server Performance
Topic A: Manage Server Performance
Topic B: Optimize Indexes


Lesson 12: Troubleshooting Issues and Recovering Databases
Topic A: Troubleshoot Database Issues
Topic B: Perform a Database Recovery


Lesson 13: Performing Advanced Database Management Tasks
Topic A: Manage Database Infrastructure
Topic B: Configure SSIS

Course FAQs

What are the available class formats?

This course is taught by a live instructor and is available in two class formats:

  1. Live Online for Individuals
  2. Onsite/Online for Private Groups

What are SQL Server planning and installation procedures?

SQL Server planning and installation procedures are important steps to take when setting up a database server. Proper planning will ensure that your system can meet the needs of your business and provide maximum performance. The installation procedure should follow best practices for security, availability, scalability, and maintainability.

Before beginning the installation process, you should have a clear understanding of the specific requirements of your system and the environment in which it will be used. This includes a detailed plan for how you will configure security, networking, storage, and other components. If possible, it is also important to have an estimate of usage patterns so that any potential bottlenecks can be identified early on.

Installation begins with the setup wizard, which will walk you through all the necessary steps. During this process, you will choose the type of installation and provide information such as authentication method, collation settings, backup location, and other options. Once everything is configured, the setup program will install the SQL Server components and any additional features you have selected.

Once completed, the installation is not complete until you have properly configured security, networking, and any other settings necessary for the system to function optimally. It is also important to ensure that the server has the most up-to-date version of the software and avoid using deprecated features.

Finally, following best practices for database maintenance will make sure that your SQL Server system runs smoothly and is always available when you need it. This includes regularly running backups to minimize data loss, periodically reviewing error logs for any issues, and making sure your system is patched with the latest security updates. Properly completed SQL Server planning and installation procedures will ensure that your database server can meet the needs of your business for years to come.

What are SQL Server instances?

A SQL Server instance is a complete database environment running on one server, allowing multiple applications and users to access the same data simultaneously. It is possible to deploy many instances on one server, depending on your needs. Each instance runs independently of the other, providing a secure, isolated environment for different workloads. The ability to run multiple instances provides organizations with flexibility and scalability, allowing them to manage their resources more efficiently. With the right configuration, a single server can host multiple instances of SQL Server, providing an economical way to meet many different data needs. This makes SQL Server instances ideal for businesses that need to quickly deploy database applications or run complex queries over large datasets. Instances are also an important factor in maintaining high availability and disaster recovery for critical applications. By running multiple instances, organizations can ensure that their data is always accessible even when one instance fails.

SQL Server instances are also used to manage access permissions and configuration settings. Each instance can be customized on a per-user basis, allowing administrators to tailor the environment to the specific needs of their users. This enables organizations to create a secure, standardized environment that allows only authorized users to access certain data or configurations. SQL Server instance administration is also critical for maintaining performance and security across an entire organization's infrastructure. By regularly monitoring each instance, administrators can ensure that the system is running optimally and any potential security threats are identified and addressed quickly.

Overall, SQL Server instances provide organizations with a powerful way to manage their data resources. By deploying multiple instances on one server, businesses can benefit from cost savings while providing users with a secure, customizable environment tailored to their specific needs. The ability to customize configurations and manage access permissions also helps organizations maintain high-performance levels and secure their data. As more organizations move towards database-driven applications, SQL Server instances will become even more important for managing their data resources.

What are SQL Server migrating, importing and exporting features?

SQL Server provides powerful tools for migrating, importing, and exporting data. These features help ensure the efficient and secure transfer of data between databases or between other sources such as flat files or spreadsheets.

Migration involves the process of transferring a database from one server to another, while still retaining its object structures and data. This can be done using the in-built Migration Wizard, which allows users to migrate databases easily and quickly across different versions of SQL Server.

Importing data into an existing database can be done using the Import/Export Data Wizard. This tool enables users to import or export data from multiple sources such as flat files, spreadsheets, other databases, or OLE DB/ODBC data sources.

Exporting data to another database or other source can be done quickly and easily using the Export Wizard, which allows users to export a wide variety of formats such as CSV files, Excel spreadsheets, Access tables, and text files.

These features provide powerful tools for managing your databases efficiently and securely in SQL Server. With the help of these features, you can easily migrate, import, and export data between databases or other sources with ease.

What are SQL Server logins, roles and users?

SQL Server logins are the server-level credentials used for authentication and access control. They provide the ability to connect to a SQL Server instance and then grant access permissions to roles, users, and object owners. Logins can be either Windows Authenticated or SQL Server Authenticated.

Roles are groups of related users with the same permissions. They provide a way to grant or revoke access for multiple users at once, which is an important part of database security. SQL Server logins can be assigned to roles; this grants login access to all objects and data owned by members of that role.

Users are individual accounts created within a database instance. They are granted access to objects and data within the database, but cannot access other databases. Users are associated with logins, so any user granted access to an object must also have a login with the appropriate permissions.

With careful use of these tools, it is possible to create secure and robust SQL Server installations. Logins, roles and users all play an important role in controlling who has access to what data, helping to ensure the integrity of your SQL Server.

What are SQL Server security features?

SQL Server security features are essential for ensuring the safety and integrity of your data. These features provide comprehensive protection against various types of malicious attacks, as well as provide user authentication and access control. Some of the most important SQL Server security features include encryption, auditing, and role-based access control (RBAC). Encryption is used to protect data at rest while auditing allows you to track user activity and suspicious behavior. RBAC helps limit access to certain users, which also helps protect your data from unauthorized access. SQL Server also offers a variety of other security options including data masking, Transparent Data Encryption (TDE), column-level validation, and more. By taking advantage of these features, you can ensure that your data is protected and secure.

What is SQL Server mirroring and replication?

SQL Server mirroring and replication are two high-availability features that provide improved data availability and protection.

SQL Server Mirroring replicates a single database between two or more servers. The principal server holds the active copy of the database while one or more mirrors serve as hot standby copies. If the principal server fails, any of the mirror servers can assume the role of principal and make the database available for users.

SQL Server Replication is an asynchronous data distribution mechanism that copies data from one database to another. It supports a variety of replication scenarios including snapshot, transactional, and merge replication. Replication can be used to synchronize and distribute data between two or more databases located on different servers. It is also a good way to copy data between databases located in disparate geographic locations.

Both SQL Server Mirroring and Replication are designed to increase the availability of your data and minimize the risk of losing critical information while keeping your system up-to-date. By using these high-availability features, you can ensure that your data is always available and protected against outages, including network or server failures.

What is SQL Server clustering and AlwaysOn?

SQL Server clustering is a high-availability solution that helps ensure the continuity of business operations. It works by connecting multiple computers to act as one unit, with the data stored in all nodes being synchronized. If one node fails, then another can take its place without any interruption or loss of data. Microsoft's AlwaysOn feature provides further protection through automatic failover and increased read scalability. By using AlwaysOn, multiple replicas of the database can be hosted across multiple nodes to provide an additional layer of redundancy and availability. This helps ensure that the data remains available even in the event of a node failure or other issues. Additionally, AlwaysOn allows for offsite backups, improved performance due to load balancing, and the ability to access data remotely. With all these features combined, SQL Server clustering and AlwaysOn provide comprehensive protection for your vital business data.

This solution is ideal for any organization that relies heavily on its database for mission-critical operations, such as financial firms or healthcare providers. It helps ensure maximum uptime for any applications or services that use your database, so you can be confident that your data is always safe and accessible. By leveraging these features from Microsoft SQL Server, you can keep your business running smoothly and efficiently without any interruptions.

In summary, SQL Server clustering with AlwaysOn provides a powerful solution for protecting and managing your organization's data. Not only does it keep your data safe from outages, but it also offers improved performance and scalability compared to traditional methods. With its combination of redundancy, automatic failover, and remote access, SQL Server clustering and AlwaysOn are ideal solutions for any business that needs to ensure the continuity of its operations.

What are SQL Server troubleshooting features?

SQL Server troubleshooting features enable IT professionals to quickly identify and address problems with their database systems. These features include the ability to review recent events within the server, trace user activity, view system health status, access performance data, and generate reports. Having accurate information about a system's performance can help an IT team identify potential problems before they become major issues. Additionally, SQL Server troubleshooting features help IT teams save time and money by reducing the amount of manual investigation needed to identify and fix problems. Furthermore, detailed reporting capabilities allow for deeper insight into system performance and help to ensure that any issues are quickly addressed before they can lead to costly downtime or data loss. By utilizing these features, IT teams can maximize their system performance and ensure optimum reliability.

Overall, SQL Server troubleshooting features provide an invaluable tool for any IT team looking to optimize the performance of their database systems. With accurate data and powerful reporting capabilities, these features make it easier than ever to identify problems and address them efficiently. When used correctly, they can help prevent downtime and data loss, saving money and time for businesses. Ultimately, these features can help any organization maintain a reliable, efficient system and ensure the best possible performance from their database systems.

What are SQL Server indexes and concurrency?

SQL Server indexes and concurrency are important concepts when it comes to managing databases. An index is a data structure used to quickly locate records in a database table or view. This helps to speed up the retrieval of information, as well as increase transaction performance. Concurrency refers to the ability of multiple users or processes to access and modify data simultaneously, without any conflicts. SQL Server provides various locking mechanisms to manage concurrency and ensure the integrity of data. These locks are used to prevent conflicting concurrent accesses, while still allowing multiple users or processes to read or modify data at the same time. By properly managing indexes and ensuring correct concurrency control, companies can improve their database performance and maintain data integrity. It is important to understand these concepts to effectively manage databases and maintain database performance.

The SQL Server Query Optimizer uses indexes to identify the most efficient way to retrieve data from a database table or view. By properly indexing columns, users can improve query performance by reducing I/O and CPU utilization. Additionally, proper indexing can help to reduce the amount of time it takes for queries to finish. Indexes also play a role in concurrency control, as they allow multiple users or processes to access and modify data simultaneously without causing any conflicts.

To ensure proper concurrency control, SQL Server provides various locking mechanisms such as transaction locks, row-level locks, page-level locks, and table-level locks. These locks are used to prevent conflicting concurrent accesses while still allowing multiple users or processes to read or modify data at the same time. By properly managing concurrency, companies can ensure that their databases remain consistent and reliable.

In conclusion, SQL Server indexes and concurrency control are important concepts for managing databases. Properly indexing columns and using the appropriate locking mechanisms can help to improve query performance, ensure data integrity and maintain database reliability. Understanding these concepts is essential to effectively managing databases and maintaining database performance.

What are SQL Server backup and restore features?

SQL Server backup and restore features are critical components of database management. By backing up the entire database, or portions thereof, an administrator can ensure that data is secured in case of disaster or other unforeseen circumstances. Backing up also allows for restoring the database to a prior state if necessary. Additionally, backups may provide the means to migrate databases from one server to another, or even duplicate the same database in multiple locations.

Restoring a database with SQL Server can be done using either complete backups, file and filegroup backups, differential backups, or transaction log backups. Complete backups protect from data loss by capturing the entire database's contents at once. File and filegroup backups allow administrators to select specific files or filegroups to back up, and restore, while differential backups capture changes since the last full backup. Finally, transaction log backups provide the ability to roll database changes forward or backward as needed.

To ensure that data is securely available in the event of an emergency, regularly scheduled backups are highly recommended by industry best practices. Fortunately, SQL Server's backup and restore features make this process highly configurable and easy to manage.

It is also important to properly maintain the backups to ensure their integrity. To achieve that, administrators should apply the 3-2-1 Rule: keep three copies of your data, store them on two different mediums (e.g. disk, cloud), and have one copy offsite. Furthermore, to ensure data availability in the event of a disaster, having an air-gapped backup on tape or other removable media is recommended.

Ultimately, SQL Server's backup and restore features make it easy for administrators to keep their databases secure and available. Regularly scheduled backups and the 3-2-1 Rule ensure that data is available when it is needed, and by using proper maintenance of backups, administrators can rest assured their data will be safe.

Related Microsoft SQL Server Information:

How Much Does Microsoft SQL Training Cost?

Public instructor-led Microsoft SQL Server course prices start at $495 per student. Group training discounts are available.

Self-Paced Microsoft SQL Server eLearning courses cost $600 at the starting point per student. Group purchase discounts are available.

What Microsoft SQL Server Skills Should I Learn?

A: If you are wondering what Microsoft SQL Server skills are important to learn, we've written a Microsoft SQL Server Skills and Learning Guide that maps out SQL Server skills that are key to master and which of our courses teaches each skill.

Read Our Microsoft SQL Server Skills and Learning Guide

What is the best way to learn SQL Server?

A: There is no one answer to this question as different people learn in different ways. However, some general tips on how to learn SQL Server effectively include:

  • Finding a comprehensive tutorial or course that covers all the basics and builds up gradually to more complex topics.
  • Doing plenty of practice exercises so that you can get used to using the various SQL commands.
  • Working with a real database from the start, rather than using dummy data. This will help you better understand how SQL works in a practical setting.
  • Seeking out experienced mentors or colleagues who can offer guidance and advice when needed.

With a bit of effort and dedication, anyone can learn SQL Server and become proficient in using it. The key is to find the learning method that works best for you and to stick with it. With some patience and practice, you'll be an expert in no time!

Certstaffix Training offers online and group onsite SQL Server training classes. Browse our offerings to find out more.

Is SQL Server difficult to learn?

A: SQL Server is a relational database management system (RDBMS) designed by Microsoft. It is a powerful tool used for managing and storing data, but it can be difficult to learn for some new users.

The good news is that there are plenty of resources available to help users learn SQL Server. There are online tutorials, books, and even SQL Server training courses offered by Certstaffix Training. With the right resources and guidance, anyone can learn how to use SQL Server effectively.

What are the top Microsoft SQL Server skills?

A: There are many skills that are important for Microsoft SQL Server professionals. However, some skills are more essential than others. Here are the top three Microsoft SQL Server skills that every professional should have:

Top Microsoft SQL Server Skills

1. T-SQL Querying: T-SQL is the language used to query Microsoft SQL Server databases. If you want to be a successful SQL Server professional, it is essential that you know how to write efficient T-SQL queries.

2. Database Administration: In order to ensure that your database runs smoothly and efficiently, it is important to have strong database administration skills. This includes tasks such as backup and recovery, performance tuning, and security management.

3. Data Modeling: Creating an effective data model is essential for any database-driven application. If you want to be a successful SQL Server professional, it is important that you have strong data modeling skills.

These are just a few of the top Microsoft SQL Server skills that every professional should have. In order to be truly successful in this field, it is important to continuously learn and keep up with the latest trends and technologies.

SQL Server Training and Certification

At Certstaffix Training, we offer three distinct approaches to elevate your Microsoft SQL Server skills, all tailored to prepare you for Microsoft SQL Server certifications. You can choose from 1) live MS SQL training classes online, 2) self-paced MSSQL certification eLearning, or 3) onsite SQL certification courses tailored for corporate groups. Each SQL certification course, whether live or self-paced, are designed to ensure you're ready for SQL Server certifications and solidify your credentials with a SQL online certification. Our SQL programming certification content is delivered by experts, designed to help you navigate the SQL Server certification Microsoft process effectively, and ensure the highest level of SQL training and certification.

Where Can I Learn More About Microsoft SQL Server?

Microsoft SQL Server Blogs

Microsoft SQL Server User Groups

Microsoft SQL Server Online Forums

Explore SQL Server Training Classes Near Me:

Certstaffix Training provides SQL Server classes near me or online, depending on the number of students involved. We offer online courses for individual learners, as well as in person classes at your office for corporate groups. Our trainers are highly experienced professionals with the expertise necessary to help you gain a thorough understanding of SQL Server concepts and tools. With our courses available online for individuals or in person for corporate groups, it's easy to develop your SQL Server skills. Start learning today and see how Certstaffix Training can help you reach your goals.


Have a Group?
Request Private Training

8/12/2024 10:00:00 AM
Online Class

Registration Deadline - 07/28/2024


11/4/2024 10:00:00 AM
Online Class

Registration Deadline - 10/20/2024

Start your training today!