In this issue:

Tech News : General Interest

Tech News : Security

Microsoft News : Security

Data Visualisation

Blogs : Administration

Blogs : Backup and Recovery

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : Data Access / ORMs

Blogs : Data Mining

Blogs : DMO/SMO/Powershell

Blogs : Hardware

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : SQL Server 2012

Blogs : SQL Server Compact Edition

Blogs : T-SQL

Articles : Administration

Articles : Development

Articles : Testing

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

Editorial - NoSQL: Are you ready to compromise with security

I was interested to read a post from Trustwave Spiderlabs, dated March 13, called MongoDB –Security weaknesses in a typical NoSQL database. The discovered problems were fundamental and concerned various authentication issues, vulnerability to SQL Injection, and others.

Skip back two years to the report reviewing MongoDB and Cassandra, Security Issues in NoSQL Databases, in the proceedings of the 2011 International Joint Conference of IEEE TrustCom-11/IEEE ICESS-11/FCST-11, and we find the following conclusion:

"The lack of encryption support for the data files, weak authentication both between the client and the servers and between server members, very simple authorization without support for RBAC or fine-grained authorization, and vulnerability to SQL Injection and Denial of Service attacks"

It is perhaps surprising to find, two years on, MongoDB still has these same vulnerabilities.

When the NoSQL movement appeared, apparently out of nowhere, its evangelists claimed that the relational database was as doomed: the future lay with NoSQL. We wondered why it was that relational systems struggled with huge multi-terabyte databases, whereas NoSQL could scale out infinitely. The truth is that NoSQL disposes of many of those comforts we take for granted in commercial IT. As well as the obvious compromise in 'transactionality', the ability to maintain ACID transactions when changing data, it seems that there is a second one, security. If security were a high priority to MongoDB, the elementary loopholes such as requiring no credentials on initial install, having read/write access without granularity and both transmitting and storing data unencrypted would now be fixed.

Transactionality, security and data integrity comes at a considerable cost. The technology is dauntingly complex. Security 'defence in depth' doesn't come cheap. If you don't need these features, then of course you possibly don't need a relational database. NoSQL proponents will suggest that we achieve security by securing everything around it, doing data integrity in the application and relying on eventual consistency. Sure, or you might as well just write your own database too, an idea that has struck down an alarmingly large number of developers.

Before adopting NoSQL for a commercial application that needs consistency and durability, you need to be sure that it can pass your security audit, and comprehensive stress testing. In other words, you need to be satisfied that the product actually provides all those niceties that the user of relational database systems take for granted. If you find you have to put development effort into adding those features, the cost of a relational system will begin to look like money well spent.

» 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

Embrace, extend, extinguish: How Google crushed and abandoned the RSS industry - Most of the commentary I've read so far about the loss of Google Reader has been about its use as an RSS client. But that's a red herring. The real victims were companies that had planned in 2005 and 2006 to build RSS sync engines. Google stomped them out of business like Godzilla sweeping through Tokyo...(more)

Inexact Design: Beyond Fault-Tolerance - You can get dramatic energy savings by slightly relaxing the requirement that a computer be 100% correct. Further, he says there are many applications where some errors are completely acceptable and even unnoticeable by users....(more)

Steve Jobs manga portrays the man as a dreamy, drug-fueled genius that Japanese girls could love - Steve Jobs sets out to tell the tale of the mercurial genius' life in broad, emotionally charged strokes. The manga's final panel shows Jobs' first meeting with a shaggy-haired, bearded Steve Wozniak, with the Isaacson narration elaborating "And that was how the two Steves met;" no further explanation of the Apple co-founder's identity is given. And maybe that's okay...(more)

What’s Next: We're building an RSS reader from scratch - But after Google’s announcement last week, and Reader’s imminent shutdown, we think it’s the right thing to do. It’s certainly the self-interested thing to do, given how much we all relied on Google Reader. Google Reader users (and RSS devotees in general) are rabid information addicts with strong opinions. We’re truly grateful for the input....(more)

Tech News : Security

Exploring Windows Server 2012 Hyper-V Worker Process Security - A VM breakout attack is where a hacker gets into the app/OS of a VM and then tries to break out from that security boundary to get onto the host and/or other VMs. This has not happened to Hyper-V but it has happened to certain other hypervisors but Microsoft wants to take no chances....(more)

The DDoS That Almost Broke the Internet - The problem is the open DNS recursors. The attackers were able to use them to generate more than 300Gbps of traffic likely with a network of their own that only had access 1/100th of that amount of traffic themselves. These mis-configured DNS recursors as a bomb waiting to go off that literally threatens the stability of the Internet itself. ...(more)

Microsoft News : Security

Towards Security as a Service? - There’s some fuss around the additional security risks of moving an existing service within a corporate data center, to a cloud-hosted virtual machine, with a shared infrastructure. I appreciate the caution, but I wonder if they aren’t overstated....(more)

