In this issue:

Tech News : General Interest

Tech News : Security

Microsoft News : Patches, Bugs

Hardware News

Blogs : Administration

Blogs : Data Mining

Blogs : Database Design, Theory and Development

Blogs : DMO/SMO/Powershell

Blogs : Excel

Blogs : High Availability/Disaster Recovery

Blogs : Performance and Tuning

Blogs : PowerPivot

Blogs : Replication

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : T-SQL

Blogs : XML, XPATH and XQUERY

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-01-14

SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
Lesson two has now been released! Read ‘Beating Backup Corruption’ and learn from the very best. Read now.
Red Gate Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
SQL Monitor Free eBook: SQL Server Concurrency
Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.
Editorial - The Java Danger

In the news this week, I saw something that surprised me. It wasn't in my technical news, but rather on CNN that there was an announcement of a Java vulnerability. I had seen this listed in tech news, but hadn't paid a lot of attention to it since I tend not to use Java on my browsers. The few places that require it remind me to turn it on and usually update it. However there's a vulnerability in Java that is being attacked widely and US-CERT issued an advisory asking people to disable Java on their browsers. If you're not sure how to do this, a quick search on the Internet should help you. 

For most of us that use SQL Server, this isn't likely an issue for our database systems. Java is typically not how our servers are accessed. There are people that use Java to access SQL Server instances, and for those people, I'd suggest you carefully watch your systems, understand the potential issues, and ensure you have good point to  point security enabled in your firewalls or routers.

Lots of our software has security issues and there are alerts being issued regularly by vendors and various security agencies. If you browse the 2012 alerts from CERT, you will see the list dominated by Microsoft products, of which there are many. Not all of them apply to SQL Server instances, but some do and you should subscribe to some bulletin service and be aware of the patches that are being released for the software you run.

Better security also comes from limiting the services you run on systems, not installing optional software, and using someting like the Best Practices Analyzer to check your installations and ensure you are not making common mistakes that can be exploited by anyone.

» 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

Samsung previews foldable, rollable smartphone displays - Flexible OLEDs that can be rolled up or wrapped around the sides of smartphones are getting closer to reality. Samsung Electronics showed some of its prototype flexible displays at CES Wednesday and launched a new brand name for them, Youm. Microsoft was also on hand, showing a research project for its Xbox Kinect that makes video games look like they extend beyond the edges of a TV screen onto the walls and floor....(more)

Consumers Generate Most of the World's Data, but Machines are Catching Up - The world’s trove of information is already expanding incredibly fast. Now automated applications will quickly enlarge it even further....(more)

Enterprise CIOs saw biggest gains as IT salaries inched up in 2012 - Positions in highest demand focus on quality control, records management, and data center operations...(more)

New laws keep employers out of worker social media accounts - Employers in Illinois and California cannot ask for usernames and passwords to the personal social media accounts of employees and job seekers under laws that took effect on Jan. 1. Illinois Gov. Patrick Quinn in August signed legislation amending the State's 'Right to Privacy in the Workplace Act.'...(more)

Tech News : Security

Homeland Security Warning: Disable Java from Browsers - It's not every day that the U.S. Department of Homeland Security makes an urgent recommendation to computer users. But they are doing so now, asking people to disable Java software because of a newly discovered security vulnerability that opens up millions of computers to criminal hacking. ...(more)

Researchers: Microsoft will pull trigger on emergency IE patch - Uptick in attacks, bypasses of recommended workarounds will force Microsoft to fix flaw criminals already using to hijack Windows PCs...(more)

Microsoft News : Patches, Bugs

Microsoft Windows Print Spooler Print Job Processing Vulnerability - A vulnerability has been reported in Microsoft Windows, which can be exploited by malicious people to compromise a vulnerable system. The vulnerability is caused due to a unspecified error within the Print Spooler and can be exploited via a specially crafted print job. ...(more)

