The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I occasionally scratch my head trying to think of a good technical reason why SQL Azure (sorry, Windows Azure SQL Database) does not support database extended properties. As far as I'm aware, Microsoft currently offers no indication of when, or if, support will appear so, for now, if you're porting databases to SQL Azure, you'll need to remove all existing extended properties.

Peter Larsson provides a short script that will make this process painless, but that's not really the main point. Flawed as they are in some respects, extended properties are simply the best way to document a database, and sometimes the only viable mechanism.

They values of extended properties are stored with the database object metadata (in a base system table for the database) and offer a means to attach the documentation directly to the database itself and its objects, rather than a separate document. The best-known example, and sadly the only one that we can add directly through SSMS, is the MS_Description extended property, in which we store a basic description of each object. However, we can use the sp_addextendedproperty stored procedure to store any property we wish in the metadata, such as revision date, author or, critically, database version number (i.e. the build number).

The beauty of extended properties is that the documentation stays with the database. When we build a database (e.g. from SMO), it extracts the extended properties from the metadata and includes them with the build script. They are also included in database backups.

Extended properties are the only satisfactory way of documenting tables and their components, since table scripts are not preserved within the server. Even more importantly, many deployment tools rely on extended properties as a means to extract from, or attach, a version number to a database in order to ensure it applies the correct set of upgrade scripts. Without them, we have to adopt a different deployment method just for the Microsoft cloud. This might involve separate documentation (in XML of JSON), or storing the build numbers in a dedicated table (not always an option, depending on how strictly controlled is the schema design).

It seems odd to me that Microsoft would introduce such hurdles to deploying databases to their cloud, especially when it's now possible, and relatively cheap, to work with fully-fledged SQL Server databases, in the Amazon cloud, without this sort of inconvenience.

Is there a sound technical reason why SQL Azure can't support extended properties? Or some other reason why Microsoft is reluctant to add support?

Cheers,

Tony.

Tony Davis from SQLServerCentral.com

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

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.

Vendors/3rd Party Products

Upgrading your tSQLt version for SQL Test users - Red Gate's SQL Test tool uses the tSQLt framework internally to enable uniting testing within SSMS. The tSQLt framework is open source and updates more frequently than SQL Test, but there is a way to upgrade the version of tSQLt that a database uses, and still use the integration from SQL Test......(more)

Tech News : General Interest

The Truth About a Failing Startup - "Somehow, I managed to create an app that some people seemed to like. With some odd stroke of luck, an investor in the U.S saw the app, and saw something in me. He gave me a shot. Invested in me and my company. Fast forward a couple of years, and that company still exists, but it’s on it’s last legs. It’s dying. I’ve failed. "...(more)

Tech News : Security

Many companies likely affected by hack of popular iOS developer forum - iPhoneDevSDK confirms the site was compromised and hosted a zero-day exploit that was likely used to launch attacks against Twitter, Facebook, and Apple....(more)

After Hacks, Twitter Looks to Two-Factor Authentication - Security expert Graham Cluley said Twitter, after resetting passwords on some 250,000 possibly compromised accounts earlier this month, has put a help-wanted ad on the Jobs section of the Twitter home page seeking experts specializing in two-factor authentication for log-ins. The move comes after Burger King and Jeep had their Twitter accounts hacked....(more)

Minnesota law enforcement urged to curb database abuses - More than half of Minnesota law enforcement personnel with access to driver's license data might have used the access inappropriately -- looking up friends, family or even themselves, a new report says. ...(more)

Tech News : The Lighter Side

The Fab 40 SharePoint Jokes (Updated) - Global consultants, enthusiasts, coders, and administrators work long hours trying to help clients and friends succeed with SharePoint all around the world. It is this community that I dedicate this list of SharePoint jokes! ...(more)

SQL Server News

February 2013 Cumulative Updates for SQL Server 2008 R2 - Microsoft released two new cumulative updates for SQL Server 2008 R2....(more)

SQL Server 2012 RTM Cumulative Update #6 is available! - The SQL Server team has released CU #6 for SQL Server 2012 RTM. Relevant for builds 11.0.2100 -> 11.0.2400. Do not attempt to install on SQL Server 2012 SP1 (any build >= 11.0.3000) or any previous version....(more)

Microsoft News : General Interest