Data Visualisation

38 Tools For Beautiful Data Visualisations - Good visualisation techniques can support statistical treatment of data, or even become an analysis technique. But also, can be used as a communication tool to create diagrams, charts, maps, graphs and plots. Today there are plenty of tools out there that can be used to improve your data visualisation efforts at every level. Below we list a non-exhaustive list....(more)

Blogs : Administration

DBCC CHECKDB performance and computed-column indexes - It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC CHECKDB for a blog post and discovered quite a nasty performance issue that exists in all versions of SQL Server back to SQL Server 2005. This isn’t a bug, it’s just the way things work....(more)

Blogs : Backup and Recovery

But what can I *do* with Windows Azure? Backups - Can Windows Azure backup data, servers, workstations or databases? Yes. Yes it can. Windows Azure storage is replicated three times in one datacenter (on different fault-domains) and then those three are replicated to another geographically separate (but still in the same country region) location, you get six copies of the data automatically. Your data stays in the datacenter you choose, and is replicated within a geo-politically same region. So it’s actually a great target for backups...(more)

Blogs : Big Data

Data Mining and Visualization: Bed Bug Edition - It is clear that the tidy story claiming no bed bug research followed WWII isn’t quite right. Bed bugs weren’t a wildly popular topic, but people still studied them a little bit. The other data organization also paid off, because the graphs visualize when papers on pesticide resistance snuck in...(more)

Are data scientists overpaid? - The final list includes jobs held by people who report relatively low levels of stress (a proxy for how demanding the work is) and who feel their job doesn't necessarily make the world a better place....(more)

Looking Back at Big Data - Making sense of reams of data seems like a uniquely modern problem. Yet historians have been doing it for centuries, reviewing archived sources, constructing analytical frameworks, and fashioning stories and arguments. ...(more)

Relational Databases Aren't Dead. Heck, They're Not Even Sleeping - To listen to the media, you would think that the end of the relational database is about to strike at any moment. Truth be told, the future for relational databases has really never been better — though it's a future that's changing rapidly, altered by the ascendency of big data and non-relational databases...(more)

Blogs : Computing in the Cloud

Execution Plans in Azure SQL Database - Microsoft is doing some really cool stuff out there on Windows Azure SQL Database (WASD), making the portal there functional and useful.Let’s hope that some of this translates down to the core product....(more)

Blogs : Data Access / ORMs

Firing an Entity Framework Database Initializer from within DbContext - One thing about Entity Framework that bugs me are how database initializers work. Database Initializers are used to initialize the database and determine the behavior of how the Entity Framework model interacts with the database...(more)

Blogs : Data Mining

A Rickety Stairway to SQL Server Data Mining, Part 10.1: DDL with DMX - DMX is much simpler than T-SQL or Multidimensional Expressions (MDX), the languages used by relational databases and cubes respectively in SQL Server. That is especially true when it comes to DDL, because there are only two types of objects we can create, mining structures and mining models, and only three DDL statements we can apply to them, DROP, CREATE and ALTER....(more)

A Rickety Stairway to SQL Server Data Mining, Part 10.2: DMX DML - In most SQL-based languages, the four major DML operations to take note of are UPDATE, DELETE, INSERT and SELECT statements. The same is true in DMX, but the functionality of these statements is far more limited. The meaning of the statements also differs in subtle ways. ...(more)

Blogs : DMO/SMO/Powershell

Using PowerShell to deploy Windows Azure Virtual Machines and Windows Azure SQL Databases - I know we can deploy VM’s through the Azure Portal but I prefer an automated approach. Fortunately the new PowerShell cmdlets support Azure VM provisioning and also Azure SQL database provisioning (plus some other nice interfaces). This enables me to quickly spin up a SQL Server VM in Azure or SQL database in Azure. I am actually quite amazed what is possible with PowerShell...(more)

Learn About Using PowerShell Value Binding by Property Name - Value binding by property name in Windows PowerShell pipelines provides a very neat way of passing several parameters at once to a Cmdlet in the pipeline....(more)

Blogs : Hardware

What is the Difference Between Physical Sockets, Physical Cores, and Logical Cores? - There may still be some confusion about how the terms 'sockets' and 'cores' are used and what they mean in relation to SQL Server 2012 hardware selection and SQL Server 2012 licensing considerations. ...(more)

Storage Performance - Most of the key components necessary for a powerful storage system are available with SSDs and the cost is highly favorable for direct placement of data files. Some additional infrastructure elements could greatly enhance the flexibility of storage systems with SSDs....(more)

Blogs : High Availability/Disaster Recovery

Use Powershell to Pick Up what Database Mirroring Leaves Behind - Database mirroring is a step up from log shipping, but is still only a database-level disaster recovery solution. Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server. This is where the DBA needs to create their own custom jobs to script and/or document these types of shortcomings...(more)

