SSIS Talk

It would seem that the DATAllegro acquisition is complete as Microsoft has shipped its first technology preview of the project code-named “Madison” to ten customers for their feedback.

Madison looks to be quite the offering, allowing for rapid growth of a data warehouse architecture because of its “appliance” model as well as integration with existing SQL 2008 instances, minimizing time-to-market. Customers have reported loading over a terabyte of data within an hour using Madison and over 1.5 terabytes of query processing per minute.

When you have a chance, stroll on over to the Madison Web page to read more about it: http://www.microsoft.com/sqlserver/2008/en/us/madison.aspx

Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
(more…)

Simon Sabin, fellow SQL MVP, has posted on the topic of formatting SQL.

For me, I align my queries vertically like so:

select track,
       level,
       title,
       Name,
       ss.length 

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId 

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4 

 order by length,
          title,
          cs.SessionId desc

If I have multiple join predicates, I continue the indention pattern:

select track,
       level,
       title,
       Name,
       ss.length

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId
   and (ss.approved  = 1
        or ss.track  = 'DBA')  

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4

order by length,
         title,
         cs.SessionId desc

What is your SQL formatting style?

SQL Server 2008 brings with it the ability to change the color of the status bar in Management Studio per your choosing. This is especially good to distinguish between production and non-production machines, or if you simply wish to have a different color other than the default gray.

However, the only way to do this is in the properties of a server when registering it under Registered Servers (Ctrl-Alt-G). Add a new server and in the connection properties, select the color you wish for this particular server.

I have a short video demonstrating it:

• Flash video
• iPod video

For those of you that have access to the SQL Server 2008 distribution via the various channels, please pay attention to the Microsoft KB article which will likely apply to your installation experience until Visual Studio 2008 (Orcas) SP1 has shipped: http://support.microsoft.com/kb/956139

Basically, if you want to install SQL Server 2008, go ahead. If you want to install SSIS, SSRS, SSAS, and a few other tools, you’re going to have to wait until VS 2008 SP1 has shipped and is installed on your machine before going through the SQL Server 2008 setup. Also note, that the warning in the KB article appears quite late in the SQL Server 2008 setup process, so you have been warned!

The code for SQL Server 2008 (Katmai) has made it to the RTM phase, according to the closing keynote at TechEd South Africa. This essentially locks up development for this version. Downloads should be available soon to MSDN subscribers and trial availability relatively soon for non-MSDN subscribers. Physical media and availability in retail outlets will be a bit longer (date unknown).

Press release here: http://www.microsoft.com/presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

If you expose your SQL Server databases to the Internet via an application layer and you are the person that manages, writes, or supports the Web code, you need to read this:

From Buck Woody, Program Manager:

You might have read recently that there have been ongoing SQL injection attacks against vulnerable web applications occurring over the last few months. These attacks have received recurring attention in the press as they pop up in various geographies around the world. These attacks do not leverage any SQL Server vulnerabilities or any un-patched vulnerabilities in any Microsoft product – the attack vector is vulnerable custom applications. In fact, SQL Injection is a coding issue that can attack any database system, so it’s a good idea to learn how to defend against them.

In order to help you respond to and defend yourself from these attacks, Microsoft has an authoritative blog including talking points and guidance. You can find this at http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx.

Please read through the included link to the TechNet article. READ IT!

Steve Kass also talks about this in a bit more detail along with a sister vulnerability, the HTML injection: http://stevekass.com/2008/05/31/read-this-if-you-serve-up-web-pages-from-sql-data/

From Redmond, WA and the MVP Summit:

It’s official – Microsoft will be delivering Service Pack 3 in, hopefully, Q4 2008.

Basically, they will be working on and delivering SP3 after SQL Server 2008 is released this year. So, let’s applaud Microsoft for listening to our wishes, and remember that SQL Server 2008 is their top priority – and rightly so.

Some of you may have visited the link to Microsoft’s whitepaper on the BI Metadata Toolkit only to find the referenced samples download link is not working. Many of us have always had a copy of the whitepaper, but not the samples download. We’ve received that missing download and are sharing it here. The folks at Microsoft are aware of the bad samples download link and are working to get that resolved.

The whitepaper focuses on sharing metadata between Microsoft’s BI tools and is a very good read. It touches on what is “metadata,” how can you use it, where to use it, and most importantly *why* you’d want to use it. Sarbanes-Oxley is mentioned and is important to consider when deploying SSIS solutions. Sarbanes-Oxley (a.k.a. the knee-jerk reaction to the Enron debacle), while broad in nature, basically boils down to enforcing accountability and separation of duties. The BI Metadata Toolkit can help in ensuring SOX compliance.

As part of the samples download, Microsoft includes the following tools:

• Dependency Analyzer: loads into a database lineage data between SSIS, SSAS, and SQL Server
• Dependecy Viewer: graphically see dependencies from the above tool
• Data Source View: A DSV for use in Reporting Services (SSRS) to view dependency repository
• Lineage Repository: A database called SSIS_META that can be used to house metadata from nearly any system
• Reports
• Report Model: For use with Report Builder to allow creating ad-hoc reports
• Integration Services Samples: Sample packages to start auditing and viewing lineage on

BI Metadata Toolkit Whitepaper (.doc – 994 kb)
BI Metadata Toolkit Samples (.msi – 311 kb)

Here are some new*, upgraded limits as they apply to tables in SQL Server 2008:

• Column limit per base table: 30,000 columns, which is up from 1,024 columns
• Index limit: 1,000 indexes, which is up from 249
• Statistics limit: 30,000 statistics, which is up from 2,000

These limits are still bound to the row size limit which is 8,060 bytes; however if a table has sparse columns in it, then that limit is reduced to 8,018 bytes.

*Note: These are not yet available in the CTPs but should be in the CTP6 refresh.