Become a SQL Server superhero and save the day when disaster strikes! Find out how in April's SQL Server Pro, the online subscription-based monthly magazine’s. Here’s a detailed look at April's issue.
The items in bold below correspond to the name of the article in the issue.
In SQL Server 2008 there are many ways to implement high availability, including clustering, mirroring, replication and log shipping. However business requirements typically mean these features were often cobbled together to produce a required solution. SQL Server 2012 has a new feature that eases the implementation of high availability, namely AlwaysOn Availability Groups.
In April's cover story Be Invincible with AlwaysOn Availability Groups, Brent Ozar provides an interesting, informative and amusing discussion of AlwaysOn Availability Groups, what it is, what problems it overcomes, and why you will want it. Brent makes an insightful point that with recovery, you should plan for the worse case scenario, since you don’t want to make an already bad situation worse. If your business can’t afford to lose any data or time when disaster strikes, you need to implement AlwaysOn Availability Groups.
Each new version of SQL Server brings additional functionally that can sometimes feel overwhelming. James Serra provides a comprehensive overview of Business Intelligence Enhancements in SQL Server 2012. James discusses improvements in:
SQL Server Integration Services (SSIS)
Data Quality Services (DQS)
Master Data Services (MDS)
SQL Server Data Tools (SSDT)
Additionally, changes in Data Management and Warehousing (xVelocity, tabular model, columnstore index) and End-User Reporting (Power View, PowerPivot, SSRS) are also discussed. If you want to get up to speed on recent BI changes, this is an ideal starting point.
I think we’ve all had times when we’ve wanted to Copying Data with Dependencies from one set of tables to another set. Itziz Ben-Gan provides two approaches to doing this, one using the MERGE statement, the other with the new 2012 Sequence Objects. I must admit, I was a bit disappointed with this article, due to my expectation. I had expected it to be a generic/dynamic solution, where I could supply the lead table and a SELECT statement of the required rows, and it would intelligently determine the relevant data in any other related tables (via Referential Integrity). Instead the solution knows the tables involves and hard-codes the relationships. Maybe someone reading this article could create such a solution? (As an aside, this would also be useful for determining the TRUNCATE order for a series of related tables.)
PowerScript is Microsoft’s premier scripting language, useful across all Microsoft’s servers. It’s an advanced .NET object orient language with formidable capabilities. This introductory article discusses Using SQL Server Management Objects with PowerShell. It outlines the installation prerequisites, and has some useful example scripts to get you started. These scripts include:
Listing SQL Server Instances
Retrieving owner, recovery model, and status of each database
Retrieving information about SQL Server Agent jobs
PowerShell is being used increasingly for everyday database administration tasks, if you want to learn about PowerShell, I recommend you start with this article!
This month’s Editorial discusses the perennial importance of Education and Training. It provides several useful links to resources, many of which are free, including Microsoft Virtual Academy and TechNet Virtual Labs. It’s certainly worthwhile looking at the courses available, I think everyone will find something useful.
As always, there are interesting snippets of information in SQL Server Questions Answered. When SQL Server can’t run a query, it records internally the reason why it is waiting (e.g. on blocking or IO to complete), these cumulative waits are a great starting point for investigating your server’s problems. However there are several innocuous waits types that can be safely removed from your investigations. These waits are discussed in Filtering Out Benign Waits. The next question discusses the reason why there are occasional problems with Locking and DBCC CHECKDB. Lastly, to improve performance, someone asks about Avoiding Logging in User Operations. However, because there is always a need to potentially rollback operations (even within tempdb), logging is always required. I was a bit surprised that some non-logged options, e.g. SELECT INTO, weren’t mentioned.
Virtualization is increasingly seen as the answer to making better use of server resources, and as such is seen increasingly in IT departments. Michael Otey gives 5 Virtualization Tips for SQL Server. The tips are especially interesting because many virtual machine wizards typically produce sub-optimal virtual SQL Server instances.