Microsoft XML Core Services Integer Truncation and XSLT Parsing Vulnerabilities - Two vulnerabilities have been reported in Microsoft XML Core Services, which can be exploited by malicious people to compromise a user's system. 1) An integer truncation error when parsing XML content can be exploited to corrupt memory. 2) An unspecified error when parsing XSLT content can be exploited to corrupt memory. ...(more)

Microsoft .NET Framework Open Data Protocol "Replace()" Denial of Service Vulnerability - The vulnerability is caused due to an error within the WCF "Replace()" function when handling Open Data Protocol (OData) data and can be exploited to exhaust system resources....(more)

Microsoft Windows SSLv3/TLS Version Negotiation Downgrade Vulnerability - The vulnerability is caused due to an error when handling the SSL/TLS session version negotiation and can be exploited to downgrade the connection to SSLv2 via Man-in-the-Middle (MitM) attacks, potentially leading to the client using a weaker ciphersuite....(more)

Microsoft Windows win32k.sys Window Broadcast Message Handling Vulnerability - A vulnerability has been reported in Microsoft Windows, which can be exploited by malicious, local users to gain escalated privileges. The vulnerability is caused due to an error in win32k.sys when handling window broadcast messages. ...(more)

Microsoft Windows Flash Player Buffer Overflow Vulnerability - A vulnerability has been reported in Microsoft Windows, which can be exploited by malicious people to compromise a user's system The vulnerability is caused due to a bundled vulnerable version of Adobe Flash Player within Internet Explorer 10. ...(more)

Hardware News

USB 3.0 standard will soon allow up to 10Gbps - An enhanced version of USB 3.0 will deliver up to 10Gbps, twice the data speed of current connections. The 10Gbps SuperSpeed USB supplement to the existing USB 3.0 specification is expected to be completed by the middle of the year, the USB 3.0 Promoter Group said on Sunday at the International CES trade show in Las Vegas....(more)

Kingston Digital Debuts a Terabyte USB Pocket Drive - On Monday, the Flash memory affiliate of memory product maker Kingston Technology Company announced its DataTraveler HyperX Predator line at the Consumer Electronics Show (CES) now taking place in Las Vegas. The new line includes both USB 2.0 and 3.0 USB Flash drives, and the 3.0 version offers capacities up to 1TB, which the company said will be the largest such pocket drive in existence. ...(more)

Blogs : Administration

Database Management Survey 2013 - As part of my PhD with the Open University I am currently undertaking an independent database survey . My research investigates the current practices and procedures that are used and examines the complexities of managing database systems. ...(more)

Determining Default Trace Location - While working on some scripts against SQL Server’s default trace, I wanted to develop a sure fire way to find the location of the default trace regardless of the version of SQL Server and without having to tinker with file names....(more)

Day 9 of 31 Days of Disaster Recovery: Use All the Checksums - welcome to day 9 of my 31 Days of Disaster Recovery series. Today, I want to talk about the three ways you can use CHECKSUM to protect yourself from and identify corruption. Checksum is the default page verification option in SQL Server 2005+ and helps identify corruption to data pages. The other two uses of Checksum are options for the BACKUP and RESTORE commands. When we are done here, hopefully you will be convinced to use all 3 CHECKSUM options....(more)

Statistics Update Clarification - By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a set of triggers. The triggers are defined as...(more)

What port is my instance listening on? - I just had the interesting task of finding the port number that one of the instances I deal with is using. Normally this is a trivial task. I log on to the server, open up SQL Server Configuration Manager and check out the TCP/IP properties....(more)

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide - A client said the coolest thing to me the other day. He said, “We talked before about why we would want to start using optimistic locking in our code. How do we get there?”...(more)

Blogs : Data Mining

