In this issue:

Tech News : Data Mining/Warehousing

Tech News : General Interest

Tech News : Security

Microsoft News : General Interest

Microsoft News : Patches, Bugs

Hardware News

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Career

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Excel

Blogs : FILESTREAM Data

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : MDX

Blogs : Performance and Tuning

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL CLR

Blogs : SQL Server 2012

Blogs : T-SQL

Blogs : XML, XPATH and XQUERY

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

Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
SQL Backup Pro Can you can restore your backups under pressure?
Use SQL Backup Pro's fully integrated DBCC CHECKDB to verify your backups, so you can restore them when it matters most. Download a free trial now.
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.
Editorial - Algorithm Secrecy is not Security

I find the field of cryptography and encryption to be fascinating. I present a talk on encryption and  constantly find people that don't realize the security of an encryption algorithm doesn't depend on what the algorithm is or knowing how it works. I can tell you that I'm using an AES or RSA algorithm and that doesn't make it any easier to decipher the plaintext.

This week Troy Hunt wrote a piece where he called for disclosure by websites (and really all applications) of the password storage mechanism. The badge above is an example of what he'd like to see posted on all websites. His explanation of what this might change in terms of Internet security is great, and while this might not actually make applications more secure, I'm not sure that many companies would want to be forced to disclose they are storing passwords in plain text. Consumers are becoming more savvy and realize this is a poor way of managing systems. I suspect that people will quickly learn what are strong and no-so-strong algorithms.

Real security still requires developers to not only implement strong algorithms in their applications but also make these algorithms upgradeable. We used to use MD5 everywhere (now a bad idea), and in SQL Server 2008 R2, we are limited to SHA1. This algorithm is known to have problems and SHA2 is recommended. Unfortunately you would have to upgrade to SQL Server 2012 in order to use this algorithm. You can write your own implementation, and if you have high security requirements, I'd encourage you to do so.

Key security is important, and this is to systems what password security is to individuals. Protecting your keys (and passwords) is ultimately the way in which we can dramatically increase the level of security in applications. This is the hardest part of managing encryption in your application, and I'd encourage you to seek out someone with experience to help you understand how to best handle  this.

Ultimately a lot of security depends on layers, with some secrecy in place, but the secrets you keep should be the exact design and implementation of your entire infrastructure, not the algorithms used. If you build encryption into your systems, please do not design your own algorithm. On a regular basis the vendors and developers who think they've implemented strong encryption by hiding the details of their algorithm are usually unaware of how flawed this approach is. Please use well known, public algorithms for encryption, which have been tested and probed by mathematicians in a public forum.

» 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 : Data Mining/Warehousing

IBM Watson Could be Offered as Technology as a Service - The IBM Watson supercomputer. After much media attention when IBM’s Watsontook on top Jeopardy champions in 2011 and won, the team behind the cognitive computer has helped it gain greater understanding of content and context. Today, IBM is experimenting with offering a way to reach Watson through a cloud service, and recently asked USC students for creative ways to apply Watson to business and social challenges....(more)

Tech News : General Interest

Thousands petition Google not to kill off Reader - More than 60,000 users of Google Reader have signed a new online petition imploring Google not to pull the plug on Google Reader. The company announced just yesterday that it was going to cut several services, including Google Reader, GUI Builder, and Cloud Connect as part of its ongoing effort to focus on products that will yield profit. The newest round of cuts brings the list of abandoned services and features to 70, according to Urs Hölzle, senior VP of technical infrastructure at Google....(more)

10 trends shaping IT hiring in 2013 - There's plenty of reason for optimism about IT jobs. CIOs are ramping up hiring, average salaries are climbing, and companies are increasing their investments in core tech areas such as security, mobility and data analytics. Concerns about the economy and budget battles on Capitol Hill have put a damper on the IT job scene, but not enough to thwart expansion....(more)

Which tech degrees pay the most from day one? - Young technologists have a variety of undergraduate degrees that they can pursue at the collegiate level. But which degree is going to produce the most job offers and the highest starting salaries? Should college students major in computer science, software engineering, IT, or some other niche in order to snare the top prize four years from now: a six-figure starting salary, perhaps with stock options?...(more)

