If you are a SQL DBA and you want to learn more about SQL Server administration, then this eBook is for you. The eBook suggests several SQL Server administration best practices, that can be used for ensuring healthy SQL Server instances. The eBook assumes at least intermediate-level experience with SQL Server administration. Moreover, it contains many simple examples on how you can easily perform useful administration tasks in SQL Server.
eBook Format: PDF
Author: Artemakis Artemiou, B.Sc., M.Sc., MCP, MCTS, MCITP, Former Microsoft Data Platform MVP (2009-2018)
Rate this eBook:
About the Author
Artemakis Artemiou is a Senior SQL Server and Software Architect and Author. Artemakis received the prestigious Microsoft Data Platform MVP award for 9 consecutive years (2009-2017). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator, DBA Security Advisor and In-Memory OLTP Simulator. Moreover, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). You can follow Artemakis on Twitter.
Table of Contents
Chapter 1: Maintenance
– Cleaning up Backup and Restore History Logs in MSDB
– Explicitly Control SQL Server Connections
– Backing up a Database in a Network Folder
– Backup Compression in SQL Server 2008 and Later
– Renaming Windows Logins in SQL Server
– The SQL Server Copy Database Wizard
– Getting the File Locations for all DBs in a SQL Server Instance
– Retrieving Database File Sizes
– Retrieving Size Information for all Tables in a SQL Server Database
– Undocumented Stored Procedure sp_MSforeachdb
– Control Database Creation with the Model System Database in SQL Server
– Transferring Ownership of All Database Objects Back to DBO
– Patching a SQL Server Failover Cluster
– Executing T-SQL Statements Against All Databases
Chapter 2: Security
– Policy-Based Management in SQL Server
– Migrating to a Contained Database in SQL Server
– Changing the Database Owner in a SQL Server Database
– Creating Logins for Orphaned SQL Server Users
– Transparent Data Encryption in SQL Server
– Retrieving Security-Related Info for SQL Server Logins
– Security Changes in SQL Server 2008
– The SELECT ALL USER SECURABLES Permission in SQL Server
– The “Public” Database Role in SQL Server
– What are Exactly Orphaned Users in SQL Server
– Frequent Password Expiration: Time to Revise it?
– Row Level Security in SQL Server 2016
– Dynamic Data Masking in SQL Server 2016
Chapter 3: Integration
– How to Create a Simple Linked Server Between SQL Server Instances
– Using Proxies in SQL Server Agent Jobs
– Using Unicode in SQL Server
Chapter 4: Special Topics
– Windows Internal Database (SSEE)
– Dynamically Generating T-SQL Statements
– Massively Detaching and Re-attaching Databases in SQL Server
– Installing 32-bit SQL Server 2005 Reporting Services on a 64-bit machine/Windows OS
– Compatibility Levels Supported by Different SQL Server Versions
– Searching for Keywords in SQL Server Jobs
– Accessing Reporting Services Using a Fully Qualified Domain Name
– Useful SQL Server Knowledge
Chapter 5: Error Handling
– Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
– Creating an instance of the COM component with CLSID…
– No global profile is configured. Specify a profile name in the @profile_name parameter
– Database [Database_Name] cannot be upgraded because it is read-only or has readonly files
– Saving maintenance plan failed
– A transport-level error has occurred when sending the request to the server
– There is insufficient system memory in resource pool ‘internal’ to run this query.
– The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystemresource’, schema ‘sys’.
– SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
– The timeout period elapsed prior to obtaining a connection from the pool
– A connection was successfully established with the server, but then an error occurred during the login process.
– Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
– The transaction log for database ‘dbname’ is full due to ‘XTP_CHECKPOINT’?
List of Listings
List of Figures
Who Should Read This Book
This book is for database administrators and architects who monitor and tune SQL Server instances in order to keep them operating to the maximum possible performance and stability. The book suggests several techniques that can be used for ensuring a performant SQL Server instance. However, the book is not intended to be a step-by-step comprehensive guide. Additionally, it assumes at least intermediate-level experience with SQL Server administration and knowledge of basic database principles (i.e. indexing, locking, etc.).
How This Book Is Organized
The ebook is organized in 5 chapters. Chapter 1 discusses basic SQL Server maintenance tasks such as disk usage monitoring, history cleanup, backup-related topics, and more. Chapter 2 discusses security and compliance topics such as Policy-Based Management and encryption. Chapter 3 talks about different integration topics such as linked servers, use of proxies on SQL Server Agent job steps and Unicode support. Chapter 4 discusses special SQL Server topics like the Windows Internal Database (SSEE) and dynamic T-SQL generation. Chapter 5 discusses error handling and ways to overcome errors you might encounter due to external factors like permission issues, invalid user input, etc.