In this issue:

Tech News : General Interest

Tech News : Security

Microsoft News : Patches, Bugs

Blogs : Administration

Blogs : Backup and Recovery

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : Data Access / ORMs

Blogs : Database Design, Theory and Development

Blogs : DMO/SMO/Powershell

Blogs : Excel

Blogs : FILESTREAM Data

Blogs : High Availability/Disaster Recovery

Blogs : Performance and Tuning

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL Server 2000

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-29

SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now
Editorial - BACPACs be gone: Managing real backups in Azure

Until recently, the killer reason why DBAs were reluctant to consider using Windows Azure SQL Databases for Azure applications was that there was no built-in way of backing them up. One can sympathize. Microsoft initially laughed off the fuddy-duddy idea of backing up such a robust platform. They soon relented, and now offer backups, of a sort, via BACPACs, saved in your Azure Storage Account. BACPACs have their uses, of course, though backup isn't an obvious one, and certainly not what most DBAs recognize as a proper database backup. It's not a page-by-page copy of the database; it is a zipped package consisting of the schema and a JSON form of the data. The way to ensure transactional consistency is to 'quiesce' the database before taking the backup.

Now, we are no longer obliged to use Windows Azure SQL Databases for a cloud application. We have the choice of using real SQL Server in a VM within Azure, and backing it up properly. SQL Server 2012 SP1 CU2 enhances SQL Server native backup to make it simple to back up to Windows Azure storage. However, we still have to face the obstacle of managing files in the Azure cloud, since this backup destination takes DBAs into what they see as the murky world of Azure containers and blobs.

Murky, perhaps, but Cerebrata (now owned by Red Gate) provide a free tool called Azure Explorer, which might be the one that removes some of the murkiness, and much of the DBAs nervousness, around managing Cloud backups. Certainly, Phil Factor seems to agree that it removes a lot of the pain.

It is simply, as its name suggests, Windows Explorer for Azure. Having set up a Windows Azure Storage account, you simply "connect it" to Azure Explorer. When you perform a backup to Azure Data Storage (ADS), or when any process copies backup to ADS, the host container appears in Explorer as the root directory, containing each of the backup files (blobs). It makes it very easy to move files around, copy them locally, or perform restores direct from ADS.

While backup to the Cloud may be useful in terms of optimizing storage across the enterprise, for DBAs it remains essential that they retain fast, easy, access to the backup files they might need, for whatever reason. I'd encourage any DBAs currently using or evaluating ADS to try it. Does it solve some of the potential difficulties? What are the others?

Cheers,
Tony.

» 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

Twitter Should Shut Me Down - Someone who created a system to attract twitter followers by programatically favourite-ing their tweets. It was very effective, but is it the equivalent of SPAM?...(more)

The rise and fall of AMD: A company on the ropes - The conclusion of a two-part series on AMD. Part one covered AMD's attempts to transform itself from a second-source supplier of Intel designs into a chipmaker powerhouse in its own right. The Athlon 64 was AMD's high point, and it's been largely downhill since then....(more)

Tech News : Security

What Happened When One Man Pinged the Whole Internet - A home science experiment that probed billions of Internet devices reveals that thousands of industrial and business systems offer remote access to anyone. ...(more)

Twitter Terror Hoax Rocks Wall Street - Tuesday, a 1:07 p.m. ET tweet from the Associated Press exclaimed "Breaking: Two Explosions in the White House and Barack Obama is injured." It was a hoax, but within seconds, Wall Street was in panic mode, sending the Dow Jones industrial average into free fall and erasing nearly $200 billion off the broader market's value. ...(more)

Boston Bomber Database Error Underscores Terrorism Watch List Weaknesses - A clerical error thwarted the FBI from discovering that now-deceased Boston bombing suspect Tamerlan Tsarnaev had traveled to Russian last year for six months. It underscores broader problems that have persisted in federal counterterrorism databases since 9/11....(more)

Microsoft News : Patches, Bugs

Microsoft re-releases botched patch as KB 2840149, but problems remain - The saga of botched patch MS13-036 takes new twists and turns -- including a problem with Multiple Master fonts...(more)