The Vatican Library Goes Digital - EMC is providing storage systems to help the Vatican Apostolic Library (pictured above) digitize and store rare manuscripts. (Photo by Michal Osmenda via Flickr.)...(more)

Virtualization: The Next Frontier… - Several years ago, we began using virtualization technologies as means to test servers and use resources more effectively. When VMware became a hypervisor, very few vendors actually supported a virtual infrastructure. So in many organizations, virtualization was relegated to the classroom and development environments....(more)

Tech News : Security

Vulnerability database hack highlights need to bolster cyber security - The recent hack of the National Vulnerability Database (NVD) is one more example of the need for a stronger U.S. cyber security strategy....(more)

Evernote Limits Hack Damage with Good Security Practices - ...(more)

Oracle Rushes Out New Java Zero-Day Patches - ...(more)

HIPAA and PCI Compliance Are Not Interchangeable - Mike Klein is president and COO of Online Tech, which provides colocation, managed servers and private cloud services. He follows the health care IT industry closely and you can find more resources at www.onlinetech.com/compliant-hosting/overview....(more)

Microsoft News : General Interest

Microsoft reverses its Office 2013 license transfer rule - Microsoft has had a change of heart about its controversial transferability rule for Office 2013 applications. A new post on the Office blog says that henceforth your Office 2013 license won't die with your machine. If you buy Office 2013 and install it on a PC, then later decide to upgrade the PC (or have to replace the old one because of an unexpected trip over Niagara Falls), you can take the old license and transfer it to the new machine....(more)

Microsoft News : Patches, Bugs

Microsoft SharePoint Server 2010 / Foundation 2010 Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Sharepoint Server 2010 and Foundation 2010, which can be exploited by malicious people to conduct cross-site scripting attacks, bypass certain security restrictions, disclose certain system information, and cause a DoS (Denial of Service)....(more)

Microsoft Windows Kernel-Mode Driver USB Descriptor Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Windows, which can be exploited by malicious people with physical access to compromise a vulnerable system....(more)

Microsoft Internet Explorer Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Internet Explorer, which can be exploited by malicious people to compromise a user's system....(more)

Microsoft OneNote Buffer Size Validation Information Disclosure Vulnerability - A vulnerability has been reported in Microsoft OneNote, which can be exploited by malicious people to disclose potentially sensitive information. The vulnerability is caused due to an error when validating buffer sizes, which can be exploited to read arbitrary data from memory and e.g. disclose username and password for configured accounts via a specially crafted OneNote file. ...(more)

Microsoft Visio Viewer Tree Object Type Confusion Vulnerability - A vulnerability has been reported in Microsoft Visio, which can be exploited by malicious people to compromise a user's system. The vulnerability is caused due to a type confusion error when handling Tree objects and can be exploited via a specially crafted Visio file. ...(more)

Microsoft Windows Flash Player Multiple Vulnerabilities - Multiple vulnerabilities have been reported in Microsoft Windows, which can be exploited by malicious people to compromise a user's system....(more)

Microsoft Silverlight HTML Object Rendering Double-Free Vulnerability - A vulnerability has been reported in Microsoft Silverlight, which can be exploited by malicious people to compromise a user's system....(more)

Hardware News

EMC unveils all-SSD array, one app to manage all flash - EMC today released its first, purpose-build, all-solid state drive (SSD) array that is based on its XtremIO acquisition last year, as well as new software that identifies server flash throughout a data center and allows it to be managed under one interface....(more)

Six Tips for Selecting HDD and SSD Drives - Gary Watson is Chief Technology Officer, Nexsan, an Imation Company. GARY WATSON Nexsan With today’s wide variety of storage devices comes lots of confusion about what types of drives to use for what data types. Adding to the confusion is Serial ATA (SATA) and SAS, which refer to disk drive interfaces, and Solid State Drive (SSD) which refers to a particular kind of internal technology. Then there are considerations of random access performance, sequential performance, cost, density and reliability....(more)

Blogs : Administration

Moving SQL Server databases - It's not uncommon to find SQL Server installations where the physical file layout is less than optimal. We have a client who is having some performance issues and in initial discovery, we observed that they had the system databases on the C drive and had put their user databases and log on the D. The challenge you can run into, and they are experiencing it, is that heavy workloads can saturate your I/O subsystem and that brings everything to a crawl. There's also a risk of running your C drive out of disk space that way which can cause the OS itself to stop responding. The client has already purchased a SAN and as part of our effort, we're going to relocate user and system databases onto the SAN. ...(more)

