In this issue:

Vendors/3rd Party Products

Tech News : General Interest

SQL Server News

Microsoft News : General Interest

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : Deep Into Windows

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Hardware

Blogs : High Availability/Disaster Recovery

Blogs : Performance and Tuning

Blogs : Security and Auditing

Blogs : SQL Server 2012

Blogs : SQL Server 2014

Blogs : T-SQL

Blogs : Virtualization

Articles

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-06-10

SQL Backup Pro New! Free Transaction Log Management eBook
Get your copy for tips on managing log growth, transaction log backups and more. Then try SQL Backup Pro to easily set-up regular log backups. Get your free resources now.
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.
Editorial - When Windows don’t provide illumination

The other day I was staring a SQL Server Management Studio and saw the impossible. There, in front of me, was a table build diagram showing that a table had two primary keys.

I had right-clicked on the table and then clicked 'design'. I stared at it in disbelief for several seconds until I realized that the view was trying to show me that there was just one primary key consisting of two columns, but the 'simple' design view couldn't do it. I don't usually go anywhere near these GUI fripperies inherited from MS Access, so I doubt if any of you would have been fooled as easily. However, this thought remained with me: are we inclined to want to make things harder for developers with this sort of cosseting? Do developers really need protection from a simple, rational CREATE TABLE statement? No. When they need to construct a class in C#, they don't heave out a prosthetic GUI to do it. They just cut code. What's so difficult about DDL code?

Microsoft, unlike Oracle or Sybase, has had a strange cultural aversion to SQL. Although ODBC is SQL-based, the Office tools have always been quick to protect us from going near it. MSQuery in Excel and the Access GUI provide elaborate tools to shield us from SQL. SSIS too makes simple SQL elaborate and complicated, though, I admit, splendidly diagrammatic. When we ask SMO/SSMS to generate CREATE TABLE statements, we get a scary mumbo-jumbo full of angle brackets and elaborate syntax that is unnecessary because it repeats the defaults. We get weird computer-generated names for constraints for which we don't want to provide explicit names. We get an awkward and unnatural syntax for foreign key constraints, separating them from the column to which they refer. And so on. This is a language dialect created by machines for machines.

Microsoft have somehow made the whole business of creating and altering tables, and their relationships, more difficult than it would be if one stuck to the standard SQL Syntax, but with logically-consistent additions for SQL Server specific extensions. What must change? We need a way to reverse-engineer rational human-oriented CREATE TABLE statements, and we need proper syntax diagrams on MSDN, rather than the rash of brackets we see there now, enough to put off the most resolute of code-cutters. Might railroad style diagrams help?

Phil Factor

» 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.


Vendors/3rd Party Products

New Custom Metric for SQL Monitor: Percentage of Blocked Connections - Most well-balanced SQL Servers will have some degree of blocking. This metric attempts to measure the impact of lead blocking queries against other queries. Higher values indicate that many connections are being blocked, and queries should be tuned to reduce the amount of contention....(more)

Tech News : General Interest

IT departments won't exist in five years - Consumerization of IT and self-service trends will lead to a restructuring of the today's IT shop, leaving behind a hybrid model consisting of tech consultants and integrators....(more)

Amazon's RDS database finally generally available - Users can also get a service-level agreement that comes with a 99.95 percent availability promise if they run the database in multiple places...(more)

Cloud Computing Rains Billion-Dollar Deals - The popularity of powerful smartphones and tablet computers has driven the demand for services that can be reached from any Internet-connected device. The phenomenon has helped propel cloud computing, and driven lucrative deals in the space. Questions are already being raised whether the buyers are getting overzealous and paying too much....(more)

SQL Server News

SQL Server 2012 Service Pack 1 Cumulative Update #4 is available! - The SQL Server team has released CU #4 for Service Pack 1. KB article: KB #2833645, Build # is 11.0.3368. This build has 39 fixes by my count. ...(more)

Microsoft News : General Interest

Microsoft overhauls pricing for Azure Web services - Institutes per-minute charges for Azure services and no longer charges for stopped virtual machine...(more)

Blogs : Administration

Database Lifecycle Management (DLM) - Database lifecycle management (DLM) is a policy-based approach to managing databases and data assets....(more)