Blogs : Administration

How to create a Deadlocked Scheduler Scenario at will - A practical demonstration of how Deadlock scheduler issues arise....(more)

Database Administration KPIs - As a DBA, we may all have this question: How should my work be evaluated objectively? or in another way, what criteria would I hope to be used by others (my manager / clients / peers etc) to evaluate my work?...(more)

5 SQL Server White Papers Every DBA Should Read - Microsoft whitepapers that Jason Strate feels should be read by every DBA, and anyone that has a hand in managing or administering a SQL Server environment. ...(more)

'Cannot resolve the collation conflict' error message with temp tables - Why you need to specify the collation in the CREATE TABLE call for the temporary table, if your database happens to use an explicit collation....(more)

File in use by another process - "I've seen people resort to a reboot because they can't figure out what process has their grubby finger on the file but that is overkill. All you really need is the free utility "Process Explorer" from Microsoft/Sysinternals."...(more)

TSQL to identify databases with high number of VLFs - A highly fragmented Transaction log, with a high number VLFs, can have a significant impact on the performance of certain database operations. ...(more)

Monitoring SQL Server Disk Space - Some DBAs are fortunate enough to have a centralized solution in place to monitor the health of the servers they manage. Many of us rely on home-grown scripts...the solution provided in this tip uses a combination of native tools to provide a robust method for monitoring local disk space on a SQL Server instance....(more)

Blogs : Backup and Recovery

SQL backup mirroring (and splitting) - Did you know that you can mirror your SQL backups? This is good for those times when you need multiple backup copies. Like one local copy and another one on a network share. You can even split backups over multiple files. If you have an IO bottleneck during backup, or if you are low on disk space, this backup splitting feature will come in handy. ...(more)

Blogs : Big Data

Do You Have A Big Data Problem? - "Somewhere along the line these past two decades we’ve lost the ability to educate people with basic analytical skills. Today we have a plethora of self-service BI tools and a dearth of people who know how to use them properly."...(more)

Five Big Data Features in SQL Server - A brief overview of five important "big data" technologies in SQL Server: Column Store Indexes, Hadoop Connectors, Full Text Search, Windows Azure SQL Federation/ Distributed Partition Views and Map Reduce Integration / Polybase...(more)

Blogs : Computing in the Cloud

Access Azure Blob Stores from HDInsight - One of the great enhancements in Microsoft's HDInsight distribution of Hadoop is the ability to store and access Hadoop data on an Azure Blob Store. We do this via the HDFS API extension called Azure Storage Vault (ASV). This allows you to persist data even after you spin down an HDInsight cluster and to make that data available across multiple programs or clusters from persistent storage....(more)

Azure Explorer: Cause for Cerebration - Phil Factor on a new free tool that takes the pain out of managing data and backup files in Azure....(more)

How to backup a SQL 2012 database directly to Windows Azure - A wlakthruogh of the new feature in SQL 2012 SP1 CU2 which allows you to natively backup/restore databases with Windows Azure Storage. ...(more)

SQL Server in Windows Azure Infrastructure Services – Updated Documentation and Best Practices for GA, Upcoming Blogs - Updated technical documentation for deploying and running SQL Server in Windows Azure Infrastructures Services is now available online...(more)

Restoring Azure SQL Database to a Local Server - Jason Strate on restoring an Azure SQL Database backup to a local SQL Server instance, for example, to do some development work on a Azure SQL Database while offline....(more)

Windows Azure: General Availability of Infrastructure as a Service (IaaS) - Scott Guthrie on the release of Infrastructure as a Service (IaaS) support for Windows Azure – including new Virtual Machine and Virtual Network capabilities....(more)

The Expense That Cloud Customers Often Forget - A move to the cloud is typically a lengthy and detailed discussion that covers a variety of key areas, including cost analyses, infrastructure decisions, security risks, and a host of other elements. But channel partners tend to agree that one very important aspect tends to slip through the cracks...the cost of cloud bandwidth...(more)

Blogs : Data Access / ORMs

The two top performance problems caused by ORM tools - Markus Winand describes two common problems associated with ORMs:P the infamous "N+1 selects" problem, and the the hardly-known "Index-Only Scan blocker"....(more)

