In this issue:

Tech News : General Interest

Tech News : Security

Tech News : The Lighter Side

Microsoft News : General Interest

Microsoft News : Patches, Bugs

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Excel

Blogs : Hardware

Blogs : High Availability/Disaster Recovery

Blogs : Performance and Tuning

Blogs : PowerPivot

Blogs : Professional Development

Blogs : Security and Auditing

Blogs : Software Development

Blogs : T-SQL

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2013-05-20

SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
Editorial - Powershell for DBAs

A few weeks ago I attended SQL Bits and noticed there were a number of people talking about using Powershell (PoSH) with SQL Server. Laerte Junior have a troubleshooting talk and Allen White talked about maintaining performance. I've started to notice more and more people are showing Powershell as a tool to accomplish repeatable tasks, run across multiple servers.

I've struggled to use Powershell much in my work, but I don't have a lot of repeatable tasks in SQL Server. I tend to be more like a consultant, with a lot of ad hoc work that varies across different instances. However I do have scripts I run regularly, like the sp_Blitz script, and these are exactly the type of tasks that work great in Powershell. As I start to work in Azure, scripting is important and I can see the value in using something like PoSH that is easily dropped onto a new machine to perform a variety of tasks, like configuring SQL Server.

This week I saw a post on getting started in Powershell and it reminded me that I should be improving my skills. I might not use them often, but more and more content, and automation examples use Powershell and being able to read and understand it will become more important. That might especially be true if you find storage or system administrators wanting to run PoSH on your database servers. I'd hope you would understand what they planned to do before they do it. Or you can backtrack and understand what they changed with their script, which might not be what they tell you they changed.

I don't know that Powershell will be required at most, or even many, companies. However I do know that Microsoft is pushing it, it's not hard to learn, and it's handy. If I were back in a situation where I was managing 10, 20, or more instances, I'd want Powershell to help me handle the basic, tedious tasks. At some point those easily automated tasks won't be worth paying someone to do. If you can't add value in other ways, you might find yourself struggling to maintain employment.

» Join the debate, and respond to today's editorial on the forums


The Weekly News

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.


Tech News : General Interest

IBM's DB2: After 30 Years and a Look Ahead - IBM's DB2 will mark its 30th anniversary June 7. Here's a glimpse at its roots and its future....(more)

Software developer wages fall 2 percent as workforce expands - The U.S. tech industry added nearly 64,000 software related jobs last year, but as the workforce expanded, the average size of workers' pay checks declined by nearly 2 percent. There are multiple theories for the decline in pay, but a common one cited by analysts is simply that the new people being hired are paid less than those already on the job....(more)

Windows RT's race to the bottom - The checkered flag is out and the race to the bottom for Windows RT manufacturers has entered the final laps....(more)

Amazon gives DynamoDB a performance boost - Amazon Web Services is improving the performance of its DynamoDB database service with Parallel Scan, which gives users faster access to their tables.DynamoDB is a NoSQL database service that, like Amazon's other cloud services, promises to offload administration tasks while allowing enterprises to scale easily and pay only for what they use. The service stores data on solid-state disk drives and creates replicas in three different locations to improve availability....(more)

On key software decision, top patent court grinds to a stalemate - Over the course of the past year, a case about four financial software patents has taken on great significance. In 2007, Alice Corp accused CLS Bank of infringing its patents on a type of computerized trading platform that used "shadow accounts." In the years since then, the Supreme Court had significantly tightened up the rules about what is patentable. In 2011, Alice's patents were thrown out by a federal judge, who ruled they didn't cover patentable subject matter....(more)

Small Businesses Lose Billions with Involuntary IT Managers' - When nontechnical employees -- also known as "involuntary IT managers" (IITMs) -- are charged with managing their company's IT solutions, it leads to losses. Big losses. In fact, those losses add up to more than $24 billion in productivity each year in small businesses in five countries including the U.S., according to an AMI-Partners small-business study commissioned by Microsoft Corp. This loss is a direct result of IITMs taking time away from primary business activities. ...(more)

Tech News : Security

Sophisticated Bank Hack Leads to $45 Million ATM Theft - It only took a few hours for thieves to rob $45 million from ATM machines -- and it seems like it wasn't all that hard to pull off. Law enforcement agencies from more than a dozen nations organized to catch the thieves. That effort led to the arrest of seven people in the U.S., accused of operating the New York group of what prosecutors say was a criminal network spanning 27 countries. ...(more)

Tech News : The Lighter Side

Type 'Atari Breakout' Into Google Image Search for a Cool Surprise - Google is commemorating the 37th birthday of classic Atari game Breakout in the best possible way: It turned Google Image Search into a version of the game....(more)