List all Objects and Indexes per Filegroup / Partition - Today, I received a call from friend asking how he can find out which database object belongs to which filegroup....(more)

SQL Server – How to Rename Table/Column in SQL Server - To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio....(more)

Dealing with a Fragmented Heap - Just for the record, this happens to be one of my favorite interview questions to ask candidates....(more)

SQL Server 2012 Diagnostic Information Queries (March 2013) - Here is the March 2013 version of my SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements to several of the existing queries. I have added some new columns to several of the queries, and improved the formatting of the results to make many of the queries easier to read and interpret. ...(more)

Job History row limiter - There are situations where guaranteed accuracy correctness is essential, which is why we have transactional databases meeting the ACID properties. And then there are situations where we just need an approximate number. An example is the size of the SQL Agent job history log. ...(more)

That’s Actually A Duplicate Index - As I’ve worked with clients on performance tuning through index improvements, one of the common areas that I discuss with them are duplicate indexes.  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)

SQL Server – Start/Stop SQL Server from command line - You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges....(more)

T-SQL Tuesday #40 - Proportional Fill within a Filegroup - T-SQL Tuesday #40 is underway, and this month's host is Jennifer McCown ( | ).  The topic is about File and Filegroup Wisdom.  Jennifer says she's a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup.  This should be pretty common knowledge, but I still talk to a lot of DBAs that don't know anything  about it, or if they have heard of it, they still don't know how it works....(more)

T-SQL Tuesday #40– File and Filegroups - It’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server. ...(more)

Administering SQL Server 2012 Running On Windows Server Core - Are you afraid of deploying (or supporting) an instance of Windows Server Core? Are you telling others to “stay away” because of your own fears of doing something new?...(more)

Filegroups and Non-Clustered Indexes - Let’s start with some basics and then jump in a bit deeper, for this post to go with the 40th T-SQL Tuesday, hosted this month by Jen McCown. ...(more)

Blogs : Analysis Services / BI

Why you need Business Intelligence - First lets define what Business Intelligence (BI) is.  My favorite definitions: Forrester Research: “Business Intelligence is a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information used to enable more effective strategic, tactical, and operational insights and decision-making.”...(more)

Remove Local Administrators SSAS Admin Access - Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG). ...(more)

Microsoft’s Database Research Lab Conceives PolyBase to Easily Join Relational Data With Data From Hadoop - Complementing Microsoft’s overall Big Data strategy, PolyBase is a breakthrough new technology on the data processing engine in SQL Server 2012 Parallel Data Warehouse Appliance designed as the simplest way to combine non-relational data and traditional relational data in your analysis. While customers would normally burden IT to pre-populate the warehouse with Hadoop data or undergo an extensive training on MapReduce in order to query non-relational data, PolyBase does this all seamlessly giving you the benefits of “Big Data” without the complexities....(more)

Implementing a ‘Time Calculations’ Dimension in Tabular via DAX - A common request from business users is ‘Time Intelligence’ functionality in an SSAS cube.  For example, users may wish to compare current year sales with the prior year or calculate sales year-to-date.  DAX includes a wide range of time intelligence functions to perform these types of calculations.  We can create measures in a tabular cube for the various time intelligence formulas, such as Sales Year-Over-Year or Sales Year-to-Date.  An alternative to exposing individual measures for the various time intelligence formulas is to create a ‘Time Calculations’ dimension....(more)

The Dangers of Non_Empty_Behavior - One thing I see quite often when I’m reviewing MDX is the incorrect use of the Non_Empty_Behavior property. 99% of the time it’s set without the developer really knowing what it does, and luckily in most cases it makes no difference to performance or what the calculation returns. However… in this post I’m going to argue that you should not use it unless you really know what you’re doing and you can prove that it’s beneficial – because if it’s set incorrectly it can sometimes mean you get incorrect results from your queries....(more)

