In this issue:

Vendors/3rd Party Products

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Career

Blogs : Database Design, Theory and Development

Blogs : DMO/SMO/Powershell

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Security and Auditing

Blogs : Software Development

Blogs : Virtualization

Articles

Articles : Development

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

SQL Server Replication Get to grips with SQL Server replication
In this new eBook Sebastian Meine gives a hands-on introduction to SQL Server replication, including implementation and security. Download free ebook now.
SQL Developer Bundle 12 must-have SQL Server tools
The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.
SQL DBA Bundle The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.
Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.
Editorial - Cloud Storage. Make it the present instead of the future.

A cloud service should be a utility just like fresh water, electricity, gas or sewage disposal. In IT, we can nowadays take the same attitude to internet provision: we can change suppliers pretty easily, and what we get doesn’t really vary much. One shouldn’t need to be conscious of it, particularly, beyond having to pay for it. We don’t have to buy a special type electric fire to suit our current electricity supplier. Mercifully, water from one tap is very like water from another.

Current Cloud storage providers just don’t seem to understand this, and until they do it won’t be the universally-accepted way of dealing with information. Nowadays, we expect things to be there and work, whatever device we’re using. I’m used to dropping a modern network server such as the Synology server into a network and instantly having all the basic services there, managed from a single console. I can see, access, create and delete the files I’ve stored there from Linux, Windows, Mac, ipad, iPhone or Android. There’s a website, a scheduled download service, music streamer, photo viewer, email server, a domain server, database, scheduled backup, and a host of other services. Compared with what one can do with this, AMS and Azure are back in the pioneering days when you had to hack code to do stuff.

For a start, I need my Azure BLOB storage to appear just like any other remote drive. I want to be able to access files and data I put on Azure BLOB storage from my other silvery gadgets, such as camera, phone, tablet, or whatever. It’s been done so it can’t be that hard. If I want to change my service provider, or have two, I’d like to copy files between my accounts on different providers’ storage. I’d like to make files or sites available, but only to people I choose. I want to schedule the transfer of information from a URL to my cloud storage.  One could go on and on. The odd thing is that this isn’t difficult, particularly for a company like Microsoft that owns a widely-used operating system.

So, instead of crowing that the Cloud is the Future, why not make it the Present?

Are there other essential features of cloud storage that are lacking? I'd be interested to hear about them.

» 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 SQL Monitor Metric: Principals with sysadmin login - This metric counts the number of principals who are members of the sysadmin fixed server role. SQL Server relies on role-based security to manage permissions. If multiple IT system administrators have permissions to set up new SQL Server logins, they might be inclined to do so as part of the sysadmin role. Adding a normal user to the sysadmin role could pose a security risk and is not recommended unless the principal is highly trusted....(more)

Blogs : Administration

Limiting error log file size in SQL Server 2012 - You can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight. You can now in SQL Server 2012, set the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. ...(more)

Inside the Storage Engine: How are allocation unit IDs calculated? - Every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page. When DBCC PAGE dumps a page header’s contents, it does the necessary calculations and metadata look-ups to be able to tell you the allocation unit ID, partition ID, relational object ID, and relational index ID. ...(more)

System Availability Charts in SSRS - It is not very intuitive to use the traditional bar charts or line charts to depict the precise idea of system availability (up/down/degraded) in the last 24 hours. A more user-friendly approach will be a color coded view of system availability timeline. After some researching and tweaking, I have come up with a fairly easy solution to create this type of chart in SSRS. ...(more)

IO Resource Governance in SQL Server 2014 - Resource Governor is intended to ensure predictable performance in a multi-tenant environment, to support mixed workload patterns, to provide mechanisms and tools to deal with runaway queries, and to enforce resource limits for CPU and memory. It enables customers to implement database consolidation or to configure their own database as a service. In SQL Server 2014, we added support for IO resource governance....(more)

Crime Scene Investigation: SQL Server - As we progress through our DBA careers we learn valuable lessons. Sometimes, with a project deadline looming and pressure mounting, we choose to forget them. I was close to giving into the temptation to throw more hardware at the problem. I’m pleased at least that I resisted....(more)