Type 'Beam Me Up' on Bing for a 'Star Trek' Surprise - Microsoft's Bing search engine just got even more inventive, creating a strange new world where no web geek has gone before. To take a unique trip, simply type "beam me up" in the Bing search engine, and see what happens....(more)

Want to work on Bing? Have I got an Easter egg for you! - Even if you don't want to work for Microsoft and couldn't care less about Bing, this novel help-wanted Easter egg should bring a chuckle -- particularly when you learn the secret to displaying the egg with Firefox or Chrome....(more)

Commander Chris Hadfield bids adieu to ISS with “Space Oddity” cover - Two astronauts completed an impromptu spacewalk on Saturday afternoon, per a press release from NASA. The crew of the International Space Station discovered a small leak in the cooling system, and the Earth crew stayed up overnight to plan an expedition to repair it....(more)

Microsoft News : General Interest

Why Microsoft won't charge for Windows 'Blue' - Analysts believe Microsoft won't dare charge for this year's update to Windows 8, but may do for future annual releases...(more)

Windows developer says kernel dev being mismanaged - Anonymous developer on Windows kernel team attributes Windows' slow performance to infighting, turnover, and mismanagement at Microsoft...(more)

Microsoft News : Patches, Bugs

Microsoft rushes Internet Explorer 8 patch release - Just 11 days after issuing an advisory, Microsoft has released a patch for a bug in Internet Explorer 8 that bedeviled the U.S. Department of Labor earlier this month. Microsoft's speedy release of this patch "is an outstanding example of Microsoft's responsiveness to the security community and their users," wrote Andrew Storms, director of security of operations for security software provider Tripwire, in an email statement....(more)

Microsoft Publisher Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Publisher, which can be exploited by malicious people to compromise a user's system. 1) An unspecified error does not properly validate an array size. No further information is currently available. 2) An integer overflow vulnerability exists. No further information is currently available. 3) An unspecified error can be exploited dereference an arbitrary pointer. ...(more)

Microsoft Lync / Office Communicator Use-After-Free Vulnerability - A vulnerability has been reported in Microsoft Lync and Office Communicator, which can be exploited by malicious users to compromise a user's system. A use-after-free error within the Lync control can be exploited to dereference already freed memory. ...(more)

Microsoft Windows Essentials Windows Writer URL Handling Vulnerability - A vulnerability has been reported in Microsoft Windows Essentials, which can be exploited by malicious people to manipulate certain data. The vulnerability is caused due to an error when handling URL and can be exploited to override Windows Writer proxy settings and overwrite files accessible to the user. ...(more)

Microsoft .Net Framework Two Security Bypass Vulnerabilities - Two vulnerabilities have been reported in Microsoft .Net Framework, which can be exploited by malicious people to bypass certain security restrictions. 1) The .NET Framework CLR does not properly validate XML signatures, which can be exploited to bypass the signature validation mechanism via an untrusted or invalid XML signature. 2) An error within the WCF endpoint authentication mechanism when handling queries can be exploited to bypass the authentication mechanism and e.g. gain access to otherwise restricted endpoint functions. ...(more)

Microsoft Windows Kernel Multiple Privilege Escalation Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Windows, which can be exploited by malicious, local users to gain escalated privileges. 1) A race condition error within the DirectX graphics kernel subsystem when handling certain objects can be exploited to gain escalated privileges. 2) An unspecified error within the Windows kernel-mode driver (win32k.sys) can be exploited to cause a buffer overflow and execute arbitrary code with kernel privileges. ...(more)

Microsoft Windows HTTP.sys Denial of Service Vulnerability - A vulnerability has been reported in Microsoft Windows, which can be exploited by malicious people to cause a DoS (Denial of Service). The vulnerability is caused due to an error within the HTTP protocol stack (HTTP.sys) when handling HTTP headers. This can be exploited to trigger an infinite loop and cause the system to stop responding by sending a specially crafted HTTP request. ...(more)

Microsoft Office Word RTF Data Parsing Vulnerability - A vulnerability has been reported in Microsoft Office, which can be exploited by malicious people to compromise a user's system. The vulnerability is caused due to an error when parsing Rich Text Format (RTF) data and can be exploited to corrupt memory. ...(more)

Blogs : Administration

Job Schedules – SQL Agent - To be fair, the majority of this code was grabbed from the internet, and this is not something I modified to the extent that I could call it my own. Michael Abair is the original author, and the original code can be found at the link below. My contribution is limited to very minor changes and a lot of hitting the tab key. I know it works just as well, maybe even 2ms faster, without the whitespace, but I had to do it....(more)