Why Microsoft’s new Office 2013 license may send users to Google Docs - When your PC dies, so does your Office 2013 license. It's difficult to see the wisdom in this change. It's not a big change, but it's not a nice one, either....(more)

Outlook.com sheds its preview tag, claims 60 million users already - Taking much less time in beta than Gmail, Microsoft's Outlook.com e-mail service has been stripped of its preview label and is now suitable for and open to all....(more)

Hardware News

The new Opteron 6300: Finally Tested! - Is the new Opteron "Abu Dhabi" a few percent faster or is it tangibly faster when running real world code? And are the power consumption gains marginal at best or measureable? Well, most of our benchmarks are real world, so we will find out over the next several pages as we offer our full review of the Opteron 6300....(more)

Blogs : Administration

Keep everyone away from breaking your database object naming convention with SQL Server Policy Based Management. - Maintaining proper naming convention for database objects is very crucial for every database project. Think about a situation where 10-15 database developers work together. If one of them creates a table with a name of Customers_tbl another one Tbl_Department or Salary, that could be quite cumbersome for future maintenance and extensibility....(more)

When will my SQL Server Evaluation Edition expire? - The SQL Server Evaluation Edition has a built in expiry of 6 months from the time that you install it. What happens when your SQL instance does expire? What are the options? Particularly if you've developed a working solution and want to put it into production?...(more)

How Does SQL Server Store Data? - Brent Ozar steps back to take a look at the big picture of SQL Server data storage, for beginners....(more)

Blogs : Analysis Services / BI

Implement SSAS MD cell security using dimension security with blazing performance - SQL Server Analysis Services (SSAS) Multidimensional (MD) is a great product, and in my opinion it’s still the only real option to go for when building complex enterprise BI solutions. It’s still very fast when implemented correctly and it’s mature and therefore very stable. The only real downside is cell security, which, in my opinion, is useless. It makes performance drop dramatically because it evaluates security cell-by-cell....(more)

Fake data science - Books, certificates and graduate degrees in data science are spreading like mushrooms after the rain. Unfortunately, many are just a mirage: some old guys taking advantage of the new paradigm to quickly re-package some very old material (statistics, R programming) with the new label: data science....(more)

Blogs : Computing in the Cloud

Virtualization Poses Security Risks in Public Cloud - Companies moving applications to a public cloud need to be aware of the risks created by using virtual machines in a shared cloud infrastructure....(more)

How to Avoid the Hidden Costs of Cloud Computing - We all know the conventional wisdom about cloud computing: it's cheap, fast and easy. But is it really that much cheaper? Or is it simply optics that make it appear cheaper?...(more)

Blogs : Deep Into Windows

Windows Server 2012 Deduplication is Amazing! - Jeff Guillet describes describes his configuration (unsupported by Microsoft) for using Windows Server data deduplication on an Solid State Drive (SSD) that holds active Hyper-V virtual machines. ...(more)

Blogs : DMO/SMO/Powershell

Get Perfmon data with Powershell - A simple way to check the system performance counters on a remote server....(more)

Calculate Bitmasks In PowerShell - Jason Strate on why you might need to use bitmask values and a simple PowerShell script that can be used to generate them....(more)

Use PowerShell Script via startup Agent Job to balance Memory between two instances on a cluster on a Failover - Scenario: You have a two instance, two node cluster. SQL1 is typically running on Node1, SQL2 is typically running on Node2. The Objective is to maximize memory usage when each instance is running on its own node, yet balance memory usage if both of them happen to run on the same node....(more)

Blogs : Hardware

Stack Exchange’s Colocation Move: 8 Lessons Learned - Moving about 35 servers, something like 2000 pounds of computer hardware 50 blocks, doesn’t seem like that big of a thing. However, in our geek microcosm moving to a new colocation facility was a year long adventure with lessons learned, arguments, designs and redesigns, a hurricane, and many weekends of preparation that somehow resulted in a setup that we are all very proud of....(more)

What’s inside a Microsoft Surface Pro and can it really replace the laptop? - Troy Hunt will do a lot of stuff with the Surface Pro that he would have turned to a laptop to in the past but… he’ll still be using the iPad while kicking back on the couch....(more)

Blogs : High Availability/Disaster Recovery

Useful information on DB mirroring - Database Mirroring is a software solution for increasing database availability. Database mirroring offers a substantial improvement in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. ...(more)

