In this issue:

Tech News : Disaster Recovery

Tech News : General Interest

Tech News : Security

Tech News : The Lighter Side

Microsoft News : Security

Blogs : Administration

Blogs

Blogs : Computing in the Cloud

Blogs : Developer Tools

Blogs : DMO/SMO/Powershell

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : Software Development

Blogs : T-SQL

Articles : Administration

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

SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
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 - Cloud Services. Waiting for the 'paint to dry'?

There is no doubt that the recent cloud ‘outages’ have tempered the more extravagant claims for the reliability of the cloud. Such things matter for many applications. The problem that affects so many of the less-experienced commentators on the IT industry is that it is hard to grasp the diversity and range of applications and their contrasting requirements. One one hand, we have games, photo stores and entertainment, and the other we have critical healthcare applications where lives can be lost if systems go down or financial applications that have economic repercussions if they fail. Many manufacturing systems can cause catastrophe if they become unavailable for any significant time. It is human nature to extrapolate from ones own experience. We therefore get over-generalised prophesies about an imminent-future that is almost entirely cloud-based.

Although it is tempting to react cynically to the worst of the Cloud evangelism, there are still plenty of ways of using Cloud services, even now in their imperfect state. It would be unwise to ignore them.

The canny CIO will make use of the obvious advantages of public cloud services where it risks neither security or availability. Adoption of the Cloud services and platforms is going to be most effective where the Cloud’s advantages of rapid scalability can be harnessed. There are other obvious candidates in development work such as bug tracking, feature-usage reporting, workflow and source control. For archiving, a move to the cloud seems obvious. For the database professional, there are plenty of other opportunities for using the cloud. Already, automated database testing is becoming easier to manage by consolidating each test environment on VM images in the cloud. It is also useful for offsite backup, or even limited Backup-as-a-Service. The design of hybrid applications is now well-proven, and there are plenty of ways for putting less critical aspects of database applications, such as reporting and monitoring, in the Cloud. I’m quite relaxed about the idea of maintaining hot standbys in the cloud, just in case. If I could find them, I’d cheerfully use cloud-based services for more mundane tasks such as formatting SQL code, checking for ‘code smalls’, detecting unused variables, finding naming inconsistencies, or checking compatibility of code with different versions of SQL Server.

So, in a year’s time. Do you see that, for your role, you could usefully exploit a wider range of Cloud services or platforms for some of the tasks that you are responsible for? Are you constrained by compliance issues, security issues or corporate policy? Are you waiting for the ‘paint to dry’ before adopting Cloud service?

» 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 : Disaster Recovery

CIOs struggling with virtualised back-up and recovery - CIOs in Europe and the US say their organisations are failing to gain the full benefits of virtualised architectures, according to research which showed companies are suffering from poor data backup and recovery strategies, and inadequate data protection....(more)

Tech News : General Interest

NASA Mars rover Curiosity on road to recovery after computer trouble - Curiosity's deputy project manager said on Monday that engineers watching the rover's telemetry last week noticed certain applications would terminate mid-sequence. The cause, he noted, appears to be a file corruption....(more)

Tech News : Security

Ransomware targets Windows PowerShell - Security researchers have discovered a novel ransomware scheme that uses Windows PowerShell to encrypt files on a victim's computer. After encrypting the files, it holds them hostage, demanding payment of a ransom to unlock the data....(more)

Tech News : The Lighter Side

Project Mismanager - In IT, it is difficult to measure individual productivity, and it is particularly difficult to determine whether a project manager for a development project is 'pulling his weight'....(more)

Microsoft News : Security

Microsoft Patch Tuesday: Update Quickly To Avoid Internet Explorer Drive-By Attacks - This month’s Patch Tuesday from Microsoft is an important one as users who don’t update quickly may be leaving themselves open to drive-by attacks (in which malicious code will be downloaded onto their computers without their knowledge as they surf the web)....(more)

Blogs : Administration

That’s Actually A Duplicate Index - The problem with duplicate indexes is that they are pointless and redundant. All they provide is an additional physical copy of the index and all you get is another index for SQL Server update and maintain. Seems like a fairly raw deal....(more)

Does Connect Work? - I had a brief conversation on Twitter about the Microsoft Connect bug reporting system, and received the tweet pictured above. It notes: "I like how you used 'Connect works' in that sentence. Great work of fiction." That made me laugh because it does sometimes seem that Connect doesn't work all that well for those of us that use it. ...(more)

HIPAA compliance and SQL Server: Tricks and tools of the trade - There are times when you absolutely have to use real data -- for example, when a database experiences an issue with bad data. In those cases, Brandon Leach tries to use as little data as possible to get the job done....(more)

Job History row limiter - Having limits on both the total number of rows for job history and individual jobs is a good idea. But this is an example of a situation where we are not concern about guaranteed accuracy. ...(more)

Autogrowth option for SQL Server database files - It is not a good idea to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase....(more)

Guest user account in SQL Server - It is a good idea to to disable guest user in every user database by revoking the CONNECT permission. But be careful not to do it for master, msdb and tempdb system databases....(more)

Blogs

Bootstrapping SQL Server bloggers and blog readers with Twitter! - If you as a blog author tweet a link to a newly published SQL Server related blog post and use the hashtags #sqlserver & #blogged. If you want to read these blogs, simply subscribe to the appropriate RSS feed in your RSS reader of choice and watch as (hopefully) great content flows into your RSS reader without you having to lift a finger....(more)

Blogs : Computing in the Cloud