Calling A Web Service From Data Explorer, Part 2 - Don’t you hate it when you get ready to blog about something cool, and then someone comes along and beats you to it? That’s what’s just happened to me – Matt Masson just wrote an excellent blog post on how to do address lookup against a web service here:http://www.mattmasson.com/2013/03/dynamic-lookups-with-data-explorer/That’s what I was going to write about in part 2 of this series. I have to say he did a better job than I would have done though, and shows off several cool tricks I hadn’t seen before…...(more)

Blogs : Backup and Recovery

Backup BizTalk Job not able to backup databases which reside on the different SQL server - I recently came across an issue where my backup BizTalk job was not able to back up DTA and few other databases which reside on a different SQL server. I noticed that my job was failing with the below error message each time it was trying to complete....(more)

Blogs : Career

Career Growth and How You Can Achieve Explosive Results - Continued investment in your professional development for career growth is hard. It’s also essential to being a successful Data Professional....(more)

Blogs : Computing in the Cloud

DevOps for Windows Azure - "DevOps" (Short for Developer Operations) is one of a group of new terms such as "Cloud", "Big Data" and "Data Scientist" - words that are somewhere between marketing and tasks we've actually had around in other forms for years.However, working in a Distributed Environment (Both on and off premises) like Windows Azure does bring a new set of tasks to the operations we currently perform in Information Technology....(more)

Blogs : DMO/SMO/Powershell

PowerShell Usage in DBA Work – 3/7 - As a DBA, sometimes we may need to demonstrate to some stakeholders when a sql server instance was last rebooted, i.e, just to prove the sql instance weekly/monthly availability . I know there are quite a few blogs / posts online talking about how to do this check, such as this one: ...(more)

PowerShell Usage in DBA Work — Case Study 2 / 7 - As a DBA in a complex environment, we frequently need to backup various environment information. One of the backups is to script out the configurations / objects, for example, scripting out replication,  jobs, policies and policy conditions etc. Almost all of these scripting work can be done via SSMS, however, there is one that cannot be done via  SSMS currently....(more)

Blogs : Events

Come Learn at SQL Intersection, Get a Surface Tablet - This spring I’m speaking at the SQL Intersection conference. It’s a new conference, managed by SQLskills founders Kimberly Tripp and Paul Randal. It’s April 8-11 in Las Vegas, and it’s got a slew of amazing speakers that I’ll be alongside, including Brent Ozar, Grant Fritchey, and more....(more)

Time to Chat - The greatest part of my job is that I get to travel all over the world to present different technical sessions. But, it’s not the presentations that make it cool. It’s the fact that I get to meet people. I get a chance to hang out with my #sqlfamily. I get a chance to make new friendships. Those contacts are amazing. I love the opportunity to sit down and talk to people about what they’re doing with technology, the challenges they face, what’s common with my own experience, what’s different. From all that, I get a chance to grow and learn. Sometimes I even get the chance to help people....(more)

User Group Marketing - I blogged about this back in September of 2010, but technology changes so it’s time to revisit this again.  Now I’m not anything even close to a marketing expert, but I’ve been the Director of Logistics and Communications for the North Texas SQL Server User Group for the past 2 years (I just started a new 2 year term as the Director of Programs).  I’ve also been the Director of Marketing for the PASS Performance Virtual Chapter for the last year until becoming the President this year.  Since I started those positions, NTSSUG’s average has gone from around 50 to around 80 (in fact, we had 87 last month), and the virtual chapter went from 40 in January to 235 last month.  Now that’s what I call exciting.  I’ve helped put on a SQLRally, several SQLSaturdays, an all-day virtual training event, and currently serve as a PASS Regional Mentor for the South Central region.  So I’m not a marketing expert, but I have gained some experience with all my volunteer activities....(more)

Blogs : Excel

Exploring Excel 2013 for BI Tip #3: Flash Fill - As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!...(more)

Blogs : FILESTREAM Data

FileTable: SQL Server 2012's little gasp-maker - When was the last time you looked at a SQL Server new feature list and said, “Ohhh, WOW!”?  Was it around March of last year? No? Then you might have missed something really, really cool....(more)

Blogs : High Availability/Disaster Recovery

Log Shipping Part 2: When Disaster Strikes (video) - Log shipping is a tried and true method in SQL Server for keeping a copy of your data on a secondary server. You have your primary and secondary server set up, and it’s working great. Are you monitoring it? Do you know what to do if you need to fail over to the secondary? Join Jes in this free 30 minute video to find out!...(more)

Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database - T-SQL Tuesday #40 It’s been a tough and long road to 31 Days of Disaster Recovery. It’s been very difficult coming up with quality topic ideas for the series as we near the end. For day 30 of the series, I am combining a post on performing piecemeal restores with a post on filegroups for ...(more)

