Robust Transact-SQL coding practices dictate proactive planning for anticipated and unanticipated errors that might occur during code module execution. SQL Server’s flavor of Transact-SQL provides several commands that can be used to properly handle errors in your code and this course will cover the fundamental commands you’ll need to know, as well as effective error handling techniques that will help you properly handle issues for high throughput, mission-critical data tiers. This course is perfect for developers, DBAs, and anyone responsible for writing Transact-SQL code, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onward, with some emphasis on new SQL Server 2012 commands.
SQL Server replication remains a viable data distribution and availability feature that has inherent advantages that are not entirely covered by other out-of-the-box SQL Server features. This course will briefly examine the various replication types before delving more deeply into transactional replication. In addition to the fundamentals, this course will also review the key performance considerations, monitoring techniques and troubleshooting scenarios. After viewing this course you should be ready to use transactional replication in the appropriate contexts, as well as deploy, support and troubleshoot issues accordingly. This course is perfect for developers, DBAs, and anyone responsible for SQL Server replication, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onwards.
Virtualization of SQL Server instances have become very popular in IT infrastructures, but it's very common to see misconfigured virtual machines resulting in severe performance problems of the SQL Server workloads. This course will show you how to properly configure, control, and monitor VMware and Hyper-V virtual machines being used for SQL Server instances, and how to troubleshoot and fix common performance problems caused by virtualization issues. The course begins by explaining how virtualization works under the covers and why virtualization has become so popular. It then discusses the various memory, CPU, and storage considerations that must be taken into account when virtualizing SQL Server, and demonstrates how to properly configure virtual machines for use with SQL Server. Next the course discusses the high availability and disaster recovery options that VMware and Hyper-V provide, plus how to back up virtual machines. Finally the course shows how to monitor and improve virtual machine guest and host performance in VMware and Hyper-V, and discusses some of the limitations that could prevent a SQL Server workload from being successfully virtualized. This course is perfect for all IT admins and DBAs who are responsible for any aspect of a virtualized SQL Server environment, from SQL Server 2005 onward.
There are many important considerations when creating and deploying an application that uses SQL Server for data storage, whether you work for a large or small ISV, and are creating a complex or simple application. It's critical that your application performs well under load, keeps the user data secure, and is able to be installed and upgraded easily. This course explains all these considerations using a layered approach that ties into stages in the development lifecycle. The course begins by examining database design and schema considerations, plus general best practices around security using roles, logins, and users. It then explains and demonstrates numerous aspects of T-SQL coding where choices can be made to make the code and application more efficient, and avoid common performance pitfalls. Application and code testing is then addressed, along with figuring out the hardware, software, and SQL Server requirements that must be communicated to the users. The course ends by walking through the deployment phase, including common SQL Server and database configuration options that must be changed or left as the default value, and explaining how to ensure that application and database upgrades run smoothly. This course is perfect for developers who are involved in the development lifecycle of applications that use SQL Server for data storage. The information in the course applies to all versions from SQL Server 2005 onward.
If you need to develop a solution to meet your business intelligence needs but don't have the in-house SQL Server Analysis Services skills to make it happen, don't rule out a SQL Server database engine solution just yet. SQL Server 2012 nonclustered columnstore indexes provide a compelling new option for extremely fast querying of very large data sets found in relational data warehouses. This course will cover the benefits of this new functionality and will also clearly articulate the current limitations and planning considerations along the way. By the end of this course, you'll be equipped with enough information to help decide if nonclustered columnstore indexes will be a viable choice for your application data tier. This course is perfect for developers, DBAs, and anyone responsible for SQL Server performance. The information in the course applies to SQL Server 2012 onwards.
How do you choose a processor? Which Edition of SQL Server 2012 should you select? How do you choose a RAID level for the SQL Server storage? How do you compare processor performance using benchmarks? This course answers all these questions and more. The course begins by explaining why it is so important to choose server hardware for SQL Server 2012 correctly, and then explains how to categorize your workload and the hardware requirements of the common SQL Server workloads. It then explains how to choose a processor based on your server configuration, as well as how to compare the performance of processors, as well as the various SQL Server 2012 Editions and their license limits. Next it explains the various storage types, RAID levels, and how to test the performance of your storage, along with comparing overall server performance and considerations around the amount of memory to use. Finally it discusses the effects on hardware choice of high-availability and disaster-recovery strategies, along with hot-swappable components to increase uptime. This course is perfect for anyone who is purchasing hardware for SQL Server for the first time and needs guidance on how to properly make decisions to get the return on investment from SQL Server 2012 license costs and server hardware and storage costs.
This course builds on the SQL Server: Introduction to Extended Events course and delves deeper into the Extended Events system to show you more advanced features, options, targets, and scenarios. The course starts by showing how to develop more powerful event sessions and also set the various event session options to control memory management, event dispatching and retention, and how to track linked events. It then presents the advanced event targets, along with the potential problems you may encounter when using them, and the advanced features of the SQL Server 2012 Extended Events UI in Management Studio, and how to leverage Extended Events using both the .NET API and PowerShell. The course ends by presenting some complex troubleshooting scenarios and shows how they can be investigated using Extended Events.This course is perfect for those with some experience of Extended Events and troubleshooting, as all the knowledge in the SQL Server: Introduction to Extended Events course is assumed. The information in the course applies to all versions from SQL Server 2008 onwards.
SQL Server query plans contain a wealth of information that can be used to diagnose performance problems, but at first glance they appear complicated and difficult to understand. With more than 30 detailed demos, this course shows you how to easily understand and interpret query plans so you can pinpoint performance problems. The course starts by explaining the various methods of capturing query plans for later analysis, and how to recognize the basic elements of query plans along with their CPU, I/O, and memory costs and requirements. It then moves on to explaining all the common query plan operators and when they will be present in a query plan, along with notes on potential problems to watch out for. The final module explains common patterns to look for in query plans that indicate a performance problem that should be investigated and fixed. This course is perfect for developers, DBAs, and anyone responsible for SQL Server performance, from complete beginners through to those with more experience who want a definitive guide to SQL Server query plans. The information in the course applies to all versions from SQL Server 2005 onwards.
There are many problems that can lower the performance of your workload and one of the most common is an incorrect query plan. Often the poor query plan is chosen because the cardinality estimate is wrong - the estimate by the query processor of how many table rows will be involved in the query. This course shows you how to recognize when the query processor has an incorrect estimate, along with explaining and showing a multitude of possible causes, plus how to fix them. The course starts with explaining why query plan quality is important, and then shows how to easily spot cardinality estimate issues from examining query plans. The majority of the course shows all the possible causes of cardinality estimates being incorrect along with how to fix them, and more than 25 demos to walk you through practical examples of the concepts and problems in the lectures. This course is perfect for developers, DBAs, and anyone responsible for performance tuning on SQL Server. The information in the course applies to all version from SQL Server 2005 onwards.
How do you configure Windows correctly for running SQL Server 2012? Which Edition of SQL Server 2012 should you select? How do you choose a RAID level for the SQL Server storage? Which installation options should you choose for SQL Server, and how do you keep SQL Server up-to-date after installing it? This course answers all these questions and more. The course begins by explaining why it is so important to install SQL Server 2012 correctly, and then explains how to configure Windows for stability and performance, choose a storage RAID level that takes the anticipated SQL Server workload into account, and test the storage to ensure it has the performance required. It then describes and shows the SQL Server 2012 installation process, including which options to choose or not, along with common configuration mistakes to avoid. After that it explains how to keep your SQL Server 2012 instance up-to-date with Service Packs and Cumulative Updates, as well as post-installation configuration tasks such as provisioning tempdb. Finally it explains how to configure Database Mail and SQL Server Agent Alerts so that you are notified of critical errors when they occur, plus installing and configuring a simple solution for regular database maintenance, such as backups and index rebuilds. This course is perfect for anyone who is installing SQL Server for the first time and needs guidance on how to properly configure Windows, SQL Server, storage, and regular maintenance so that the SQL Server installation is stable, performs well, and adheres to configuration best practices.
This course explores the new developer-centric SQL Server Data Tools introduced with SQL Server 2012, including advanced SSDT topics such as snapshots, schema compares, and database deployment to both on-premise and cloud-based servers. In addition to a deep exploration of SSDT, several enhancements to the T-SQL language in SQL Server 2008 and SQL Server 2012 are explored, including table-valued parameters (TVPs), MERGE, windowing, sequence objects, error handling and host of new built-in functions.
There are a wealth of problems that can affect the performance of SQL Server workloads, and this course shows you more than 35 common performance issue patterns. With 17 detailed demos, you're shown how to recognize each pattern along with practical troubleshooting guidance that you can use. The course starts with high-level issues around people and practices, before moving to technical areas like I/O, concurrency, memory, and CPU. The course also has modules on specific areas like tempdb, application design, and factors that can affect query plan quality. This course is perfect for developers, DBAs, and anyone responsible for SQL Server, from complete beginners through to those with more experience who want a fresh way to troubleshoot SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.
The logging and recovery mechanism is one of the most misunderstood parts of SQL Server, but it's one of the most critical for you to understand. There are a lot of log management problems you can run into that can cause workload performance problems and even application outages. This comprehensive course, written by someone who has actually worked on the SQL Server logging and recovery code itself, explains everything practical there is to know about how logging and recovery work, and how to avoid and recover from problems with the transaction log. The course follows a building-blocks approach, starting with a simple definition and example of how logging is used, and then covering transaction log architecture and log recorss themselves. Using this knowledge, the course then moves into details of checkpoint operations, general transaction log operations including log clearing and VLF management, and how recovery and crash recovery work. The three recovery models are described in detail, as well as how to create and configure transaction logs for optimal performance, including monitoring transaction log performance. The course ends with a detailed module on transaction log backups, tail-of-the-log-backups, and examining transaction log contents, and then a module on dealing with transaction log corruption and how logging and recovery are used in high-availability technologies. The course is perfect for anyone who has to manage SQL Server and wants to avoid common transaction log problems, as well as those looking for in-depth coverage of everything to do with the transaction log. The information in the course applies to all versions from SQL Server 2005 onwards.
When you experience a performance problem, how can you easily tell where the problem is without being able to compare against a known-good set of measurements? This course explains how to create a performance baseline for a SQL Server instance using benchmarking techniques. The course begins by exploring the reasons for benchmarking and baselining, along with how to decide what to capture, when and how often to capture it, and where to store it. It then explains how to use the Performance Monitor tool in detail, along with how to capture query information using SQL Trace and Extended Events, and then analyze that information using the free Clear Trace and ReadTrace tools. After that it describes and demonstrates the multitude of Dynamic Management Views that can be used to gather information about SQL Server, before ending with a summary that pulls all the modules together. This course is perfect for those with no experience of benchmarking and baselining, and those with some experience but who want to solidify their understanding of creating and using benchmarks and baselines. The information in the course applies to all versions from SQL Server 2005 onwards.
Deadlocks can be very problematic when they occur, especially if you do not know how to figure out what's making the deadlocks occur so that you can take steps to prevent them. This course explains how the locking mechanism in SQL Server works and the circumstances that can lead to deadlocks occurring. The course then describes and demonstrates the various methods for detecting deadlocks in SQL Server and collecting information about the deadlocks so they can be analyzed, plus how to perform deadlock graph analysis using XML, Profiler, Management Studio and third-party tools. Common deadlock scenarios are explained and demonstrated, including lock escalation deadlocks, multi-victim deadlocks, reverse object order deadlocks, and more. The course concludes with how to handle deadlocks in Transact-SQL and ADO.NET code, including the implementation of custom retry logic. This course is perfect for those with no experience of deadlock analysis and those with some experience but who want to solidify their understanding of deadlocks and how to analyze and prevent them. The information in the course applies to all versions from SQL Server 2005 onwards.
Extended Events were added in SQL Server 2008 as a method of performing analysis and troubleshooting of SQL Server, with the ability to troubleshoot scenarios that have never been possible before. SQL Trace has finally been deprecated in SQL Server 2012 and so it is now imperative that developers and DBAs learn how to use Extended Events. This course explains the architecture of Extended Events, and how to create and manage event sessions, using events, actions, predicates, and basic targets. It then gives comprehensive coverage of the Management Studio UI for Extended Events that was added in SQL Server 2012, as well as the freely-available SSMS add-in that Jonathan wrote for SQL Server 2008 and 2008R2. The course ends by presenting some common troubleshooting scenarios that can be easily accomplished using Extended Events. It is perfect for those with no experience of Extended Events, those who are new to the Extended Events UIs and those with some experience but who want to solidify their understanding of Extended Events and how to use them. The information in the course applies to all versions from SQL Server 2008 onwards.
If you need to modify data in a SQL Server database, then you need to know how to use the INSERT, UPDATE and DELETE statements. This course starts by explaining how to find information about the tables and columns you want to modify. It then explains the INSERT, UPDATE, and DELETE statements in detail along with various methods for limiting the data being modified. Finally it moves beyond the basic modification statements to more advanced topics like the MERGE statement, error handling and more. More then thirty five demos help to give you a thorough understanding of how to perform these essential operations, all using a freely-available demo environment that you're shown how to set up and configure. This course is perfect for developers who need to modify data in SQL Server databases, from complete beginners through to more experienced developers who can use some of the modules as reference material. The information in the course applies to all versions from SQL Server 2005 onwards.
If you need to retrieve data from a SQL Server database then you need to know how to use the SELECT statement. This course starts with the basics of a SELECT statement and its various sub-clauses, and progresses to how to select from multiple data sources in the same statement and a comprehensive section on the functions available to manipulate, aggregate, and convert data during the select operation. More then fifty demos help to give you a thorough understanding of how to perform these essential operations, all using a freely-available demo environment that you're shown how to set up and configure. This course is perfect for developers who need to query SQL Server databases to retrieve data, from complete beginners through to more experienced developers who can use some of the modules as reference material. The information in the course applies to all versions from SQL Server 2005 onwards.
One of the easiest ways to collect information about SQL Server workloads is to capture trace information. This course will introduce you to using SQL Trace and SQL Profiler for tracing SQL Server activity, including common pitfalls and problems to avoid. You'll learn how to analyze the collected information using a variety of methods that will help with performance troubleshooting, workload benchmarking, activity auditing, and much more. The course is perfect for developers and DBAs who need to collect information about workloads running on SQL Server for analytic purposes. The information in the course applies to all versions from SQL Server 2005 onwards.
Where do you start looking when trying to diagnose a performance problem with SQL Server? The answer is to use wait statistics! This course will introduce you to the powerful 'waits and queues' performance tuning methodology. You'll learn how SQL Server's thread scheduling system works, what wait statistics are and how to use them, what more advanced synchronization mechanisms like latches and spinlocks are, and a wealth of detail about common performance issues and how to diagnose and solve them. The course is perfect for developers and DBAs who have been struggling to figure out how to start troubleshooting performance problems with SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.
This course is designed to teach you the fundamentals of using PowerShell to manage your SQL Servers, as well as develop on them. In Module 1, we cover basic DBA tasks using just PowerShell. With Module 2, we provide an introduction to the two main tools for working with SQL Server, the SMO, or SQL Management Objects, and the SQL Server Provider. Module 3 will cover basic DBA tasks using both SMO and the SQL Provider. With Module 4 we switch to Developer mode and cover development using the SQL Provider. Module 5 continues the developer theme, this time using the SQL Management Objects. We finally wrap things up with Module 6, in which we go over a real world example of using PowerShell and SQL Server. We also cover the SQL PS mode of PowerShell, as well as how to make effective use of PowerShell from a SQL Server job.
There is so much information online about SQL Server, however much of it is inaccurate to some degree. In this course, you will learn the truth behind more than 80 myths and misconceptions about SQL Server that can cause you performance and availability problems - from someone who wrote the SQL Server code. This course is perfect for developers and DBAs who have been struggling to make sense of conflicting information about SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.
SQL Server performance tuning is an in-depth topic, and an art to master. A key component of overall application performance tuning is query tuning. Writing queries in an efficient manner, and making sure they execute in the most optimal way possible, is always a challenge. The basics revolve around the details of how SQL Server carries out query execution, so the optimizations explored in this course follow along the same lines.
Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is good side as well evil side of the indexes. To master the art of performance tuning one has to understand the fundamentals of the indexes and the best practices associated with the same. This course is for every DBA and Developer who deals with performance tuning and wants to use indexes to improve the performance of server.
This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. This course is for anyone working with SQL Server databases who wants to improve her knowledge and understanding of this complex platform.
This course covers SQL Server's integration with XML technologies including the XML data type, XQuery implementation, XML Schema support, and XML index performance considerations. It also covers different ways of integrating XML with related SQL Server features.
This course is primarily for C# developers who use SQL Server in their applications. T-SQL developers and Database Administrators will also find it useful. It covers the fundamentals of using SQL Server from T-SQL to the CLR, to automating processing with SQL Server Integration services.