Use PowerShell to Find Certificates that are About to Expire - It is easy to find certificates that are about to expire in Windows PowerShell 3.0 is because we add a dynamic parameter to the Get-ChildItem cmdlet when the cmdlet targets the Cert: PSDrive. The dynamic parameter is called –ExpiringInDays and it does exactly what you might think it would do— it reports certificates that are going to expire within a certain time frame....(more)

Blogs : Developer Tools

Improvements in the Red Gate source code control suite - A while back, Red Gate released Source Control for SQL Server but I was also interested to note their upcoming release of Source Control for Oracle. While I focus predominantly on SQL Server, many sites I work with use more than one type of database engine, and the other engine is often Oracle. So it's useful to be able to use a single system for both....(more)

Blogs : DMO/SMO/Powershell

Hacking SQLCMD Mode: Introducing PowerShell - Running PowerShell commands though SQLCMD mode is something that's been available to us for a very long time. It seems that we just failed to notice...(more)

Blogs : Integration Services/ETL

Why is my Stored Procedure executed twice in SSIS? - When I revisited my stored procedure, I realized that I have overridden the default option of SSIS and explicitly have SET FMTONLY OFF. Now, I removed SET FMTONLY OFF from the OLE DB source and my problem is resolved! ...(more)

Integration services packages using Execute process task with WinZip32.exe fails intermittently, when run under a SQL Server Agent Job: - The scheduled job fails consistently or intermittently when executing an Integration services package that contains an Execute Process Task calling a custom or 3rd party executable scheduled through a SQL Agent job...(more)

Script Component Recompile in SQL Server 2012 Integration Services (SSIS) to refresh metadata - How to avoid the 'metadata is out of sync' error in In SQL Server 2012 SSIS ...(more)

Blogs : Performance and Tuning

The AdventureWorks2008R2 Books Online Random Workload Generator - I created a large script file that contains all of the SELECT statement examples from the SQL Server Books Online. This script is divided into separate sections using a delimiter, and then I wrote a PowerShell script that reads the file and breaks it down into individual scripts that are randomly executed against the configured SQL Server using SMO. ...(more)

Execution Plan Analysis: The Mystery Work Table - I am a fan of writing queries using explicit data types (rather than relying on implicit conversions) and generating relatively small query plans that offer the query optimizer clear and obvious choices. By necessity, this often means writing small SQL queries in logical steps...(more)

Blogs : Reporting Services

Seven dirty secrets of data visualisation - What are the real challenges visualisation developers face, and what don't they want you to know about their work? Two professional practitioners lead you through some of the dirty secrets of the infovis trade ...(more)

Blogs : Security and Auditing

The Davinci code ( SQL Cryptology) - How to use Symmetric keys to meet a role-based requirement that the user should be able to see only the data which he is entitled to see...(more)

Blogs : Software Development

Exporting SQL Query Results to a CSV file using SQLCMD and C# - Today a colleague asked me how to export SQL Query Results to a CSV file. This got me thinking that it would be a nice exercise to do this from a C# application....(more)

Speeding up your application with the IIS Auto-Start feature - Have you ever thought that the first request to your ASP.NET application seems to take much longer than others? If so, then you’re not imagining things…it actually does. That’s because to maximize resources IIS doesn’t keep all sites on an instance running at all times....(more)

Chunks of development work are like database transactions - I noticed something recently, that I was using exactly the same logic in two seemingly unrelated aspects of my job. Database transactions and self-contained chunks of engineering work are exactly the same! Bear with me…...(more)

Blogs : T-SQL

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB. - Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process....(more)

Conversation History: The Continuing Case Against GUIDs - Why not use a compound key of integers? whereas an identity column would provide a compound primary key with guaranteed uniqueness and at half the cost (4 bytes x 2) of a GUID (16 bytes). That storage space adds up, and has a big impact on nonclustered indexes, fragmentation, page splits...(more)

Generating Sequence Numbers Using the Sequence Object - With the introduction of SQL Server 2012, Microsoft introduced a new method to generate a sequence number, using a Sequence Object. This new object can be used to generate unique numbers that can be automatically incremented based on an increment value, and much, much, more. In this article I will be discussing the different features of the sequence object and how you can use it to generate sequence numbers....(more)

SQLCMD and PowerShell: Generate 450 Million Names With 20 Lines of Code - One of the Census Bureau's publications is a list of frequently occuring first names and surnames. Today, we'll download these data files and insert them into a table using nothing more than SQL Server Management Studio in SQLCMD mode....(more)

Column name or number of supplied values does not match table definition when dealing with temp tables - You can hit problems If you have two or more procs that have a temporary table with the same name if you try to execute them within the same connection, because the temporary table is local to your connection but it not automatically dropped until you close the connection. ...(more)

Giving only insert permissions to a table for a new login - I was faced with a requirement to create a new user who would have only insert permissions to one table, this user would also have insert and select permissions to another table. This is pretty simple to accomplish...(more)

Standardize Table Aliases - I've to give a shout out to RedGate's SQL Promt. In addition to other features, SQL Prompt allows you to automatically assign table aliases, and specify custom aliases forcing you to use standard aliases....(more)

Use the sp_describe_undeclared_parameters stored procedure to check if dynamic SQL has undeclared parameters - Sometimes you get some dynamic SQL handed to you which is 2 pages long with all kind of parameters. Of course you yourself would never write such a monstrosity, but how can you verify that all parameters in the code have been declared? ...(more)

Trigger Authoring Decision Tree - On the evils of creating a trigger for the wrong reasons. There are good reasons?...(more)

Articles : Administration

Partitioning Using the Wizard in SQL Server - I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index View...(more)


Administrative