Blogs : Integration Services/ETL

SSIS-Encrypt data while in transit - A coworker asked if I had any experience with encrypting data while in transit with SSIS. Their client specifies that PII data must be protected at rest and in flight. They enforce the data at rest through something, it's not my client so I don't know the particulars but we'll be pulling data like a social security number, SSN, out of the financial system and into the data warehouse and while that data is between places, our ETL needs to safeguard that data. ...(more)

Using Object Typed Variables in SSIS - Note: This will be the first post in a short series on using Object typed variables in SQL Server Integration Services....(more)

SSIS 2012 Deadlock When Running SSIS from SQL Agent Job - I recently had an interesting situation where an SSIS package was scheduled to run at 1 AM but failed with a deadlock message. This seems a bit strange given that it was a deadlock on the step executing the SSIS package, not an error message from within the package. There was no history of a failure in the SSIS execution logs, just the job failure. As you can see, the error only happened a couple of times even though all the 1:00 AM runs were at the same time as the other job....(more)

Blogs : MDX

MDX #27–Who are Ascendants? - Many function names in MDX are very family-friendly. Children, parent, ancestors, descendants, ascendants, are all terms we often use in telling family stories. Except ascendants. I never really understand what this word means until I ran into this word in MDX....(more)

Blogs : Performance and Tuning

Find missing indexes using SQL Servers index related DMVs - Today, we experienced performance issues with few databases that are hosted on one of our most critical production SQL Server. Upon reviewing the query execution plan and querying index related dynamic management views (DMVs), I noticed the problem is related with potential missing indexes on columns. The index related dynamic management views (DMVs) I queried are as follow:...(more)

Index Tuning - Leave a comment on the original post [sqlserver365.blogspot.com, opens in a new window] ...(more)

Profiling PowerShell cmdlets with ANTS Performance Profiler - Following the early lead of Exchange Server 2007, Citrix’s XenDesktop SDK (which I was fortunate enough to work on), and now the Azure platform, PowerShell is being used more and more to provide the scriptable interface to proprietary systems....(more)

Dissecting SQL Server physical reads with Extended Events and Process monitor - In this blog post I’ll (re)introduce you to a really neat tool to use alongside of SQL Server and use this tool to show you how SQL Server is handling IO under the covers.  We will view the IOs as they occur, and then tie the IO back to the pages that we pull into the buffer pool.  ...(more)

Join Reordering and Bushy Plans - Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required. ...(more)

Execution Plan Analysis: The Mystery Work Table - I love SQL Server execution plans. It is often easy to spot the cause of a performance problem just by looking at one. The task is considerably easier if the plan includes run-time information (a so-called ‘actual’ execution plan), but even a compiled plan can be very useful. Nevertheless, there are still times where the execution plan does not tell the whole story, and we need to think more deeply about query execution to really understand a performance problem. This post looks at one such example, based on a recent question posted on the SQL Performance Q & A site....(more)

Dropping Indexes - While working on some demos recently, I needed to drop an index for a test. I executed this generic statement for an index I’d just created....(more)

switchoffset built-in function can cause incorrect cardinality estimate - Recently, we received a call from a customer reported that a query was slow.  Upon further investigation, his query has a predicate that look like this:...(more)

Optimizing Massive SQL Joins - Scenario: Run ETL to perform a full data warehouse load. One of the steps requires joining four biggish tables in a stating database with 1:M logical relationships. The tables have the following counts: ...(more)

SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek - “Don’t use functions in the WHERE clause, they reduce performance.” I hear this quite often. This is true but this subject is hard to understand in a single statement. Let us see what it means and how to use the function in the WHERE clause....(more)

Custom Metric: Number of queries running - This metric counts how many queries are running, and alerts you if the value goes above a defined threshold. If an alert is raised, you can run the query manually against the database server to see what is happening....(more)

Blogs : Reporting Services

SSRS – Using a List Item to Display Details - Add a Shared Dataset named StudentDetails that uses the data source from step 4, and use the following query as the source:...(more)

