If you’re a Business Intelligence (BI) power user, May's issue SQL Server Pro magazine’s cover story will help you get the most out of Microsoft Power View. There's also plenty for SQL devs.
In our regular look at the contents of the online subscription-based monthly magazine, bold signals the title of articles.
Getting the Most Out of Microsoft Power View presents the following six tips that will allow you to work with Power View more effectively:
Easily connect to Power View
Easily change data sources
Customise the display of Power View reports
Monitor Power View usage
Get Power View on the Desktop
Make the most of screen real estate
Also discussed are the latest features just added in SQL Server 2012 SP1, including drillable charts, map control, KPIs, and hierarchy support. The article is an easy read, and a must for Power View users.
If you want to improve your SQL development experience, you really should Get to Know SQL Server 2012’s SQL Server Data Tools. SQL Server Data Tools (SSDT) is the latest incarnation of SQL development tools to help you with your SQL Server work. There are some great features to tempt you away from your existing methodology (of manual coding in SSMS). These features include:
Richer development environment (IntelliSense, code refactoring, code navigation, find all references, sandboxed development and deployment)
Declarative database development. Where you say what you want to do (e.g. rename and add columns), and SSDT implements it for you
SQL Compare, allows you to compare schemas
Changes can be scripted so you can review the changes before applying them (or perhaps more realistically, until you gain confidence in the tool). The refactoring tool allows you to rename a column and automatically amend all the relevant stored procedures to use the renamed column. With the advantages it bestows, I can see SQL shops increasingly moving towards SSDT, and as such this is my favourite article this month.
There’s a great comparison of COALESCE vs. ISNULL. While at first it looks like they are very similar functions, this article shows how very different they are. Highlights include how each determines the data type of the resultant expression, and the nullability of expression. As always Itzik Ben-Gan provides some great accompanying example code. It’s a surprisingly interesting read.
Michael Otey provides a step-by-step guide to Migrating to SQL Server 2012. This introductory article should prove very useful as more organizations start to upgrade to SQL Server 2012. The article contains pointers to potential pitfalls, and a large number of screenshots that should ensure the upgrade process goes smoothly. While the article is introductory, it’s quite detailed too.
This month’s SQL Server Questions Answered column discusses Recovering a Database with a Missing Transaction Log. The answer given is to run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS, which will recover the database but any damaged data is lost. This may give the misleading impression the database is now ok. I’ve often wondered if this option should be renamed to REPAIR_BUT_IS_IT_ANY_USE. As always there is no substitute for having a solid and tested database backup strategy. Another question asks Is Recovery Interval Guaranteed? The answer is a resounding ‘no’, mainly because the UNDO and REDO work that needs to be done after a crash can take longer than the default one minute guideline given.
One aspect of having a solid backup strategy is to ensure you verify the backup you’ve taken is viable. Too many people discover their backup is useless, and their backup process has been useless for months, only when they need to restore a backup in an emergency. Tool Time discusses the SQL Server RestoreChecker Utility, which verifies your backups are viable, there’s a free version too – so now there’s no excuse! Don’t forget SQL Server includes helpful inbuilt options too, like ensuring your page_verify_option_desc is set to CHECKSUM, and using the RESTORE VERIFYONLY option which verifies a backup is good without actually restoring it.
This month’s editorial continues the recent discussions about Growing Big Data. Previously, collecting Big Data may have been too arduous or the data just didn’t exist, however, now there are ever increasing streams of data becoming available. Additionally, new technologies like Hadoop allow unstructured data to be processed in new ways. Both these features mean new opportunities for decision making. The article continues by highlighting how the forthcoming PolyBase technology will link Big Data and SQL Server. I suspect we’ll see Big Data increasingly working alongside SQL Server in the future.
Can any of us get enough free tools? Here 8 Free SQL Server Tools are discussed briefly. One of the tools is SSDT which by coincidence is reviewed elsewhere in this issue! Other notable mentions include a Database Copy Tool, and SQL Sentry Pan Explorer 2.0 which analyses the query plan to highlight problem areas. A few minutes with each of these tools should show you their value, and increase the area of your solution domain should problems arise.