Blogs : Integration Services/ETL

Using Event Viewer Logging for SSIS Performance Trouble Shooting - So while working with a client recently I was going some performance tuning on SSIS for them. While SSIS isn’t exactly my normal workload, the problem wasn’t really in SSIS, but just normal SQL Statements being called from SSIS....(more)

SQL Server 2012 Business Intelligence Enhancements - Perhaps the most noteworthy change to SSIS is the new package deployment model, which lets you build, deploy, and execute multiple SSIS packages as an SSIS project. Previously, you had to deploy each package individually....(more)

Blogs : Performance and Tuning

Implicit Conversions and Avoiding Them With Computed Columns - An indexed computed column could be deployed immediately as a temporary fix in order to remove excessive CPU consumption. Addressing the implicit conversion meant that an Index Seek could be used, rather than the expensive Scan operation, and subsequently a more suitable join operation was chosen by optimizer (Nested Loops rather than Merge) for the query....(more)

SQL Server : Part 8 : Explaining The Covering Index or Included Columns - The leaf level of a non clustered index contains only the non clustered index key column and clustered index key (if the table is a clustered table). In order to fetch the remaining column from the clustered index structure or heap structure, SQL server has to do a bookmark/key look up operation.Many time the bookmark or key look up operation might be costly affair....(more)

Blogs : Reporting Services

Reporting service ENCRYPTION KEY automated backup REPORT utility - Reporting Services uses encryption keys to secure credentials and connection information that is stored in a report server database. In Reporting Services, encryption is supported through a combination of public, private, and symmetric keys that are used to protect sensitive data....(more)

Blogs : Security and Auditing

Exploit fixed in MongoDB NoSQL database - The vulnerability allowed an attacker to use the find function in the MongoDB shell to call the native_helper function in the SpiderMonkey JavaScript engine used in MongoDB. The attacker could then manipulate the arguments sent to native_helper function in order to change memory pointers so the malicious code could be executed....(more)

How do I move a SQL login from one server to another without the password? - SQL Server uses hash encryption for its passwords, and can't be “un-hashed”, and turned back to an understandable string. To copy the password intact from one server to another we have to get the “hashed” version of the password and create the login with it, but making sure to tell SQL that it is in fact already hashed and there is no reason to do it again. Fortunately there is a way to do this....(more)

Blogs : SQL Server 2012

Upgrading To SQL 2012: Ten Things You Don’t Want To Miss - It can be a daunting task to put together everything you need in a pre-upgrade checklist. I’ve compiled the top ten items that you need to include in any checklist you put together for migrating your database server to SQL Server 2012. Including these ten items is likely to help you avoid 95% of any potential upgrade issues....(more)

Blogs : SQL Server Compact Edition

Standalone 3.0 for SQL Server Compact 4.0 - This is a Standalone version of (most of) the same functionality as the add-in, for SQL Server Compact 4.0. (Let me know if you miss anything) Useful for anyone not having Visual Studio Professional or higher installed. Requires .NET 4.0 and the SQL Server Compact 4.0 runtime...(more)

Blogs : T-SQL

SQL Server JSON to Table and Table to JSON - how do you read a JSON string as a table, and the how do you produce a JSON document from a table. In this blog, I’ll take the whole process around the circle. ...(more)

Articles : Administration

Managing the SQL Server Transaction Log: Dealing with Explosive Log Growth - You've just become responsible for a database, only to find that the log file is growing out of control. Why is it happening and what do you do to correct it?...(more)

Diagnosing Common Database Ails - When a database starts showing signs of an illness, it's up to the DBA to get to the root of the problem, fast. Kat Hicks takes a look at the most common causes of database troubles, free tools that can help, and the misconceptions that get in the way. ...(more)

Azure SQL Database Maintenance - It is increasingly likely that DBAs are now given responsibility for maintaining Azure SQL databases as well as conventional SQL Server databases. What is likely to be required by way of maintenence? What are the differences? ...(more)

Articles : Development

Solving Complex T-SQL Problems, Step-By-Step - What should you do if your first, most intuitive solution to a problem ends up scanning the data more than is necessary, resulting in poor performance? Have you missed a new SQL Server feature that can remove inefficiency from your technique? Alternatively, do you need a little help, and some lateral thinking, to open the path to a different approach? Sometimes, the answer is "both"....(more)

Experimenting with Online Backups - While there are plenty of services out there, finding one that provides the right features at the right price is anything but trivial....(more)

Articles : Testing

Software Testing and Checking Refined - There are three kinds of checking, Human checking is an attempted checking process wherein humans collect the observations and apply the rules without the mediation of tools, Machine checking is a checking process wherein tools collect the observations and apply the rules without the mediation of humans, and Human/machine checking is an attempted checking process wherein both humans and tools interact to collect the observations and apply the rules. ...(more)


Administrative