Blogs : Security and Auditing

Should websites be required to publicly disclose their password storage strategy? - I don’t know how Evernote stored my password, you know, the one they think might have been accessed by masked assassins (or the digital equivalent thereof). I mean I know that their measures are robust but then again, so were Tesco’s and according to their definition, “robust” means storing them in plain text behind a website riddled with XSS and SQL injection (among other security faux pas)....(more)

Script to clean up “Windows” logins no longer in AD - ...(more)

Is Software Security a Waste of Money? - To: Bruce Schneier and othersCounterpoint: Value of software security for small businesses I agree with what I think Viega is *trying* to say, but not what he said (i'll call that the broader claim). Let's say what he said in a nutshell: a process like Microsoft's SDL, with all its labor and costs, makes no sense for smaller businesses. This is entirely true. However, it would be false to state that SDL is representative of secure or reliable software development in general. Further, most user's security expectations are minimum: don't corrupt the data; basic confidentiality protection; good service availability; best practices for rest. It doesn't take a huge SDL to accomplish this....(more)

Blogs : Software Development

Test Better - Developers take pride in speaking their mind and not shying away from touchy subjects. Yet there is one subject makes many developers uncomfortable....(more)

Blogs : SQL CLR

Security and Code Signing in SQL CLR - Before we publish our newly created SQL CLR Stored Procedure, we need to take a moment to discuss security.  SQL Server is a highly-protected environment.  As Database Administrators, we strive to work based off of least-privilege principles to keep the environment’s data secure and trustworthy.  Introducing a completely new set of code, running inside of our environment, should make us all take particular caution.  Unfortunately, there is quite a lot of debate across the internet about this, in which one side is saying, “Don’t worry about nitpicking over signed vs. unsigned code.  Protecting the front door is your real concern.”  While, yes, the front door (logins, roles, so forth) is often left vulnerable, shouldn’t we protect the  back door, especially when it is a NEW back door that we are opening willfully?...(more)

Deployment and Usage of a CLR Stored Procedure - Once we have setup our environment properly, written the code we are wanting to deploy, and decided how we are going to handle code security for SQL CLR, the remaining steps are very straightforward.  Unless we are referencing other libraries that are not part of .NET, but that will be discussed in detail later....(more)

Blogs : SQL Server 2012

Databases Five Years from Today - Five years from now, in March 2018, what will be different about databases?  I’m a Microsoft SQL Server guy, so keep that in mind when reading my thoughts....(more)

Blogs : T-SQL

SQL Server 2012 Window Function Basics - For some time, Microsoft had a few window functions, but not the full set specified in the SQL 2003 standard. Now, in SQL Server 2012 we have the whole range, and extremely useful they are too. There's no longer an excuse to avoid them, particularly now you have Rob's gentle introduction....(more)

Break large delete operations into chunks - Far too often I see folks complaining about how their transaction log took over their hard disk. Many times it turns out that they were performing a massive data operation, such as deleting or archiving data, in one large transaction....(more)

Making sure your Triggers fire when they should - As some of you may be aware, triggers are not my favourite thing in the world but like most things, it does have its place....(more)

A TOP Query - For the blog post that I’ll publish tomorrow, I wrote a query that I thought needed a blog post all of its own. This is that post....(more)

SQL SERVER – How to Add Column at Specific Location in Table - Recently I noticed a very interesting question on Stackoverflow. A user wanted to add a particular column between two of the rows. He had a experience with MySQL so he was attempting following syntax. Following syntax will throw an error....(more)

Blogs : XML, XPATH and XQUERY

Retrieving the next or previous element node value in an Xml blob using XQuery - It’s been a little while since I wrote a blog post on my favourite subject but this is from a thread on the SSC Xml forum that I was helping out on a couple of weeks ago. I’ve been a bit busy lately with a big project at work, revising/taking exam 70-461 and getting ready for the birth of my second child! But this thread seemed too good a subject for me to miss quickly get a post out showing how I might approach this particular problem....(more)

Ad-Hoc XML File Querying - When you need to shred just part of the data within a large XML file into a SQL Server table, the most efficient way is to just select what you need via XQuery or by using XPath, before shredding it into a table. But precisely how would you do that?...(more)


Administrative