| Professional Microsoft SQL Server 2014 Administration |
Page 1 of 3 Author: Adam Jorgensen et al This book aims to give a professional understanding of a very broad range of administration topics, how does it fare? This is a big book, with a very broad coverage of topics discussed over its twenty-five chapters. This fact itself tells you there is much more to SQL Server 2014 than simply T-SQL. The ‘professional’ moniker of the title purports this is not a book for beginners, instead it is aimed at experienced DBAs and SQL developers. Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 SQL Server 2014 Architecture The chapter begins with an overview of the different types of admin users (production, development, business intelligent DBAs, and SQL developers) and the new and enhanced features each may want to investigate. The underlying physical architecture is described in terms of database files, logs, filegroups and pages. The various system databases are briefly described:
A miscellany of topics follows. The concept of schemas is discussed, as is synonyms. A long list of the various groups of Dynamic Management Views (DMVs) is given and briefly discussed. Various data types and their ranges/values are detailed. Creating your own data types and routines with the Common Language Runtime (CLR) is outlined. Feature lists for the various editions of SQL Server are given for comparison purposes. Lastly, licensing is featured, the big difference being previously it was socket-based but is now core-based, so potentially more expensive. This chapter feels awkward, since it contains a wide range of topics, with sufficient depth for an introduction, but they are only loosely tied together. There are some good lists that could be useful for future reference. I was a little surprised the fashionable DevOps role wasn’t mentioned. Chapter 2 Installing SQL Server 2014 Best Practices Installation typically involves a large amount of preparation, the time spent planning often pays for itself with a corresponding reduction in problems. The chapter opens with a discussion of various hardware considerations, including processors (type, speed, multi-core etc), memory (cache, RAM, disks etc), and storage (SAN, DAS, RAID). This is followed by software considerations, including collation and service account usage. The various methods of installing SQL Server, namely new, side-by-side, and upgrade are outlined. Attention is given to both unattended (via a supplied PowerShell script) and attended installations. Some tools for testing/stressing the environment are discussed. Options for configuring SQL Server for security are detailed. Troubleshooting a failed install is discussed. Overall this is a useful chapter, easy to understand, and with links for further, more detailed, information. There’s a nice list of post-install options to check, which can be easy to overlook. Chapter 3 Upgrading SQL Server 2014 Best Practices The previous chapter focused on new installs, this chapter relates to upgrading SQL Server. Reasons for upgrading are discussed, these include: in-memory OLTP performance improvements, updatable columnstore indexes, extending the buffer pool using Solid State Disks (SSDs), delayed durability, improved cardinality estimator, backup encryption, new security roles, automatic and direct backups to Windows Azure, and enhanced AlwaysOn functionality. There is an emphasis on the amount of testing the product has received via Microsoft’s daily builds, CTPs, RCs and case studies. Again preparation is the key to a smooth upgrade. The different types of upgrade (in-place, and side-by-side), are detailed and compared. For each, checklists are provided for pre-upgrade, execution, and post-upgrade steps. To help with upgrading, 2 pre-upgrade tools are discussed, the Upgrade Advisor and the Upgrade Assistant, both should increase your confidence in a problem-free upgrade. The Distributed Replay Utility (DRU) looks useful in helping evaluate the migration from SQL Server 2012 to 2014. This is a very sensible and easy to follow chapter, containing many pointers of what to look for during upgrading, pre-empting potential problems, with some very good checklists.
Chapter 4 Managing and Troubleshooting the Database Engine SQL Server comes with a good set of default configuration options (e.g. CLR is disabled), meaning it can typically be run without further changes. However, there are times when configuration changes need to be made. SQL Server configuration manager is discussed for configuring services, ports, and protocols. The use of various startup parameters, trace flags, and startup routines are discussed in the context of configuration. In terms of troubleshooting, the use of the Dedicated Administrator Connection (DAC) is shown in a step-by-step walkthrough. Further troubleshooting is given via SQL Server Management Studio (SSMS) including: reports (CPU, IO, log space etc), use of sp_configure, error logs, and activity monitor. Monitoring using T-SQL is shown via: sp_who2, sp_WhoIsActive, sys.dm_exec_connections, and sys.dm_exec_sql_text ().The use of global and local trace flags is outlined. With larger systems, multi-server management becomes increasingly important, this is discussed in the context of using the Central Management Server (CMS). The use of advanced tools including SQLDumper.exe and SQLDiag.exe is outlined. This chapter is by necessity loosely tied together, this is to be expected since the components of monitoring and troubleshooting are wide ranging. The use of the DAC is well worth reading about – one day it may save your career! There is a note concerning rebuilding system databases, however this is placed incorrectly in the section concerning SQL Server Management Studio. There is also a warning about not using NOLOCK in production systems, however, the accompanying updated 2014 code given no longer uses NOLOCK.
Chapter 5 Automating SQL Server A lot of SQL Server administration work is repetitive, and as such is ripe for automation. The chapter starts with a look at maintenance plans, which is essentially a GUI on top of a SQL Agent job. Plan wizards exist for DBCC, shrink, index reorgs/rebuilds, update statistics, backup etc. Additionally, for greater control, the maintenance plan designer is outlined. Automation via the SQL Server Agent is described in depth, including jobs, schedules, operators, and alerts. A useful example of getting notified when the transaction log passes a threshold is given. The configuration of SQL Server agent jobs is given in detail. Extending automation across multiple servers is illustrated with forwarding events to a centralised server, and creating jobs centrally and distributing them. This is another easy to follow and practical chapter, with many step-by-step examples that will help improve many of your day to day admin tasks.
Chapter 6 Service Broker in SQL Server 2014 This chapter provides an overview of the use of Service Broker (SB), what it is and why you would want to use it. In essence SB provides asynchronous processing, using queues. In many ways it supersedes MSMQ technology. The chapter details the major components of SB: message types, contracts, queues, and activation stored procedures. Perhaps the authors read my review of the 2012 edition of this book, where I lamented the lack of a simple end-to-end working example of Service Broker in action – this has now been added.
Chapter 7 SQL Server CLR Integration The CLR allows you to write .NET code (e.g. C# or VB.net) and use it within a SQL Server environment. Some tasks, e.g. complex calculation, give better performance when written in .NET. Additionally, .NET provides a rich programming model and development environment. The chapter shows how to create, deploy, and register.NET code (called assemblies), for use within SQL Server. Examples are provided to do this via the command line and from within Visual Studio. Security is discussed, as is performance monitoring via PerfMon, Extended Events and DMVs. Again, it would have been better to have produced a useful CLR utility, e.g. using .NET Regular Expression libraries to validate email addresses etc. The example given in the chapter just prints the message “My Second SQLCLR Assembly”. I feel this was a missed opportunity.
Chapter 8 Securing the Database Instance Security is an increasingly noticeable topic. This chapter opens with a discussion of SQL versus Windows authentication types. It then proceeds to cover security at the server, database, object levels, and the use of GRANT, DENY, and REVOKE T-SQL commands. Permission chains and cross database permissions are briefly discussed. I found this chapter too light. Additionally, I wanted a worked example, from start to end, of creating a new user, a new role, assign the user to the role, and assign object permissions to the role.
|
||||
| Last Updated ( Sunday, 19 October 2014 ) |
