Welcome to Database Weekly

SSRS - Custom Code with External Assemblies In a previous post I wrote about how you can use embedded custom code to extend the capabilities of Reporting Services. This week I will show you another method of using custom code but this time with external assemblies. Ideally when using custom code you would choose to do so using external assemblies. External assemblies help developers manage code from outside Reporting Services and share the exact same code across multiple report. Here’s a few of the pros and cons of using external assemblies for custom code. read more... Introduction to XML DML (Part 2) - Using the modify() method: replace value of This is the second part of a 3 part blog which will attempt to show some different ways of how you can use the xml modify() method to update the contents of an XML document. This part, starting at the absolute basics, will cover the ability to update data in an existing XML document read more... Rebuild Your Indexes Online (When You Can) So I was recently reading SQL Server’s blog, specifically the article Customers using SQL Server 2012 today! and some brave businesses are already using and enjoying some of SQL Server 2012 features that help their databases stay available. I’m excited about the new features too. For example, index rebuilds have been improved. We can now rebuild indexes online that include “blob” columns (like nvarchar(max), image, etc…). This means that (almost) every index can be rebuilt without requiring a table lock for the entire operation. And that’s good news for availability! read more... Microsoft's plan for Hadoop and big data Microsoft has placed Apache Hadoop at the core of its big data strategy. It's a move that might seem surprising to the casual observer, being a somewhat enthusiastic adoption of a significant open source product. read more... SQL Spatial Puzzle #3: The Seven Bridges of Königsberg The old city of Königsberg, capital of East Prussia (now Kaliningrad), was built on either side of the river Pregel, with seven bridges across the river between four separate landmasses. A problem made famous by Swiss mathematician Leonard Euler is to try to find a route around the city that crosses every bridge once and only once. read more... Ring Orientation, Bigger-than-a-Hemisphere Polygons, and the ReorientObject method in SQL Server 2012 If you’ve ever tried to import spatial data (from an ESRI shapefile, say) into SQL Server, you’ve almost certainly encountered the dreaded “bigger than a hemisphere” error, as follows: read more... SQL under the hood (Part 2): Estimate the SQL Disk IO Workload based on the Virtual File Stats DMV. Do you know how much data is being written and read on a daily basis by your SQL Server databases? SQL Server keeps track of these stats for you already; you just have to retrieve and interpret them correctly so you can turn this data into valuable information. read more... Europe proposes a "right to be forgotten" pean Union Justice Commissioner Viviane Reding has proposed a sweeping reform of the EU's data protection rules, claiming that the proposed rules will both cost less for governments and corporations to administer and simultaneously strengthen online privacy rights. read more... Feature: The Great Disk Drive in the Sky: How Web giants store big—and we mean big—data Consider the tech it takes to back the search box on Google's home page: behind the algorithms, the cached search terms, and the other features that spring to life as you type in a query sits a data store that essentially contains a full-text snapshot of most of the Web. While you and thousands of other people are simultaneously submitting searches, that snapshot is constantly being updated with a firehose of changes. At the same time, the data is being processed by thousands of individual server processes, each doing everything from figuring out which contextual ads you will be served to determining in what order to cough up search results. read more... Join us March 7, 2012 for the Virtual Launch of SQL Server 2012! On March 7, 2012 we are hosting the SQL Server 2012 Virtual Launch Event (VLE), to share the latest on SQL Server 2012 and the evolution of the Microsoft data platform. Through our VLE, anyone, anywhere in the world can simply log in and be a part of this amazing experience – consuming content at your own pace while still experiencing all the benefits of a tradeshow event. read more... Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. read more... T-SQL stored procedure for finding/replacing strings in a text file. Really I know people have been doing all sorts of things with T-SQL, and I have absolutely no issue with people trying to push the limit of what T-SQL can do, or what you can use it to accomplish, especially when it’s for demonstration or pedagogical purposes, or as an intellectual exercise. But then I bumped into an article on writing a T-SQL stored procedure to find and replace strings in a text file. read more... Threatened by Anonymous, Symantec tells users to pull pcAnywhere's plug Symantec this week took the highly unusual step of telling users of its pcAnywhere remote access software to disable or uninstall the software while it fixes an unknown number of bugs. Security experts said the move was unprecedented for a company of Symantec's size. read more... Database Password Storage Points To Need For Better ID Management Salted digests are good, but getting away from storing passwords in unsecured databases could be a better option read more... Read contents of a file to variable – SSIS Sometimes you will find the need to import a file to a variable using SSIS. In this post, I’ll use Script Task to read contents of a flat file into a variable. read more... Slammer, Alive…Barely By now you must have heard of the SQL Slammer worm. It was quite an infectious little nuisance. The harm it caused came largely due to unpatched, unprotected SQL Servers. read more... Improving cube processing time As your SSAS cube gets bigger, cube processing time will become a problem. This is especially true as more and more companies want cube processing during the day instead of the usual off-hours time when no one is using the cube. Partitioning the cube can help to reduce the processing time. So can using a different processing strategy than a Process Full. read more... Performance impact: hyperthreading on Intel Westmere-EP processors (X5690) Recently, I have been looking into the performance impact of enabling hyperthreading on various platforms with various SQL Server workloads. All the results I have shared so far are from a DL580 G7 with four Westmere-EX (E7-4870) processors. Overall, the results of enabling hyperthreading have been positive for both the tested reporting queries and the tested OLTP queries, although I did run into one exception where a reporting query workload performed better without hyperthreading . read more... Bad Habits to Kick : Using AS instead of = for column aliases This one is quite subjective, and I'm sure I will face plenty of opposition - not only because it's a preference thing and many people are married to their preferences, but also because it violates the strict interpretation of the standard. Personally, I'm more worried about the former than the latter - I have no concerns whatsoever that SQL Server will eradicate the = notation for column aliases, read more... Performance impact: SQL2008 R2 audit and trace We are told that SQL Server 2008 R2 Audit (and SQL Server 2008 Audit) has much less performance overhead than SQL Trace when we try to capture the same information. Knowing how SQL Server R2 Audit is implemented (i.e. on top of the extended events infrastructure), I’ve always taken that as a given and never bothered to check it out. Recently, I had to capture some object access information, and it turned out that SQL Audit was not the most convenient tool for the task read more...