Blogs : Database Design, Theory and Development

Who’s afraid of the big bad data type - Data types seem so innocuous, but mistakes in their use can creep up on you like a flesh-eating zombie and destroy your project just when you least expect it....(more)

Blogs : DMO/SMO/Powershell

Compare AD Group Memberships with Powershell - A quick Powershell script to check AD Group Memberships between two user accounts. Just set the $user1 and $user2 variables and you’re good to go....(more)

Windows PowerShell 3.0 and Server Manager Quick Reference Guides - Some short-form Quick Reference (also called "cheat sheet") guides for providing tips, shortcuts, common operations, limitations, and proper syntax for using Windows PowerShell 3.0 and Server Manager in Windows Server 2012. ...(more)

Blogs : Excel

Using Excel for business intelligence - Microsoft has placed increased emphasis on business intelligence (BI) functionality in its latest Office suite upgrade. But be warned, it isn't free on Office 2013, so some SMBs might be better to stick with 2010. ...(more)

Blogs : FILESTREAM Data

Adding Data to Filestream with T-SQL - Steve Jones looks at the table side of Filestream and how you get your binary data into the database....(more)

Blogs : High Availability/Disaster Recovery

SQL Server 2012 AlwaysOn – Part 12 – Performance Aspects and Performance Monitoring II - In the synchronous availability mode we delay the commit acknowledge to the application...database locks on the rows changed are held until acknowledgement come back. This can throttle concurrency in a serious manner and with that the volume of business transactions processed....(more)

How It Works: Always On–When Is My Secondary Failover Ready? - The easy answer: Only when the secondary is marked SYNCHRONIZED. - End of blog right? – not quite!...(more)

Answers to Common High Availability and Disaster Recovery Questions - Some interesting follow-up quesitons and answers from Brent Ozar's recent webcast on HA & DR basics for DBAs....(more)

Blogs : Performance and Tuning

Introduction to SQL Server Filtered Indexes - SQL Server filtered indexes can save space and improve performance if they are used properly. Under what circumstances can they be used? When are they most effective, and what sort of performance gain or space-saving is likely? How does a filtered index affect the choice of execution plan? Seth explores these questions with practical experiments...(more)

How filtered indexes could be a much more powerful feature - Aaron Bertrand points out a few areas where filtered indexes could be improved and make them much more useful and practical to a large portion of workloads out there....(more)

Identifying the cause of SQL Server IO bottlenecks using XPerf - How Xperf (Windows Performance Toolkit) can help troubleshoot slow or stalled IO. With the Drivers and FLT_IO_* kernel flags, we can see how much time is spent in the different levels of the IO stack ...(more)

Missing index suggestion – Duplicate Index - The missing index feature, while tuning or checking estimated and actual plans, is very helpful. Something that is key in that sentence is the word, helpful. Don’t run out and simply create all of them. Make sure it is a viable solution that does not implement the three major faults in index strategies: duplicates, overlapping and misalignment....(more)

Blogs : Reporting Services

Refreshing an SSRS Snapshot Using T-SQL - Melissa Coates (aka SQL Chick) with a quick tip about using T-SQL in an Agent Job to refresh a SQL Server Reporting Services snapshot report, rather than a schedule....(more)

Blogs : Security and Auditing

Does TDE really work on MDF files? - Armed with a Hex Editor, Steve Jones demonstrates how TDE protects your data....(more)

Blogs : Software Development

How Software Developers Really Spend Their Time - Mostly waiting. And you thought being a software engineer was all about dreaming up clever algorithms or amazing graphics routines and then instantiating them in elegant, tightly written code. Shows what you know....(more)

What Does the Agile Manifesto Mean? - Scott Ocamb on the key tenets of the Agile manifesto and what they mean to him. ...(more)

Blogs : SQL Server 2000

More businesses than you think are still using SQL Server 2000 - 44% of repsondents to Paul Randal's survey still have SQL Server 2000 in production. How will this look in a year's time though, given that Extended Lifecycle Support ended for SQL Server 2000 on April 9th, 2013....(more)


Administrative