| Getting Started with SQL Server 2014 Administration |
Page 2 of 2
Author: Gethyn Ellis
Chapter 4 Delayed Durability This chapter flows naturally on from the in-memory optimized tables chapter. The premise being that you can improve performance by delaying writing transactions logs from memory to disk. In some systems the bottleneck is in writing log records, leading to a general degradation in performance. With delayed durability, from a coding perspective, the data is ‘committed’ before being written to disk, thus improving performance. The downside is that this uncommitted data can be lost if a failure or crash occurs. Full transaction durability (the default) is explained in terms of SQL Server waiting for a transaction to be committed to the transaction log disk before returning to the client. This is the traditional way SQL Server works, and should be kept if you can’t afford any data loss and performance due to log writes isn’t an issue. Following on from this, delayed durability is discussed, basically the transaction details are stored in memory and written to disk in batches or when an sp_flush_log command is issued. In essence, delayed durability is an asynchronous write to disk. Delayed durability reduces contention/blocking together with log I/O wait times, thus improving throughput and concurrency. Full and delayed durability are then compared, with reference to data loss and performance. Wait stats are typically used to determine if you have a bottleneck on writing to the transaction log. Example code is provided to Implement delayed duration transactions. One interesting point is that if a full duration (i.e. normal) transaction runs, it will cause the log to flush and all the delayed duration transactions will be committed, since it issues an sp_flush_log command. This is interesting because, if you need to bring down your system for maintenance, you can use a normal transaction to commit any outstanding delayed duration transactions. Log flushes can be monitored using Performance Monitor (PerfMon), and some examples uses are shown. There’s a useful but short section concerning the impact of delayed duration on other SQL Server components, including AlwaysOn Availability Groups, SQL Server Failover Clustering, log backup, log shipping and transaction replication. In essence, these will only see fully committed transaction, so you will need to be aware of this to reconcile differences. AlwaysOn Availability Groups that use synchronous commits are especially liable to impact. Overall, this chapter provides a good brief introduction to delayed duration transactions, how to implement it, its various options and implications. There are some useful Microsoft links for further information on certain topics. I enjoyed the discussion of the consequences of delayed durability on the various HA/DR options, identifying some very important pitfalls to be aware of. The sub-sections are typically short, several are only one paragraph long, I know this is an introduction but more detail or more links would have been useful.
Chapter 5 AlwaysOn Availability Groups The chapter opens with an overview of what an AlwaysOn Availability Group is, and why it’s important. An AlwaysOn Availability Group is Microsoft’s recommended HA and DR solution, it allows groups of databases to failover together, additionally the secondaries are usable for read-only reporting. AlwaysOn Availability Groups were introduced in SQL Server 2012, and were one of the major reasons that some shops upgraded their SQL Servers. AlwaysOn Availability Groups have been enhanced in 2014, the enhancements are discussed in this chapter, and include:
The advantages of hosting the replica in the cloud are discussed, these include improved DR, since the replica is located offsite, and you don’t need on-premises resources. The section also considers the commit mode and reporting/backups on secondaries, but all under the heading of Microsoft Azure, which is inappropriate, these features relate to secondaries located anywhere. The heading could have highlighted that secondaries stored in Microsoft Azure can only use asynchronous mode – but it didn’t. There’s a brief section on building AlwaysOn Availability Groups, involving Windows Server Failover Cluster (WSFC), enabling AlwaysOn Availability Groups on each instance, and the Availability Group listener. There’s a link to building WSFC, useful since this is probably outside the DBA’s domain. Having set up the Windows cluster on each node, there’s a step-by-step walkthrough of creating an Availability Group. This section is more practical, showing how to name an Availability Group, add databases to it, and specify the secondaries and their properties (e.g. commit mode, readability, backup preferences etc). The troubleshooting section describes a new function sys.fn_hadr_is_primary_repica(‘dbname’) that determines if a database is the primary (useful for programmatic decision making). Changes to the AlwaysOn dashboard are also described (e.g. availability mode, estimated recovery time). The final section describes how to create a cloud-based AlwaysOn Availability Group. In essence you create an on-premise Availability Group, and then create an AlwaysOn Availability Group in Azure, using either Microsoft Azure (everything is in the cloud) or a hybrid environment This chapter provides a useful overview of the AlwaysOn Availability Group enhancements. The step-by-step creation of the AlwaysOn Availability Group should prove useful. Several sections felt a bit light, and some areas (e.g. use the wizard) could have had more explanation. Chapter 6 Performance Improvements This chapter relates to performance improvements other than those discussed earlier (i.e. in-memory optimized tables, and delayed durability). With partition switching and indexes, it is now possible to rebuild individual partitions, one useful switch is the wait_at_low_proprity that waits a given time to obtain locks. Database statistics can now be updated for a given partition. Buffer pool extensions allow the use of Solid State Disks (SSDs) to extend RAM, which can improve I/O significantly. The resource governor, which previously allowed resources to be limited by CPU and memory, can now also limit the amount of I/O. The cardinality estimator has been improved, with the aim of helping produce better query plans, while most queries should run faster or at the same speed, but some may run slower – so need testing. Columnstore indexes are now updatable and support clustered indexes. There’s a useful Microsoft link providing more information. Next is a helpful walkthrough for creating a columnstore index within SSMS using the columnstore wizard, SQL code is also given. There is also an example of updating a table that has a clustered columnstore index. I found this chapter disappointing. Most of the topics have just a paragraph or two of description. Greater detail or links for more information would have been useful. Conclusion This book is a short introduction, from an administrator’s viewpoint, to the new features of SQL Server 2014. It’s generally practical in its approach, containing step-by-step walkthroughs, together with supporting screenshots. It’s generally an easy read, with good but limited explanations. The book’s title is incorrect, it should reflect the book relates to new features of SQL Server 2014 only. Some people (myself included) would actually prefer a book that contains only the updated features. I can imagine some people looking for an introductory book for general SQL Server administration, getting annoyed at purchasing a book that contains specifically new features only. One of the problems with reviewing this book is the “Getting Started” part of the title, it provides an apology for the lack of depth and omissions. This is compounded by the book’s small size, only 114 pages. For example, there’s nothing about the new security enhancements (e.g. connect any database, impersonate any login). I wanted more! I had to keep reminding myself this is a “Getting started” book… Although the diagrams are correctly placed, it might have been useful to provide them with titles – making subsequent chapter scanning easier. I’m not sure for whom this book is intended, if you’re new to SQL Server administration, you will need a more general book, not just the new features. If you’re experienced with SQL Server administration, you probably need a more detailed book – perhaps this is the book you read before the detailed one (or lookup the detail on the Internet)? As a brief introduction to the new administration features of SQL Server 2014, I can recommend this book, but I suspect you’ll soon need a follow-on book containing more detail.
|
||||||
| Last Updated ( Wednesday, 28 May 2014 ) |