A Rickety Stairway to SQL Server Data Mining, Algorithm 1: Not-So-Naïve Bayes - As mentioned in my last post, I had to delay my article on the simplest of Microsoft’s data mining algorithms due to a number of factors, the worst of which turned out to be my own unfamiliarity with nested tables. This led to a lot of unforeseen performance problems that I have been able to work around for the time being by denormalizing the data source view (DSV) and schema mentioned in A Rickety Stairway to SQL Server Data Mining, Part 0.2: How to Dig Out of a Data Mining Cave-In....(more)

A Rickety Stairway to SQL Server Data Mining, Algorithm 2: Linear Regression - An algorithm that starts with the word “regression” seems an unlikely candidate to move forward with this series of self-tutorials in SQL Server Data Mining (SSDM), but don’t let the name fool you: Linear Regression is a powerful data mining tool that represents a logical second step in any workflow, including the climb up this creaky stairway. It can also be considered a sort of building block toward Decision Trees in one direction, as well as Logistic Regression and Microsoft’s Neural Network algorithm in another. Perhaps best of all, it is complex and powerful yet relatively easy for statistical laymen to understand....(more)

Blogs : Database Design, Theory and Development

Master Data Management (MDM) Hub Architecture - The Master Data Management (MDM) hub is a database with the software to manage the master data that is stored in the database and keep it synchronized with the transactional systems that use the master data. There are three basic styles of architecture used for Master Data Management hubs: the registry, the repository, and the hybrid approach....(more)

Blogs : DMO/SMO/Powershell

Executing tasks in parallel in PowerShell - Let's pretend that we need to write a script that can test to see if 10 different webpages are available. While this could be accomplished by retrieving the webpages one at a time, it does take some time to do this. Often something like 0.5 to 2 seconds for each page....(more)

Blogs : Excel

Getting a Power View report within Excel 2013 to work with SharePoint - I was setting up my SharePoint 2013 server to be able to use an Excel 2013 workbook that had a Power View Report in it. However, when I tried opening the workbook, I got the following error:...(more)

Blogs : High Availability/Disaster Recovery

Day 5 of 31 Days of Disaster Recovery: Dealing With Corruption in a Nonclustered Index - Welcome to day 5 of my series on disaster recovery. I want to start digging into some corruption scenarios. We’ll start off with the easiest form of corruption to fix, a nonclustered index....(more)

Day 7 of 31 Days of Disaster Recovery: Writing SLAs for Disaster Recovery - Today is day 7 in my series on disaster recovery. I thought I would switch gears for today and write about disaster recovery Service-Level Agreements (SLAs). Specifically, I’m talking about Recovery Point Objective (RPO) and Recovery Time Objective (RTO) SLAs....(more)