1TB Databases for Developers [Video] - If you write code that accesses a database one terabyte or larger, you need to know that things are different around here. When you hit the very large database (VLDB) territory, you need to pay particular attention to statistics, TempDB, and staging tables. Microsoft Certified Master Brent Ozar will share his favorite lessons for developers who work with either OLTP or data warehouses in this 25-minute webcast:...(more)

Dragging a name from the object explorer to a query window - In the same vein as Steve Jones’ series on Customizing SSMS this is a fairly basic trick, but one that not everyone appears to know. A fairly standard layout of SSMS looks like this. ...(more)

SQL Server – How to Move Table to Another Schema - Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas....(more)

Blogs : Analysis Services / BI

UseRelationship() and Tabular Row Security - Quick summary: DAX measures in SSAS Tabular that use the UseRelationship() function return an error when row security is applied to a table. I’m surprised this hasn’t been documented somewhere – I know Marco came across it some time ago, but I ran into it again recently so I thought I’d mention it....(more)

Blogs : Backup and Recovery

Roll Logs – Backup Recovery - Rolling log files for a day, especially with 15 or even 5 minute log backups is a pain at best. Here’s a slightly better way to do it. Set the two variables at the top to the directory where the log backups are and the database you’re looking to restore. It will, assuming you named your log backups as DatabaseName_Log*, display all log backups in that folder in chronological order into an output script which is best read when doing results to text (Query/Results To/Results To Text)....(more)

Blogs : Computing in the Cloud

Session State Management in Windows Azure Web Roles - One of the frequent questions that developers and customers ask me during my Windows Azure related discussion is how to manage session state in Windows Azure Web Role. With Web Roles supporting full IIS (previously it was just hosted web core), the inclination to fall back to the tried and tested methods of either in-proc where the session is stored in the webserver memory or store it in SQL Server is very tempting. A few queries also hover around out-of proc and storing them in State Server....(more)

Infrastructure as a Service for SQL Server (IaaS for SQL Server) - With “IaaS”, the usage of cloud has further increased productivity. Infrastructure available as a service, offers Virtual machines which are available as templates. So you can pick the size and the version desired and deal only with your application without having to worry about Infrastructure. ...(more)

Where are the Windows Azure customer case studies – and why aren’t there more? - “Case Studies” are a great tool when you’re evaluating a platform. Having evidence that other companies have deployed Windows Azure, in addition to how they did it, is a good way to plan your own deployments or even just evaluate whether Windows Azure would be a good fit. And we have several case studies you can examine here: https://www.windowsazure.com/en-us/home/case-studies/...(more)

Blogs : DMO/SMO/Powershell

10 Tips for the SQL Server PowerShell Scripter - Today’s blog is a bit unique. I was throwing around ideas with Chad Miller, and somehow we came up with the idea that he would share some tips for the SQL Server Windows PowerShell scripter. You can read more about Chad and see his other blog posts on the Hey, Scripting Guy! Blog site....(more)

Use Powershell to find and punish – er, delete – rogue files - I like to know what’s on the drives that are supposed to be dedicated to data and log files. So I run a quick check of sys.master_files:...(more)

SqlDev PowerShell 101: Getting Started with PowerShell - This is the first in a new series focused on PowerShell for SQL developers. That means using PowerShell to create and/or modify table schema, stored procedures, and indexes. In other words, automating code development and database tuning processes. This is mostly what I’ve been using PowerShell for so far, and I have to say, I am LOVING it! It’s allowing me to save *days* of coding and validating. ...(more)

Blogs : Excel

Parameterizing an Excel Spreadsheet - I’ve had a few discussions in the past week about not only including Excel spreadsheets on PerformancePoint dashboards, but specifically about how to pass values from Filters to the spreadsheet. I did a few demonstrations to show the capability to some customers. Immediately after a few of the demonstrations I received a emails asking me to explain how I accomplished passing the filters to the spreadsheet. I would explain that I added a parameter to the spreadsheet before I saved it to SharePoint. Each time the customer would say, I did not know that was possible. As a result, I have decided to document the steps in this blog. ...(more)

The Tale of One Tabular Model and Excel 2013 with Multiple Power View Reports - SQL Server 2012 Reporting Services introduced Power View, which was initially available only in SharePoint. This limited the use of Power View only to those individuals and organizations that had SharePoint deployed in some environment. To make Power View more widely available it is now an add-in for Excel 2013. Now anyone with Excel 2013 can create reports and dashboards using Power View. This leads me to the story....(more)

Data Explorer May update is available now! - As some of you may have already noticed, we released a new Data Explorer update on Friday. You can get it from our download page or by clicking the Update button in the Data Explorer ribbon tab....(more)