Document more trace flags? - With so many undocumented trace flags, the question frequently arises “Why aren’t MORE trace flags documented?” Kalen Delaney explains....(more)

File Layout Viewer vs. Drop Clustered Index - When you create a clustered index on a heap, the data from the heap is rewritten and ordered, and the upper levels of the index are added. If you drop that clustered index, what happens?...(more)

Get a list of all your database files - A quick, easy way to get a list of all the database files on a SQL Server instance....(more)

Automating SQL Server Database Deployments: Scripting Details - To wrap up the series on Database Deployment Challenges, Alexander takes a dive into the details of how he scripted a solution and comes up with several practical tips for getting the most out of any automated database deployment framework....(more)

SQL Server Agent Does Not Start After Uninstalling/Reinstalling SQL Server 2012 - A tip to avoid problems with Agent if you happen to uninstall and then reinstall SQL Server 2012 on the same machine....(more)

Blogs : Analysis Services / BI

SQL Server Analysis Services Multidimensional Model – Merging Partitions with Slice - Even if you meet all the requirements for merging and you have no plan to reprocess the data after merge, you can still run into some unexpected situations. ...(more)

Analysis Services Multidimensional Now Works With Power View–And Why That’s Important - In SQL Server 2012 SP1 CU4, Analysis Services Multidimensional can now be queried in DAX, and this plus some tweaks to Power View mean that the two can be used together for the first time. ...(more)

Blogs : Backup and Recovery

The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO - Paul Randal starts a series of six posts helping you understand one of the most important areas of responsibility for people looking after SQL Server: ensuring there are sufficient backups to allow recovery if a disaster occurs....(more)

Blogs : Computing in the Cloud

Access SQL Azure from a Node.js app deployed to Windows Azure Web Sites - How to access SQL Azure data from an Express.js Node.js application deployed to Windows Azure Web Sites, and expose that data over HTTP in 31 lines of Node.js and T-SQL code. ...(more)

Cloud Computing Architecture Patterns: Don’t Focus on the Client - Normally Buck Woody tries to put topics in the positive in other words "Do this" not "Don't do that". Sometimes its clearer to focus on what *not* to do. Popular development processes often start with screen mockups, or user input descriptions. In a scale-out pattern like Cloud Computing on Windows Azure, that's the wrong place to start....(more)

Why I Dislike EC2 - Amazon mazon is doing a great job, especially compared to other cloud providers...but...every synthetic and real-world benchmark that we've run is consistent with what virtually everyone else has observed. EC2's price-to-performance ratio is horrible. ...(more)

How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS) - Bob Dorr dives into the details to help you understand your Windows Azure IaaS, SQL Server deployment capabilities and options....(more)

Microsoft finds a new way to deliver a private cloud in a box - With the newly announced Windows Azure Pack, Microsoft may have found a way to deliver its long-promised private cloud in a box....(more)

Performance Guidance for SQL Server in Windows Azure Virtual Machines - Key factors to consider when evaluating performance and planning a migration to SQL Server in Windows Azure Virtual Machines....(more)

Enhancements to SQL Server Backup to Cloud in SQL Server 2012 SP1 CU4 - Cumulative Update 4 for SQL Server 2012 Service Pack 1 includes enhancements to improve the SQL Server Backup and Restore to Cloud user experience. The enhancements include performance improvements, cleaning up invalid blobs when backups fail or are interrupted, and PowerShell support. ...(more)

Blogs : Deep Into Windows

Analyzing BSOD Minidump Files Using Windbg - Mark S. Rasmussen shows how analyzing BSOD minidump files using Windbg will enable you to find the cause of the BSOD after the fact....(more)

Blogs : DMO/SMO/Powershell

Microsoft announces PowerShell v4, DSC - In particular, a new feature called Desired State Configuration promises to become the foundation for some pretty serious expansion. Essentially, DSC lets administrators write a declarative “script” that describes what a computer should look like....(more)

Blogs : Events

Microsoft TechEd 2013: Visual Studio 2013, TFS 2013 Bust Out - Microsoft made bold moves in announcing major new versions of its dev tools, Visual Studio 2013 and Team Foundation Server 2013, at its TechEd 2013 show today....(more)