Script to Set the SQL Server Database Default Schema For All Users - What if you have hundreds of users, across all of your user databases, that should all have the same default schema, but haven't? Identify whether you have users with the wrong default schema, and fix them with this script...(more)

All about RollbackSnapshotTempDB - if your backup utility’s purpose is to generate system-wide backups to protect from disaster scenarios, by capturing a complete system image as it was at the time the backup was taken, it is unlikely that the activation of ‘autorecovered snapshot’ makes sense as far as SQL Server is concerned....(more)

Corrupting Databases on Purpose Using the OrcaMDF Corruptor - To hone your skills in recovering corrupt SQL Server databases, you need corrupt databases. Corruptor does more or less what the name says – it corrupts database files on purpose by simply overwriting random pages completely with all zeros. Depending on what pages are hit, this can be quite fatal....(more)

Blogs : Analysis Services / BI

Tabular model: Not ready for prime time? - The tabular model makes sense for proof-of-concepts or small projects. It can also be used for simple models requiring the absolute best performance for the user experience. Right now I can’t recommend it for anything else...(more)

Blogs : Backup and Recovery

SQL Server Corruption Recovery – When All Else Fails - If database pages are hit by corruption, nothing will get that data back, unless you have a backup. However, if you're hit with metadata corruption, and/or a database that won't come online, this may be a viable solution....(more)

SQL Server Backup Compression - time for an overhaul? - SQL Server version 2008 added backup compression for Enterprise Edition only, with a single compression algorithm that values CPU efficiency over the degree of compression achieved. Now this low compression is becoming a significant liability. All the third party backup compression products offer multiple levels of compression via open source algorithms and it is time for SQL Server to follow....(more)

Blogs : Career

How to Write a Killer SQL Server Job Description - The secret to getting employees that don’t suck: Write a job advertisement that doesn’t suck....(more)

Blogs : Database Design, Theory and Development

Document Your SQL Server Databases with Extended Properties - There are many ways to create and store documentation. you could create an inventory by hand, You could buy a third party product and use it. Or you could use a built-in tool in SQL Server to add the information, and use Reporting Services to display it....(more)

SQL Server: Quickly get row counts for tables, heaps, indexes, and partitions - How do you quickly get a count of the number of rows in a table? This small utility will enable you to quickly get row counts for tables, heaps, indexes and partitions. It works with SQL Server 2005 and higher....(more)

Blogs : DMO/SMO/Powershell

PowerShell: Delete Unneeded Rows/Columns In Excel - On some of the Excel files we needed to import into SQL Server, the header row started on row 2, 3, 4, or 5, and on some others, the first two or three columns were entirely blank. However, each of the datasets begins with a column headed "Identifier", so that is the cell that I want to end up in cell [A1]. They could be cleaned up with PowerShell...(more)

Advanced [Math] in PowerShell - There is a System class named [math], with 30 static methods we can use for doing math. It is a static class, which means you can't create a [math] object. It is a collection of methods, code snippets that we can leverage in our scripts....(more)

ThePowerShell Reference Library - For the past 5 years, PowerShell MVP Tobias Weltner has published a daily tip on powershell.com. Together with fellow MVP Aleksandar Nikolic, he’s started revising and compiling this huge tips collection for you and came up with ready-to-use pocket guides of PowerShell knowledge. ...(more)

Blogs : High Availability/Disaster Recovery

n-Memory OLTP: High Availability for Databases with Memory-Optimized Tables - SQL Server 2012 offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid working with Windows Azure. In-memory OLTP hopes to deliver up to 30x better performance, and is integrated with High Availability features in SQL Server....(more)

Blogs : Integration Services/ETL

Search SSIS packages for table/column references - There is major failing with SSIS that it is sometimes quite difficult to find what a package is actually doing, what it accesses and what it affects. This is a simple dos script which will search through all packages in a folder for a string and write the names of found packages to an output file....(more)

Blogs : Performance and Tuning

