In this issue:

Tech News : General Interest

Tech News : Security

Product Reviews

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : FILESTREAM Data

Blogs : Integration Services/ETL

Blogs : NOSQL

Blogs : Performance and Tuning

Blogs : Replication

Blogs : Security and Auditing

Blogs : SQL CLR

Blogs : SQL Server 2012

Blogs : SQL Server 2014

Blogs : T-SQL

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

SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
SQL DBA Bundle ‘10 Tips for Efficient Disaster Recovery’
Steve Jones gives the final lesson in the ‘Top 5 Hard-earned Lessons of a DBA’. Read now and learn from the best.
SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
Editorial - Some Relevant PowerShell Books

I’ve been reading quite a number of books on Powershell and SQL Server recently. Actually, I’ve read only two that are dedicated to covering both topics together, though several PowerShell books are useful to the database professional.  The first of these, Professional SQL Server 2008 Administration with Windows PowerShell, by Ananthakumar Muthusamy (MAK)  and Yan Pan, covers a wide range of topics and has some useful information but suffers from being written before SQLPS matured.  More immediately useful is SQL Server 2012 with PowerShell V3 Cookbook by Donabel Santos MVP. It is less ambitious in its scope, but nails the topic of using SMO with PowerShell more thoroughly. It is essential reading for any SQL Server professional.

Other books touch on administrative tasks. I like Richard Siddaway’s ‘PowerShell in Practice’.  For anything WMI, use the free eBook ‘WMI Query Language via PowerShell’ by Ravikanth Chaganti.  For covering general Windows admin work, I rate The Windows Powershell 2.0 Adminstrator’s Pocket Consultant by William Stanek though it isn’t the sort of book one would want to read for pleasure.

For general information about PowerShell there is one essential PowerShell book, Bruce Payette’s ‘Windows PowerShell in Action, Second Edition’. It is full of insights and talks directly to professional programmers.  Although the author is occasionally remorseless in his thoroughness to get into detail, it is always rewarding to read. Lee Holmes’ ‘Windows PowerShell Cookbook’ is very thorough and I refer to it quite a lot for the excellent information, but mostly via its index. It’s only fault is an editorial one, and that is that the subject matter isn’t a good fit with the idea of a cookbook, and the analogy is occasionally strained. Lee Holmes also  wrote the useful  ‘Windows PowerShell Pocket Reference, 2nd Edition which uses the same information in a more straightforward way, but with less detail.

For a more developer-oriented take on PowerShell, I enjoy Douglas Finke’s blog. He also  wrote ‘Windows PowerShell for developers’, which has some gems in it but suffers from insufficient editing. It is probably the best PowerShell book for developers, but it reads more like a series of extended blog posts.

For the complete beginner to PowerShell, I’m not sure what to recommend. I started by reading Bruce Payette’s book, but you may have to find one of the last real bookshops in your area and nose into a few to find what suits you best. Have you read any really good PowerShell books recently? I’d be interested to hear about it.

» 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

Google Can Now Translate Handwritten Notes - Go to the Google Translate page, select the language you're working with, and go to the menu on the bottom left of the input window and select the pencil icon. You can then write out what you're trying to translate, and Google will interpret. ...(more)

Tech News : Security

Microsoft took down Rustock botnet thanks to 67 year old counterfeit law - In March 2011, Microsoft shut down the Rustock criminal botnet, which included seizing physical computer equipment in a number of major US cities. Microsoft found an interesting loophole in the laws of the US that allowed them to raid those locations. A bill that was passed by Congress in 1946, the Lanham Act, does allow for equipment to be seized in a civil lawsuit if that hardware is being used for counterfeiting....(more)

Product Reviews

Book Reviews: Pick of the Shelf - SQL Server 2012 - The release of SQL Server 2012 has resulted in an avalanche of books designed to guide you through all aspects of working with Microsoft’s server database. The thought of reading an entire bookcase of books is enough to make normal mortals run for the hills. Fortunately, our database experts are far from normal mortals, and they’ve done the hard work of sifting through the books for you to find the best on offer....(more)

Book Review: SQL Server Concurrency: Locking, Blocking and Row Versioning - The the author's clear and coherent explanations, the down-to-earth practical examples, the presentation with its small number of pages and great typography (big fonts, large padding and spacing), work together for an easy flowing and intriguing read....(more)

Blogs : Administration