Corruption demo databases and scripts - I originally blogged a series of corruption demos and associated databases back in 2008, for use with SQL Server 2005 and 2008. Since then the releases have changed which databases and corruptions work and I’ve had to rework some of the databases for you. This is an update that takes into account SQL Server 2008R2 and SQL Server 2012 and sets out everything clearly....(more)

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server - ne of the use-cases for a cloud solution is to serve as a Disaster Recovery option for your on-premises servers. I’ll explain one particular use-case in this entry, specifically using Windows Azure “IaaS” or Virtual Machines as a Recovery Solution for SQL Server (more detail here: http://www.windowsazure.com/en-us/home/features/virtual-machines/). In future installments I’ll explain options for other workloads such as Linux and Windows Servers, SharePoint and other solutions. Some architectures also allow for using Windows Azure SQL Database (Formerly SQL Azure) in recovery scenarios; I’ll cover that separately....(more)

Blogs : Performance and Tuning

An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 3 - Before you start reading this blog post, I strongly suggest you to read the part 1 of this series.It talks about What is Redundant Index. The story is a conversation between two individuals – Jon and Mike. They are different but have single goal learn and explore SQL Server. Their initial conversation sets the ground for this blog post. They earlier discussed what is a Redundant Index as well, discussed what are the special cases for the same. It is a general assumption (or common best practices) is to drop Redundant Indexes....(more)

An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 2 - The story is a conversation between two individuals – Jon and Mike. They are different but have single goal learn and explore SQL Server. Their initial conversation sets the ground for this blog post. They earlier discussed what is a Redundant Index as well, discussed what are the special cases for the same. It is a general assumption (or common best practices) is to drop Redundant Indexes. Later Mike asks for special case where even though the index is clearly a Redundant Index, why it should not be removed. Jon promises to explain with a demo where a Redundant Index is useful and should not be dropped. Here is their conversation continued from earlier....(more)

Blogs : PowerPivot

Parameterising PowerPivot Connection Strings in Excel 2013 - One of the things I’ve always wanted to do with PowerPivot is to parameterise the connections used to import data. Despite PowerPivot’s ability to handle massive data volumes, most of the time you want your end users to import only the data they actually need – and for them to be able to specify filters on the data themselves somehow. The only way to do this in Excel 2010 was to have them go into the PowerPivot model and change the connection themselves, which is not very user-friendly, but now we have a proper PowerPivot object model in 2013 we can modify connection strings in VBA (so we can take filtering information direct from the worksheet) and this post shows how. I’d like to acknowledge the help I got from Kasper’s post here which covers very similar ground, but I came across a few interesting things while building the example here so I thought it was worth a post on its own....(more)

Blogs : Replication

Step Up and Take More Ownership - Regular readers will know and tell you that I’m resolute in my philosophy that the Best DBAs Automate Everything. So when I was presented with an opportunity to dish out a dose of unadulterated #SQLWinning recently, the temptation was just simply too great for me to resist....(more)

Blogs : Reporting Services

SSRS Subscriptions Report - As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions. It was soon found to have a bug with SQL Server 2008 R2 SP2. IN the comments on that post, I promised to post an updated script. Here is that update – without the bug....(more)

Blogs : Security and Auditing

How to keep your ASP.NET database connection strings secure when deploying to Azure from Source - A question came up during the deployment. We moved our database from SQL Compact to a SQL Server instance in Azure. But, how do we keep our database connection strings a secret? We are pushing our source code to GitHub and don't want our connection strings and passwords committed as well. ...(more)

Login Failed, cannot open user default database - Each login in SQL Server has a default database associated with it. When you login to SQL Server context is set to default database for login. This is set when login is created and it can be changed by using ALTER LOGIN command. ...(more)

Blogs : T-SQL

Joins are Commutative and SQL Server Knows it - Takeaway: I explain how SQL Server is aware that inner joins are commutative and so the order of tables in your queries doesn’t matter....(more)

Diversion: Sub-Second SQL Sudoku Solver - I enjoy Sudoku as a way to relax on occasion but, being an IT guy, I suppose I am predictably meta: solving them is fun, but making a machine to solve them is even more fun. I created a clunky solver once before based on the idea of using as few T-SQL statements as possible. This year I decided to try to make a fast solver instead. It’s working fairly well and solves most puzzles in under a second on my laptop....(more)

Blogs : XML, XPATH and XQUERY

Choosing Paths for Selective XML Index – Part 1 - About a month ago, I wrote a series of blog entries on the Selective XML Index introduced in SQL Server 2008 SP1. Just to summarize before I start up again, the Selective XML Index (I’ve abbreviate as SXI) is a feature introduced to allow indexing of only certain paths in an XML column. It uses side-tables using sparse column technology (that was introduced in SQL Server 2008) to effectively index pieces of XML documents while keeping the size of the index relatively low. It is unrelated to the “original” XML index technology (CREATE PRIMARY XML INDEX, etc) that creates large indexes (2-5 times size of the original documents) but indexes every element, attribute, and text node. Either XML indexing technology may be used independently or both may be used together. In addition to the (primary) SXI, you can have “secondary” SXIs, which are simply nonclustered indexes over specific columns in the side table....(more)

Choosing Paths for Selective XML Index – Part 2 – Using the SXI XEvents - In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop in between each example) to see what triggers the XEvents and what the query plan effects are....(more)


Administrative