Blogs : Integration Services/ETL

Comparing multiple rows insert vs single row insert with three data load methods - The performance of 'extract, transform, load' (ETL) processes for large quantities of data can always be improved by objective testing and experiment with alternative techniques. The cause of poor performance can sometimes be surprising....(more)

Blogs : NOSQL

My first venture into Hadoop - "If you keep an eye on the tech industry you cannot have failed to notice that an Open Source technology called Hadoop is getting lots of coverage these days." Jamie Thomson describes how he got started....(more)

Data Science Laboratory System - Instrumentation - It is sensible to check the performance of different solutions to data analysis in 'lab' conditions. Measurement by instrumentation makes it easier to develop systems that are efficient....(more)

Cassandra, Hive, and Hadoop: How We Picked Our Analytics Stack - It’s important to think carefully about your data and your technology choices, and sometimes it can be difficult to do that in a data vacuum. Cassandra, Hive, and Hadoop ended up being the right tools for us at this stage, but we only arrived at that conclusion after actually doing live acceptance tests and performance tests....(more)

Blogs : Performance and Tuning

Custom Metric: Low physical/external memory - Determining if your operating system is causing memory issues within your SQL Server instance can be a challenge. One way around it is to use the sys.dm_os_ring_buffers to capture alerts registered there that show when server memory (also known as physical memory) is low. This is especially useful in situations where you’re running SQL Server on a shared environment with some other service or application....(more)

Blogs : Professional Development

Capturing Attention: Writing Great Session Descriptions - A great session description is a short yet compelling story designed for a very specific reader. The reader is the protagonist, his problem is the antagonist, and you are the narrator, helping the reader through his quest for glory. Know your target audience, understand its problems, help solve them, and your session description will be wildly successful....(more)

Multitasking A Ridiculous Workload – How I Do It - Multitasking is a misnomer, people can’t actually do more than one task at a time. What most of us consider to be multitasking is in fact task-switching, the act of switching back and forth from one task to the next. Task-switching is expensive and you could be loosing as much as 40% of your productivity in the process....(more)

Blogs : Reporting Services

RS: Database Engine does not meet edition requirements - This error is a result of mismatched SKU's between the Reporting Server and the Database Engine....(more)

Blogs : Software Development

Well, Actually - As we mature as developers, finding logic errors and incomplete solutions becomes our way of life. It defines us. But our engineering strength is also our social weakness. Countless times as engineers you will find yourself interrupting someone ...to correct a false assumption, provide an extra fact...You can identify this behavior because the person interrupting usually starts with the phrase "Well, actually...". ...(more)

DevOps does not equal “Developers managing Production” - If a start-up or smaller development house tells you "yes, we work in a DevOps model" what they really mean is "We pretty much have no Operations capability at all, and we rely on the Developers to build, deploy and manage all of the environments from Development to Test to Production. Mostly by hand. Badly"....(more)

Blogs : T-SQL

Named constraints: two sides to the story! - Arvind Shyamsundar cautions against use of named constraints for temporary tables....(more)

Script Out Foreign Keys With Multiple Keys - It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys. But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?...(more)

Startup Expression Predicates - In this post, we'll walk through a simple parent/child example that exploits a partially denormalized table schema to improve join performance to the child tables. The performance improvement comes through SQL Server producing query plans that contain Startup Expression Predicates, which effectively prevents certain parts of the query plan from executing in some cases....(more)

7 Things Developers Should Know About SQL Server - "Hi. I’m a former developer who’s moved into database administration, and here’s what I wish somebody would have told me when I got started."...(more)

Rolling Averages in SQL Server - Aggregate functions are convenient – they solve a business need and they make development easy. Unfortunately, not all business requirements are so easy to solve. Let’s look at one example: rolling averages....(more)

Inline UDFs do not prevent parallel execution plans - Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our queries are wrapped in inline UDFs or copied and pasted into the main query. ...(more)

SQL Swiss Army Knife Series is indexed - Pedro Azevedo Lopes creates a central location for all past and future posts of the “SQL Swiss Army Knife” series....(more)

When DBCC OpenTran doesn’t list all open transactions - DBCC OpenTran shows the open transactions by reading the Transaction log and gets the open transactions from it. If the transaction is not listed in the Transaction Log it will not be listed by DBCC OpenTran. ...(more)