How to Find ‘Secret Columns’ in Nonclustered Indexes - SQL Server needs a unique way to navigate to every row in an index. It also needs to maintain a relationship between every nonclustered index and its base table. In order to accomplish these two tasks, SQL Server always makes sure to add one or more columns to a nonclustered index. If you don’t add the columns yourself, it goes ahead and does it secretly...(more)

How many SQL Server DBAs does an organization need? - I never seem to run into those situations where there are too many SQL Server DBAs. With that iin mind, I have come across quite a few situations where a qualified DBA is needed either because there is none or the factors listed above warrant an addition of resources....(more)

Blogs : Analysis Services / BI

Creating a Business Intelligence Dashboard with R and ASP.NET MVC: Part 2 - Continuing the project to build a Business Intelligence (BI) application using R, Visual Studio 2012 and Twitter Bootstrap, Sergei introduces drill-down via jQuery and jQuery UI, simplifies the R coding with the PLYR and GGPLOT2 libraries, and shows how to generate reports simultaneously with a batch file...(more)

Blogs : Backup and Recovery

Step by Step : Backup and Restore for SQL Server in Windows Azure Blob Storages - With SQL Server 2012 SP1 + Cumulative Update 2 you can back up to and restore from the Windows Azure Blob storage service directly by using T-SQL backup and restore statement. By using this feature you can provide a disaster recovery solution to your applications. ...(more)

Blogs : Big Data

The refactoring of everything - I expect traditional databases — i.e. ones that focus on human-generated data — to eventually wind up in RAM. I imagine that there will be both relational and dynamic-schema APIs to the memory-centric DBMS that manage them...(more)

Using Less Data - Operating on smaller datasets was one of the best ways to ensure productivity and rapid experimentation when doing data science.The idea was inspired by the class-imbalanced subsampling heuristic. This is a well-known trick amongst computational advertising practitioners, and the uncanny effectiveness of this technique always intrigued me. ...(more)

Avoiding Pitfall of Failure: Java, SQL or other Programming Language? - Enterprises must use the advanced analysis tool that is business-expert-oriented, regardless of user’s technical background, and capable to convert the business logics to the business algorithm rapidly, intuitively, and conveniently. How about NoSQL or SQL? Neither of them is ideal. From the TCO perspective, I would rather choose the lightweight R language....(more)

Blogs : Computing in the Cloud

Why Many Private Cloud Projects are Failing - While survey after survey show that enterprises claim to prefer private cloud models, IT is struggling to deliver. Off the record many in the IT consulting and SI business will say the majority of private cloud pilots they’re seeing aren’t going well. In fact some believe that up to 90% of private cloud pilots in the enterprise will fail to move forward....(more)

Windows Azure July Updates: Support for Automated SQL Database Exports - You can now export transactional-consistent copies of your SQL Databases, in an automated recurring way, to a .bacpac file in a Storage account using any schedule you wish to define....(more)

Blogs : DMO/SMO/Powershell

Finding SQL Server Cluster Failover Events using PowerShell v3 - Here’s a 2 node cluster running Windows Server 2008R2 for the OS and a single instance of SQL Server. If the name of the clustered service/application is known, querying the event log using PowerShell’s remoting features implicitly with Invoke-Command (aliased to icm) makes finding cluster failover events pretty quick. ...(more)

Introducing the PowerShell and WMI Browser - Kaido Järvemets (Enterprise Client Management MVP), has released a new WMI and Windows PowerShell browser tool. It is great for any kind of WMI browsing, but it also enables you to browse the Windows PowerShell commands on the local computer to get a list of the valid parameter sets. Written completely in Windows PowerShell using WPF form...(more)

Blogs : Events

SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances - The new UI for Extended Events in SQL Server 2012 Management Studio only works for SQL Server 2012 instances, but administrators that manage multiple versions have no way to explore the option of using Extended Events on their SQL Server 2008/R2 instances. Today, we’re releasing a new SQL Server 2012 Extended Events Add-in to provide backwards compatibility with SQL Server 2008 and SQL Server 2008R2 for Extended Events...(more)

Blogs : FILESTREAM Data

How to: Restoring SQL File stream Data - It is not uncommon to have multiple copies of the same database on a single instance or to restore copy of a database from one instance to another for testing. How does this work when I have filestream data in my database? ...(more)

Blogs : Integration Services/ETL

Parsing Delimited Data with a Data Flow Script Task - I ran into a rather unique problem in SSIS in which the extracted delimited data, from a “multi-dimensional” database, was in a format in which each column contained ALL values for each row in that column. ...(more)

Blogs : NOSQL