Blogs : Hardware

Monitoring SSD Performance - Everyone wants to make sure they’re getting the best performance out of their solid state storage. If you’re like a lot of people, you want to make sure you’re getting what you paid for, but how do you know for sure that the drive is performing well?...(more)

Storage Spaces/VHDx and 4K Sector Size - This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes. SQL Server - New Drives Use 4K Sector Size: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx ...(more)

Blogs : High Availability/Disaster Recovery

Flow Control in Always On Availability Groups – what does it mean and how do I monitor it? - Flow Control is primarily a mechanism to gate or throttle messages to avoid use of excessive resource on the primary or secondary. When we are in “Flow Control” mode, sending of log block messages from the Primary to the Secondary is paused until out of flow control mode....(more)

Blogs : Performance and Tuning

Indexes – Unused and Duplicates - Indexes aren’t free, and many databases end up with unused indexes. Every time you make any update to a table you will be updating the clustered index (I assume no heaps, because I hate heaps), and every index that has uses one of the columns that were updated. Inserts and Deletes affect every column and will affect every index, with exceptions for non-typical indexes. These updates cost a lot, to the point that I have several processes that disable certain nonclustered indexes, do all of the work, then rebuild the indexes afterwards....(more)

Improving ETL Processes - My name is Ratna Rekha Koukuntla and I am a Senior SDET with Microsoft’s Sales and Marketing IT (SMIT) organization. I am working on a Data warehousing application involving multiple ETL jobs and Packages. Based on our customer needs to have monthly releases, we moved to an Agile software development lifecycle, and since then, have been experimenting with ways for quick test turnaround, particularly in the area of performance testing....(more)

The DBA Detective: The Case of the Missing Index - When problems arise in SQL Server, we're faced with a server full of suspects, including disk I/O, memory, CPU, incorrect or missing indexes, badly written T-SQL code, out of date statistics, and full disk drives. All of these have motive and opportunity to murder the performance of our databases, and it's the DBA's job to collar the culprit, quickly without relying on luck or heroics. ...(more)

Top 3 Performance Killers For Linked Server Queries - One thing I have noticed in all my years as a data professional: few users understand (or care) how far away they are from their data. Quite often they expect instant results from their queries despite there currently being an upper bound due to things like network bandwidth, the speed of light, and the data sitting on a server on the other side of the world....(more)

Why is your ASP.NET application running slow? - Any queries run against SQL Server or Oracle databases are recorded, along with how many times those queries were run and how long they took. That information is shown alongside your .NET code, so you can quickly see why the code you wrote resulted in those queries being executed. This can be particularly powerful if you’re using ORMs like Entity Framework to access data, which tend to be an impenetrable black box where performance issues can arise, but where it’s difficult to understand why....(more)

Blogs : PowerPivot

Accumulating Data In An Excel Table Using Data Explorer and PowerPivot - One of the first questions I get asked after showing someone PowerPivot for the first time is “Can I add new data to a PowerPivot table that already has data in it?”. Out of the box, of course, the answer is no: when you process a table in PowerPivot you have to reload all the data from your data source, you can’t just append new data (unless you’re using copy/paste to load data, which isn’t a good idea). However, there are a lot of self-service BI scenarios where the ability to do this would be extremely useful: for example, you might want to scrape stock quotes from a web page every day and then, in an Excel workbook, accumulate that data in a table so you can analyse historical stock prices with PowerPivot....(more)

Blogs : Professional Development

The joy of being a programmer - I am programming since I am 10 and I am now 38. Today I measure how much good programming bring to my life, directly and indirectly. I’d like to give credit to aspects I love in this job. Hopefully some young people will read this and will consider maybe doing one of the most wonderful job on earth....(more)

Blogs : Security and Auditing

Clickjack attack – the hidden threat right in front of you - XSS protection: check! No SQL injection: check! Proper use of HTTPS: check! Clickjacking defences: uh, click what now?! ...(more)

Blogs : Software Development

OrcaMDF Is Now Available on NuGet - Thanks to Justin Dearing (b|t), OrcaMDF is now available on NuGet! OrcaMDF being on NuGet means the bar just got lowered even more if you want to try it out. Let me show you how easy it is to read the Adventureworks 2008 R2 Database using OrcaMDF: ...(more)

Blogs : T-SQL

Another argument for stored procedures - This is one of those religious/political debates that has been raging for years: should I use stored procedures, or should I put ad hoc queries in my application? I have always been a proponent of stored procedures, for a few reasons:...(more)

Puzzle and Answer – REPLICATE over 8000 Characters - It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle. SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string. ...(more)


Administrative