TechEd 2013 - Day 1 Summary - Announcements on SQL Server 2014, Windows Azure BizTalk Services (WABS) and more....(more)

Blogs : Hardware

Servers Need Expiration Dates - Servers are like milk cartons: both need an expiration date printed clearly on the outside....(more)

Blogs : High Availability/Disaster Recovery

AlwaysOn Availability Groups Troubleshooting and Monitoring Guide published! - AlwaysOn Availability Groups Troubleshooting and Monitoring Guide provides information on troubleshooting the common AlwaysOn Availability Groups issues and monitoring the availability group health....(more)

Blogs : Performance and Tuning

How To: Troubleshooting SQL Server I/O bottlenecks - One of the most common reason for server performance issues with respect to SQL Server is the presence of an I/O bottleneck on the system. ...(more)

Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR [video] - Adam Machanic's TechEd session shows you when and how to use powerful SQLCLR capabilities to speed up various types of analytical queries, do much faster XML processing, and build smart caches that will supercharge your user-defined functions....(more)

Blogs : Security and Auditing

Hardening a SQL Server - Top of the list: "Secure sa account with a strong password."...(more)

Blogs : SQL Server 2012

SQL Server 2012 System views poster available for download - Microsoft has made available for download the SQL Server 2012 System Views Maps....(more)

Blogs : SQL Server 2014

My 2 cents on SQL Server 2014 - Klaus Aschenbrenner focuses on improvements in the area of High Availability, Performance, and Scalability....(more)

SQL Server 2014: Unlocking Real-Time Insights - "Designed and developed with our cloud-first principles in mind, it delivers built-in in-memory capabilities, new hybrid cloud scenarios and enables even faster data insights."...(more)

What’s New for BI in SQL Server 2014? - Not much. SQL Server 2014 will be a database-focused release with no major changes to the BI "pillars" (SSAS, SSRS, SSIS) and PowerPivot, except perhaps bug fixes. However, you might find some other new additions and enhancements interesting depending on your specific projects....(more)

SQL Server 2014 In-Memory OLTP (“Hekaton”) Whitepaper - Kalen Delaney opens a Q&A on her new Hekaton whitepaper....(more)

(Almost) Everything You Need to Know About SQL Server 2014 - Just when you thought SQL Server couldn’t get better, Microsoft is announcing the features for SQL Server 2014. They haven’t announced the licensing/pricing, but Brent Ozar tells you what he does know so far....(more)

SQL Server 2014 - For an early look at what's coming in SQL Server 2014....(more)

Blogs : T-SQL

Using the Output Clause with T-SQL Merge - The Output clause, first implemented in SQL Server 2005, can be used to return information for each row modified by an Insert, Update, Delete or Merge statement. This functionality greatly increases the power and usefulness of Merge, and is required in the processing of Slowly Changing Dimensions. ...(more)

SQL Server CREATE TABLE syntax diagrams - Many of us have seen, on MSDN, the heading 'Syntax', followed by a rash of nested brackets and keywords, enough to put off the most resolute of code-cutters. However, there is a goldmine of information there, and Phil Factor had an ambition to get at it, and share the gold. The result is this article, full of railroad diagrams...(more)

Blogs : Virtualization

Server 4024 part 1 – Virtualisation - Server 4012 (WS2012 + SQL 2012) is what you want be running to get the most out of your modern hardware and push that through to your database engine....(more)

PowerShell daddy on Windows Server 2012 R2: Cloudy cloud cloud - “How many of you ever paid for a sorting library? Memory managers? TCP stacks? Now you just get it in the operating system. That is the inevitable progression of our industry. So it will be with virtualisation...”...(more)

The Accidental DBA (Day 5 of 30): Virtualization Considerations - Jonathan Kehayias discusses specific considerations for running SQL Server in a virtualized configuration. ...(more)

The Top 10 Things to Know When Running SQL Server Workloads on Windows Azure Virtual Machines - We've seen organizations run everything from simple development and test SQL Server workloads to complex distributed mission critical workloads. Here's a few things we've learned from their experiences....(more)

Articles

Don’t just blindly create those “missing” indexes! - The problem with just blindly creating this index is that SQL Server has decided that it is useful for a particular query (or handful of queries), but completely and unilaterally ignores the rest of the workload. ...(more)


Administrative