Welcome to Database Weekly

SQL Injection Attacks Unfortunately there are gaping holes in the security of some web sites that are being aggressively exploited by a form of hacker attack called SQL Injection. Even more worrying is that this attack can be carried out against fully patched databases. It is not a problem with patches, more an issue with the way databases work and how a system has been designed. read more... Designing Efficient SQL: A Visual Approach Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show off all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables. read more... Exploring your database schema with SQL if you are doing a review of a development database, there are a number of facts you’ll need to establish regarding the database, its tables, keys and indexes, in order to home-in on any possible problem areas. The Catalog views offer just about every piece of metadata that SQL Server currently exposes to the user. read more... The Comic That Edifies Despite Your Best Efforts: The Trojan Horse Excuse me, was that geek or Greek? read more... The Comic that puts Everybody First: Robotic Resources Derek the DBA is judged by a jury of his peers read more... NOT EXISTS vs NOT IN The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows. read more... Thoughts on how PowerPivot and SSAS could work together Here’s a post on how PowerPivot and ‘traditional’ SSAS could be integrated. Here’s my basic idea: let us be able to build regular SSAS cubes using PowerPivot models as data sources, with SSAS working in something similar to ROLAP mode so every request for data from the cube is translated into an MDX (or SQL – remember SSAS, and presumably PowerPivot, supports a basic version of SQL) query against the PowerPivot model. read more... Bad habits to kick : creating the uber-view The one-size-fits-all view: you've seen them, you've had to use them, you may have even created them. Why do we use them? Convenience, laziness, you name it. read more... Configuring Database Mirroring in SQL Server In order to prepare for mirroring at a minimum you need a full backup of the principal database and a transaction log backup of the principal database, both of these files need to be restored creating the mirrored database on the secondary instance. read more... Energizer USB Charger Software Contains Malware the Energizer DUO USB battery charger is a vehicle for attacks on PCs, according to the Department of Homeland Security's Computer Emergency Readiness Team. The software that installs with the Energizer charger contains a Trojan horse that gives malicious hackers a back door into Windows machines. read more... Why abbreviate schema names? Do you abbreviate the names of schemas in SQL Server? I ask because I see that a lot of people do and quite frankly I don’t really see a justification for it. From what I hear from others, abbreviating schema names isn’t commonly practiced but that doesn’t jive with what I see in my work day-in day-out. read more... Easy Ways to Detect I/O Pressure in SQL Server 2008 As a database professional, you need to be able to detect when your SQL Server instances are experiencing poor performance due to I/O bottlenecks. I have put together a small collection of queries to do this. Even if you are not able to run PerfMon (because you don’t enough rights on the database server itself), you can still run these queries to get a pretty good idea what is going on from an I/O perspective. read more... Dealing with Large Queues Just like tables, queues may require maintenance operations. But unlike tables, the DBA has no DDL at its disposal to do the job, except the DBCC DBREINDEX on the internal table, run from the DAC connection, which is a hack: a deprecated command, the DAC requirement, the internal table name digg from metadata… Hopefully, the problem will be addressed eventually and ALTER QUEUE … REINDEX and ALTER QUEUE … REORGANIZE will make it to the product. read more... SQL Server Agent Job Status The purpose of this proc is to allow you to check job status programmability and allow that programmatic checking to do something with the status. This will allow us to create a SQL Server Agent job calling process that can wait for the job to stop prior to calling the next step read more... Inside sys.dm_db_index_physical_stats The idea of the DMV is to display physical attributes of indexes- to do this it has to scan the pages comprising the index, calculating statistics as it goes. Many DMVs support what's called predicate pushdown, which means if you specify a WHERE clause, the DMV takes that into account as it prepares the information. This DMV doesn't. If you ask it for only the indexes in the database that have logical fragmentation > 30%, it will scan all the indexes, and then just tell you about those meeting your criteria. read more... Google Public Data Explorer Google made yet another tentative step into the world of web-based BI with the launch of Public Data Explorer, a tool for analysing at least some of those public data sets that have been appearing thick and fast over the last year or so. Although it’s very fairly basic compared to other tools out there, it’s actually quite fun and much better than other Google efforts in this area like Fusion Tables. read more... Solid State Drive Performance Hands-On: Part 1 Disk performance has gotten slower in relative comparison to the improvements made in CPU and memory over the last 30 years. So much slower, in fact, that some of the smartest people on the planet are spending their time trying to figure out how to store data differently to get around the disk bottleneck. SSDs will really change our lives and provide "the only real hope" for fixing the disk performance issues. read more... SQLIse; A Powershell Based SQL Server Query Tool SQL Server Powershell Powershell Extensions (SQLPSX) has been updated to version 2.1. The most notable change is the addition of a Powershell Integrated Scripting Editor (ISE) module called SQLIse (pronounced “SQL Ice”). The module provides a basic IDE for T-SQL that includes the ability to edit, execute, parse and format SQL code from within Powershell ISE. read more... Service Broker Sample activated application The sample assumes a usage scenario where the activated application is used to offload some CPU-intensive computation from Sql Server. Whenever there’s some work to be done, Sql Server creates a conversation and sends a RequestMessage on it. The message contains some application-specific binary payload. read more... Top 6 Myths of Transaction Logs I think there is a lot of misunderstanding concerning database transaction logs in SQL Server. I had intended to write this as the top 5 myths, but I wasn't able to narrow the list down that far. read more...