Welcome to Database Weekly

Improving performance by using SET STATISTICS IO When I started using SQL Server the company I was working for at the time had no skill in this area, they were more focussed on other platforms and so I had nobody to learn from. The community was in it’s infancy and I had no idea people would blog about this kind of stuff. You were pretty much limited to what was in Books Online and that was kind of dry and hard to navigate. read more... The Index Your Queries will Never Request (A Clustered Index!) When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex™. read more... How to Pass Calculated Columns in the WHERE & GROUP BY clause in T-SQL..? Suppose we have a calculated Column that is a result of some complex calculation and we want to refer it in a where clause (or a group by clause). This is not straight forward since we can’t use the computed Column directly in the where clause like this: read more... Using SMB shares with Hyper-V Replica SMB is getting a lot of attention with Windows Server 2012, and we’ve had questions from a few customers regarding the inter-play between SMB shares and Hyper-V Replica. In this post we’ll share our experience around setting up and using various configurations involving SMB shares and Hyper-V Replica. The issue we were expecting to run into is the apparent lack of authorization to use the SMB share, when using remote management. read more... Microsoft Locks Down the Cloud With Multifactor Authentication Microsoft makes its Windows Azure cloud services platform a bit more hacker-resistant with a security feature that Web-facing tech companies are increasingly embracing. read more... The NSA upshot: We're finally taking Internet privacy seriously The debate over Snowden's status as PRISM leaker obscures real issue: The reach of the Fourth Amendment in a digital age read more... NSA's lax ban on USB drives may have contributed to PRISM leaks News about the NSA and FBI's surveillance programs read more... Microsoft Study Finds Gap in SMB Cloud Perception, Reality Small- and medium-size businesses have been wary of cloud services because of security, privacy and reliability issues. But a new Microsoft report, released this week, found that perceptions of clouds contrast with actual experiences. read more... SQL Server 2012 Discovery Report - Determining what SQL Server Components are Installed This continues to be one of the more frequently asked questions on the MSDN Forums and is important for many reasons, including patching, security, compatibility with other applications, network compliance, and even troubleshooting failed installs. Over the years, I've pointed many people to Peter Saddow's 2009 blog entry highlighting the introduction of the Discovery Report feature included within SQL Server setup as part of the SQL Server 2008 release. Since there have been two major releases of SQL Server since the blog entry was written, I wanted to update the blog entry with the SQL Server 2012 version of Discovery Report. read more... Surfing Restaurant Inspections with Microsoft Data Explorer and GeoFlow Father’s Day is approaching and you might be thinking about a good place to have a nice lunch with your Dad… We would like to show you how Data Explorer and Geoflow can help you gather some insights to make a good decision. read more... IT Jobs Growth: Sure and Steady, but Modest While the IT jobs market continues to grow despite weakness in the nation's employment data, career experts are cautiously optimistic. read more... Best practice recommendations for writing Dynamic SQL Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over the years to make sure that when writing my dynamic SQL it breaks less often. Note I don’t say “doesn’t break ever”, dynamic SQL is tricky stuff. The best you are likely to get is “less often”. Some people may manage 100%, I’m not one of them, but I’ll bet that if they are out there then they use every one of these recommendations. read more... SQL Server Tipping Games – Why Non-Clustered Indexes are just ignored! Almost everytime when I’m doing SQL Server consulting engagements, DBAs are showing me queries and their Execution Plans, where SQL Server is just ignoring a good defined Non-Clustered Index. Imagine for example the following table and index definition: read more... Why should we upgrade off of SQL Server 2000? But, it still works FINE… A good many companies still have servers chugging along on SQL Server 2000 (or even, as some of us noted on Twitter, SQL Server 7.0 and 6.5) . If you’re one of those companies, and you’ve never had a problem with it, why should you upgrade?1, stability: It’s completely out of support. Microsoft won’t release any new patches, and won’t help you if (when) something blows up. New viruses or bugs out there can find and exploit your server read more... Update Your Audit Queries for SQL Server I was working with an auditor today who is working through a system with an external audit agency. The external agency handed us scripts to run across SQL Server, Active Directory, etc. I took on the SQL Server scripts. Then I refused to run them. The main reason I pushed back is because the scripts were valid for SQL Server 2000, but they aren't for SQL Server 2005 and above. In other words, we could like fine based on the scripts and not be fine. The scripts don't adequately check the controls on the system with regards to SQL Server. So what caught my attention? read more... Printing the Report Parameters on SSRS I'm surprised there isn't an option in the Report Manager for allowing you to include on the print out of the report the report parameters you used. If there is, someone please set me straight. Quick background, this report I was working on is on SQL 2012, I'm not using Sharepoint, and it's using an analysis services cube data source. It has 13 report parameters: a date parameter and 12 multi-value parameters. A lot of my users only read printed versions of the reports that someone runs for them. I needed a way to print the report parameters that were used but not print the parameters that were left alone (i.e. the default "All" was selected). I printed the values selected using the Parameters Collection References and a join statement, and controlled the visibility of each row with an IIf statement, checking for All as the first selection. Here's what I did to achieve this: read more... Painless management of a logging table in SQL Server Tables that log a record of what happens in an application can get very large, easpecially if they're growing by half a billion rows a day. You'll very soon need to devise a scheduled routine to remove old records, but the DELETE statement just isn't a realistic option with that volume of data. Hugo Kornelis explains a pain-free technique for SQL Server. read more... Preparing to Upgrade your SQL Server It isn't a problem to use deprecated TSQL features until it comes to the time to move the database to a server with a newer version of SQL Server, because The Upgrade Adviser tool will tell you what needs to be changed. An alternative is to flush out the use of archaic features via scripting during the development process so there are no surprises later. Jonathan Allen shows how read more... The Accidental DBA (Day 12 of 30): Backups: VM Snapshots In the final post in the Backup section of our Accidental DBA series, I’m going to a look at backups using virtual machine (VM) snapshots, which are popular among VM administrators but may not be the right solution for your SQL Server recovery needs, depending on your RPO and RTO requirements. read more... What the NSA can do with “big data” One organization's data centers hold the contents of much of the visible Internet—and much of it that isn't visible just by clicking your way around. It has satellite imagery of much of the world and ground-level photography of homes and businesses and government installations tied into a geospatial database that is cross-indexed to petabytes of information about individuals and organizations. And its analytics systems process the Web search requests, e-mail messages, and other electronic activities of hundreds of millions of people. read more...