SQL Database Administration

Technologies: SQL 2016, SQL Server 2014, SQL Server 2016
Extract, transform, load (ETL) is the main process in data warehousing solutions, facilitating the transfer of the data from a source to a destination. Examine the ETL process and how it can be implemented.
DQS can help identify duplicate or invalid data entries while automatically correcting them. MDS enables data validation and rules to ensure consistency in your data. Discover how to install and use both of these technologies.
SSIS in SQL Server 2014 allows for dynamic packages, enabling values within the package to be manually set, depending on your requirements at execution time. Discover what’s involved in designing SSIS packages.
Connection managers in a data warehouse are used to define the data source connections. Discover how to use connection managers to specify source and destinations, as well as how to create custom data sources.
Control flow consists of three elements: containers, tasks, and precedence constraints, which can be used to provide structure, functionality, and order of operations. Examine how to design control flow elements in SSIS.
When creating a data warehouse, data quality should be one of your top priorities. In order to trust the results of data analysis, high-quality data is essential. Explore various strategies for creating data quality projects.
SQL Server Integration Services (SSIS) provides the ability to create custom components if the integrated components do not suit your specific needs. Learn how to develop and install custom components and implement script tasks.
Microsoft SQL Server Integration Services (SSIS) is a platform that can be used to build packages for data warehousing. Discover how to create simple SSIS packages and use script tasks.
When working with data in a data warehouse, some level of transformation is usually required between the source and destination. Learn how to work with transformations and design data flow.
A data warehouse combines data from multiple sources that can be used to create analytical reports. Explore schemas and tables that are created within it, and how to create dimension and fact tables.
SQL Server Integration Services (SSIS) can be installed during the SQL Server installation or as a separate component. Examine the various components of SSIS, as well as installation considerations, such as upgrade options and architecture.
SSIS consists of several layers, including digital signatures and database roles, to provide a rich and flexible security environment. Examine techniques and recommendations on how to secure an SSIS deployment.
SSIS consists of several layers, including package properties and centralized management, to provide a rich and flexible environment. Explore SSIS catalog creation, package deployment, and package validation.
SQL Server Integration Services (SSIS) and SQL Server Data Tools (SSDT) both offer tools and techniques to monitor and optimize SSIS solutions. Discover how to implement auditing, logging, and event handling in SSIS.
SQL Server Integration Services (SSIS) is an extensible platform for creating data integration and workflow solutions. Explore various options available for loading data, including incremental, full, and bulk load.
SQL Server 2014 allows for the use of parameters, variables, and expressions to create dynamic packages. Examine SSIS variable concepts, such as system variables, scope, parameterization of properties, and data types.
SSIS includes tools and features that can be used to troubleshoot data integration. Learn how to debug and troubleshoot packages at the design and production level using techniques such as data viewers and break points.
By using the control flow data flow task, data flow can be implemented into the SSIS package specifying the data source, data transformation, and data destination. Discover how to implement control flow elements.
Each data warehouse consists of at least one fact table, which contains fundamental measurements of the business. Explore the implementation of fact tables using partitions, data compression, and columnstore indexes.
Data is an important enterprise asset and must be protected, retrievable, and highly available. Examine high availability and disaster recovery in SQL Server 2016, including log shipping, shared disks, and cluster shared volumes.
By carefully managing aspects of your database, you can better tune performance and identify potential issues. Explore index management tasks, such as identifying and repairing fragmentation, and identifying and creating missing indexes.
Managing database integrity by ensuring objects are structurally sound and properly allocated is an important aspect of database administration. Explore database consistency checks and how to identify and recover from database corruption.
There are various types of application patterns available when migrating SQL Server-based applications to an Azure environment. Examine some of these patterns, including 1-Tier, 3-Tier, Presentation Tier, and Business Tier.
Connection strings are used to allow client applications to interact with Azure SQL databases. Explore application connections strings and the Azure Traffic Manager, which is used to control the distribution of incoming user traffic.
Moving Microsoft SQL Server applications to Azure virtual machines can help optimize overall performance. Discover various methods used for performance tuning and configuring availability sets for redundancy.
Microsoft SQL Server applications can be moved to Azure virtual machines to help reduce costs and to optimize overall performance. Explore the various methods used to migrate applications as well as gathering benchmark data.
Migrating a database to Azure SQL Database primarily involves three steps: preparing, exporting, and importing. Explore the requirements and processes involved in migrating data to Azure SQL Databases.
There are many variables to consider when deciding on a single-tenant or multi-tenant design for Windows Azure. Examine the considerations for each design option.
Keeping a SQL Server environment running its best involves constant monitoring of activity. Discover how to configure operators and alerts in SQL Server 2016 to help you maintain peak performance.
SQL Server 2016 offers various tools to tune a SQL Server environment for peak performance. Discover how to maximize database performance as well as the maximum amount of server memory.
Besides Transparent Data Encryption (TDE), there are other methods available for Azure SQL database encryption. Examine alternate encryption solutions, including Always Encrypted and cell-level encryption.
Transparent Data Encryption (TDE) performs real-time I/O encryption of data and can be used to secure SQL servers and Azure SQL databases. Discover TDE commands, functions, and considerations.
Part of your deployment strategy should be to secure not only the databases but also the data itself. Examine dynamic data masking, a feature in SQL Server 2016 that can be used to limit exposure to sensitive data.
Determining how to secure databases in SQL Server 2016 is one of the most important parts of a deployment strategy. Discover how to implement firewalls, authentication, and auditing to ensure Azure SQL database security.
SQL Server uses instances to allow more than one copy of a SQL Server component to be installed on a single server. Explore SQL Server instances in IaaS and on-premises, and the various storage solutions in SQL Server 2016.
Azure virtual machines (VMs) provide a way to move on-premise SQL Server workloads and applications to the cloud. Explore service tiers, pools and SQL databases for Azure VMs.
Azure virtual machines provide a way to move on-premise SQL Server workloads and applications to the cloud. Discover the recommended usage scenarios for Azure VMs as well as how to provision, create, and connect to Azure SQL Databases.
When performing a SQL Server 2016 upgrade, it's important to understand numerous recommended pre- and post-installation best practices. Examine upgrade options, such as in-place and side-by-side upgrades.
It's important to understand the numerous recommended pre- and post-installation best practices set forth by Microsoft to ensure a successful deployment. Explore deployment options, including post-installation best practices.
Understanding the architecture of SQL Server 2016 is important to being able to effectively work with it. Explore the SQL Server architecture, including parallelism, I/O operations, account types, and network protocols.
It's important to understand the various components, editions, and versions that exist when discussing SQL Server 2016. Explore the SQL Server platform and best practices and guidelines to follow when preparing for an installation.
Regularly monitoring SQL Server instances helps identify and correct issues before they cause damage and can aid in performance tuning. Explore Policy-Based Management and performance monitoring of SQL Server 2016 instances.
SQL Server 2016 offers various database programmable objects, such as stored procedures and functions. Discover how to implement database programmability objects and how to work with temporal and non-relational data in SQL Server 2016.
Subqueries in SQL Server 2016 are nested SELECT statements used to create more effective queries in T-SQL. Explore subqueries and correlated subqueries and the EXISTS predicate.
One method for maintaining data integrity and minimizing potential disaster is replication. Discover how to configure, implement, and manage SQL Server replication as part of your backup and recovery plan.
SQL Server AlwaysOn Availability Groups are built on WSFC clusters and AlwaysOn Failover Cluster Instances (FCI). Discover how to create, configure, and manage SQL Server AlwaysOn FCIs and AlwaysOn Availability Groups.
Windows Server Failover Clustering (WSFC) helps increase the availability of applications and services. Explore WSFC, including processes and cluster storage, and how to create a failover cluster.
Windows Azure lets you configure and manage a dedicated VM to run an instance of SQL Server. Discover VMs on Windows Azure; how to install, configure and manage SQL Sever on a VM; and high availability options in SQL Server 2014.
Meet Microsoft Windows Azure and Windows Azure SQL Database. Explore Windows Azure architecture, services, and storage, and examine Windows Azure SQL Database, including security and migration.
SQL Server 2014 includes features for private cloud deployment and management of SQL databases. Explore cloud computing, configuring SQL in a private cloud configuration, and working with prepared instances of SQL Server.
SQL Server 2014 has a number of enhancements and features aimed at private cloud deployment and management of SQL databases. Explore SQL Server consolidation and management of consolidated resources.
When planning a SQL Server infrastructure, you must include the management of all instances of SQL Server. Discover how to implement policy-based management and how to manage SQL Server using System Center.
A SQL Server infrastructure must be planned from the hardware up. Discover how to plan both the physical and logical database structures using the Microsoft Planning and Assessment Toolkit.
Regularly monitoring SQL Server instances helps identify and correct issues before they cause damage and can aid in performance tuning. Explore the creation and management of operators and SQL Server Agent alerts.
An enterprise database infrastructure has a lot of moving pieces, and it's crucial that they be monitored. Explore SQL Server database queries, including the Query Store, Extended Events, SQL Trace, and troubleshooting server health.
An enterprise database infrastructure has a lot of moving pieces, and it's crucial that they be monitored. Explore managing and monitoring SQL Server database activity, including data collector and creating a utility control point.
In the event of an unforeseen disaster, critical data should be protected and easily recoverable. Discover how to perform piecemeal and page restores, and restoring file groups.
An effective data protection strategy requires the ability to restore data from a backup in case of disaster, failure, or errors. Explore restoring SQL Server 2016 databases, including point-in-time recovery and automation for testing.
A tested backup strategy is the most effective means of protecting your data from potential disaster. Explore SQL Server 2016 backup management practices, including backup strategies, media options, and retention and recovery models.
Should something go wrong, you can always use your SQL Server backup to recover your data. Explore the SQL Server 2014 recovery process, including restoring from Azure and Azure SQL Database.
From an individual to the largest organization, it is crucial that you always back up your data. Discover SQL Server 2014 backup strategies, how to perform a backup, and how to create and maintain a disaster recovery plan.
SQL Server 2016 offers built-in functions that provide data type conversion, comparison, and NULL handling. Explore the various types of built-in functions and how to write queries using built-in functions.
SELECT statements in SQL Server 2016 are used to query tables and views and to manipulate data for customized results. Explore the fundamentals of using SELECT statements, including how to use aliases and case expressions.
Aggregate functions in SQL Server 2016 are used to summarize data in multiple rows. Explore the GROUP BY clause and how to change a table-valued expression into another table using the PIVOT and UNPIVOT operators.
Set operators in SQL Server 2016 are used to compare rows between input sets. Explore the UNION, INTERSECT, and EXCEPT set operators, the APPLY operator, and the basic components of common table expressions.
Aggregate functions in SQL Server 2016 are used to summarize data in multiple rows. Explore various types of aggregate functions such as SUM, MIN, MAX, AVG, and COUNT.
SQL Server 2016 delivers many new features and enhancements. Explore an overview of the SQL Server 2016 architecture through the fundamentals of T-SQL querying.
SQL Server 2016 offers various clauses that work in conjunction with SELECT statements. Discover how to sort and filter data, use predicates and operators, and use NULL in queries.
SQL Server 2016 delivers many new features and enhancements. Explore an overview of the SQL Server 2016 architecture, from topics such as sets and predicate logic.
SELECT statements in SQL Server 2016 are used to query tables and views and to manipulate data for customized results. Explore the fundamentals of querying multiple tables using joins.
SQL Server 2016 offers various clauses determine how the results of a particular query are presented. Explore data modification using conversions, character data, SELECT INTO, INSERT, and more.
Ensuring data security is an important part of administering any database infrastructure, and encryption is one method to help you protect sensitive data. Explore encryption-related tools and processes used in SQL Server 2016.
To protect data in a SQL Server Database environment, you need to ensure that only trusted users and applications have access to it. Explore user options for Azure SQL Database, row-level security, and dynamic data masking.
To protect data in a SQL Server Database environment, you need to ensure that only trusted users and applications have access to it. Explore data access and permissions, including database object permissions, users, and custom roles.
Auditing is an essential administration task used to track and log database events to ensure the database is performing properly. Explore auditing, Azure SQL Database audit, and analyzing audit logs and reports from Azure SQL Database.
Mastering security is crucial. Explore one of the most important aspects of SQL security. Discover the lock system, levels of security access and management, privileges and rights, and the setup and installation of security architecture.
Master SQL transactions. Explore transactions and how to group statements, Transact-SQL, and transaction logs.
Master advanced queries. Queries in SQL allow you to manipulate databases like a pro. Discover advanced techniques including subqueries, correlated queries, difficult queries, and learn query tips for better efficiency.
Master SQL transaction locks. Explore transaction locks and explore the different levels of locking.
Discover SQL views. Explore SQL views in detail, defining various views and their management, from creating views to view types, updating, and dropping views.
Work with multiple tables. Mastering the fundamentals of SQL creates a foundation. Explore multi-table database architecture and design, connecting several tables, using inner/outer joins, and equijoins and non-equijoins.
Master the core functions of SQL strings. Strings are a key component to any database, and you can work magic using fundamental SQL commands. Explore string functions like LENGTH, REVERSE, and TRIM, as well as how to put them together.
Manipulating databases is a necessary skill. Explore Structured Query Language (SQL) and dive into the architecture. Discover efficient and easily manageable databases using features like SELECT, data types, UPDATE, and ORDER BY.
Fundamentals create foundation. Get a clear picture of what SQL is all about. Discover table functions including ALTER and CREATE, as well as column functions and math operators.
Master SQL concepts. Learning the core fundamentals creates a foundation. Discover relational databases and Structured Query Language (SQL) database concepts.
Master SQL tables. Take your first steps in learning about relational databases and Structured Query Language (SQL) tables. Discover how to manage tables, and queries, including complex tables, changing tables, and deleting tables.