Collecting performance data for SQL Server via Powershell - First of all, we have to define the sql server and the database to collect data. Then we decide which performance counters to collect and define the intervals and number of samples to get. We collect the data as an XML string. Lastly we execute a stored procedure to insert the collected data in a table. ...(more)

New Performance Tips eBook Out from Red Gate - 'Not too long ago Red Gate asked for quick tips on SQL Server performance intended for developers. I sent a couple in. '...(more)

Blogs : Security and Auditing

Recipe For Making 1 Million TPS On $5K Hardware - Getting to one million over-the-wire client-server database-requests per-second on a single machine costing $5K is a balance between trimming overhead on many axes and using a shared nothing architecture to isolate the paths taken by unique requests. These techniques could be applied to a ftp server, a static web server, and even to a dynamic web server...(more)

Fake femme fatale dupes IT guys at US government agency - How did World Wide Tech crack open a US government agency that Lakhani described as being, as Constantin paraphrased it, "a very secure one that specializes in offensive cybersecurity and protecting secrets and for which [World Wide Technology] had to use zero-day attacks in previous tests in order to bypass its strong defenses"?...(more)

Anatomy of a password disaster - Adobe's giant-sized cryptographic blunder - One month ago Adobe suffered a giant data breach. As far as anyone knew, including Adobe, it affected about 3,000,000 customer records, which made it sound pretty bad right from the start. But worse was to come, as recent updates to the story bumped the number of affected customers to a whopping 38,000,000....(more)

Hackers gain Info on Rich & Famous By Copying Major Limo Broker Service's Database - The database could be a field day for tabloids (and, one assumes, blackmailers), because the notes associated with certain passengers for things they may have done in those limos can, at times, be embarrassing....(more)

sp SrvPermissions & sp DBPermissions V3.0 for exploring permissions, roles and users - When I need to know all permissions for a given user, copy a login from one server to another (with SID and password), know everyone who has permissions to a specific object in the database or I need to know everyone who is a member of sysadmin...(more)

Blogs : Software Development

Clone an Azure VM using Powershell - I am putting together some hands-on lab material and in order to save time in asking people to install software during the course I am simply going to prepare a virtual machine (VM) containing all the software and lab material for each delegate to use. Given that I am an MSDN subscriber it makes sense to use Windows Azure to host those VMs given that it will be close to, if not completely, free to do so. ...(more)

SQL Server Index Properties in Management Studio - Understanding indexes and how they work can be complicated enough for a Jr. DBA, but throw in all the different options and properties and an index can soon be overwhelming. In this tip, I'll discuss the different options available when creating a basic index...(more)

SQL Server Scripts Runner - A simple console application, which executes all SQL files added into Source Control by developers working with their own copies of the database...(more)

Blogs : Virtualization

Virtualizing SQL Server on Hyper-V and on Windows Azure VMs - There are several factors to consider before moving SQL Server workloads to a virtualized environment. I’ll focus on how to virtualize SQL Server on Windows Server Hyper-V, however several, if not all of factors for consideration pertain to other virtualization platforms as well....(more)

Articles

The White Cane as Technology - There is, in IT, a well-meaning but utterly wrong-headed notion that "ubiquitous computing" will save the world. I see a troubling number of technologies now touting themselves as prototypes for blind or deaf users—as an afterthought application. ...(more)

Best Information Databases of 2013 chosen by librarians - While this year’s list features a few winners from industry giants such as Gale/Cengage, ProQuest, and ­Elsevier, most nominees were from smaller and newer publishers including Zinio, Novel Data, Plum Analytics, and Rosen Digital. ...(more)

The myth of the brainstorming session - No matter how much we say we love creative ideas as a society, our brains are hardwired to fear novelty. Making a wrong decision is literally painful for our brains to cope with so we tend to seek out ideas that are safer....(more)

Articles : Development

The Case For Agile Over Waterfall - Overall, Agile methodologies are better for software development projects than traditional project management (Waterfall). Even Agile projects fail; having a superior methodology is no substitute for a good team and solid project discipline. ...(more)


Administrative