The problem with NoSQL databases - Whether it be MongDB, Couchbase, CouchDB, Hypertable, HyperDex, Riak, Redis, DynamoDB, SchemafreeDB or Oracle NoSQL, there is one thing you gotta know. These databases can be resource intensive. They demand higher CPU and RAM allocation than any relational database...(more)

Blogs : Performance and Tuning

Myth Busting Query Optimisation - The very best thing you can do to avoid poor query performance and bad plans is to use proper data modeling techniques. Good data modeling has a powerful effect on all relational databases, both column and row stores. SQL Server’s optimiser is one of the smartest optimisers out there, but even that piece of code needs a hand from the data modeller....(more)

Blogs : Replication

Many Masters, One Truth - People often want a database that lets them read or write data on any server transparently. While it’s possible to use SQL Server, no replication solution is ideal. If you’re willing to consider changing your schema, what about the possibility of changing your underlying data storage system? (by Jeremiah Peschka) ...(more)

Blogs : Security and Auditing

The scenario of a missing SA password: - You are the senior DBA of your organization. In order to secure the environment and the databases, you removed all built-in admin account from SQL Server logins, all users from SYSADMIN server role except SA, change the SA account password to something extremely complex. For SQL daily operations, you use a domain user account with DBO permissions on user databases with no SYSADMIN privileges on the system, and you haven't saved the SA password. You've now forgotten it! ...(more)

Blogs : SQL CLR

Interval Queries in SQL Server Part 4 - Unfortunately, it looks like temporal data support is not coming in SQL Server soon, at least not yet in the next version (2014). Therefore, it looks like we have to use our own solutions for a while. In this post, I am introducing a solution that uses my interval on a countably infinite discrete set called IntervalCID CLR user defined type. ...(more)

Blogs : SQL Server 2012

Cache Warming on an Analysis Services Tabular server - When the Analysis Services service starts, it does not immediately load and warm the cache of the Tabular database files. The first user will have to wait for their database to load which can take 30 seconds up to a few minutes, depending on how many GB of files there are to load...(more)

Blogs : SQL Server 2014

SQL Server 2014: In-memory OLTP Engine, code-name: Hekaton - Hekaton provides in-memory OLTP capabilities built into core SQL Server database to significantly improve the performance of your database application. Hekaton is installed with the SQL Server 2014 Engine without requiring any additional actions and allows in-memory performance benefits without rewriting your database application. ...(more)

Buffer Pool Extension to SSDs in SQL Server 2014 - The Buffer Pool Extension feature (BPE) targets nonvolatile storage devices, in particular SSD drives, as an extension for SQL Server buffer pool. The first advantage is the higher flexibility of storage options over memory options; the second is the greater price efficiency of available storage vs. memory....(more)

Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology - The new In-Memory OLTP architecture executes stored procedures more efficiently. Individual CPU cores are not getting significantly faster and there is not much opportunity for multi-core parallelism within OLTP stored procedures that access relatively few rows. To achieve dramatic improvements in performance the database system code must get more dramatically more efficient....(more)

Blogs : T-SQL

The SQL of Gaps and Islands in Sequences - Some SQL problems are intriguing because, just when good methods emerge and are accepted, other alternative solutions are discovered. The fun of exploring problems such as 'Gaps and Islands' is all the greater when we have a thorough test-harness to try out the alternative solutions....(more)

Gap issue in SQL Server 2012 IDENTITY column - I found a gap issue when using IDENTITY column in SQL Server 2012, every time I restarted the SQL Server service, the current identity number will jump to 1000. This was reported by Grant Fritchey to Microsoft connect in April last year. ...(more)

Articles : Development

Correctly Building Asynchronous Libraries in .NET - Building an asynchronous library requires very specific design patterns that can be quite different from the patterns used when consuming an asynchronous library. But if you follow some basic rules you can greatly improve the experience for the consumers of your libraries....(more)

20 Popular Javascript Charts And Graphics Libraries - Popular JavaScript Charts and Graphics libraries such as jQuery, MooTools, Prototype, Dojo and YUI can be great for accomplishing common JavaScript tasks. and they'll do simple bar, line or area charts and more besides. But if you want more......(more)

Skeuomorphism: Perfect if You Aren't Digging the Flat Design Trend in Application Design - When it comes to website and application design, skeuomorphism ( the designer tries to make items on the screen look like things from the physical world) refers to how something works. Both the look and the corresponding action mesh in an intuitive way. Design for the user. It really is that simple. ...(more)


Administrative