Posts Tagged ‘SSIS’

My Favourite SQL Server Features

SQL Server MVP Ken Simmons is offering the chance to win one of three MSDN subscriptions on his blog, I thought I’d join in by writing a post describing my favourite features of SQL Server.

The ‘big ticket’ item for me is SQL Server Integration Services. For those of you stumbling across this post from non-SQL backgrounds SSIS is Microsoft’s ETL tool and has been bundled with the database engine since SQL Server 2005. Essentially SSIS allows you to extract data from (or deliver data to) a range of ODBC, OLE DB, ADO.NET or flat file data sources including Oracle, DB2, MySQL, Excel, CSV files and of course – Microsoft SQL Server. Along the way you can make use of a range of tasks and components that allow you to transform the data and perform operations such as looping, FTP, Email, Lookup Values, Data Mining, Aggregation, Process Dimensions and many many more .  There are alternatives out there but many are prohibitively expensive for the small-medium enterprise (e.g. Informatica, BODI) – that SSIS comes bundled with SQL Server still astonishes me to this day.

As for my favourite ‘little’ features I’d have include:

Linked Servers, a few people give it a bad rap since they can be misused but I’ve made great use of Linked Servers to simply data assurance processes and join data in queries running across both MySQL and Sybase data sources in a single statement.

Copy and Paste Coloured SQL, I’m not sure if it’s just because I’m used to Management Studio but I often struggle to read SQL if it’s not coloured-in. Colouring SQL improves readability and since SQL Server 2005 I’ve enjoyed the ability to copy and paste directly from Management Studio into emails or word documents and retain the coloured keywords, it makes documentation much more interesting.

Common Table Expressions, it took me a while to get into the swing of using CTEs and then for a while I probably over-used them before settling down. They’re often a great way to make statements with nested subqueries much more readable and much more elegant in design but whenever I go back to SQL Server 2000 or MySQL I really miss CTEs.

SQL Server Express, it might be a bit of a cheat to include an edition as a feature but the fact that the core SQL Server database engine is available for free is brilliant and allows a lot more people to try out SQL Server and see the difference between a slick GUI like Management Studio Express vs the MySQL toolset.

Intellisense, I couldn’t resist choosing this as one of the more modern features I love about SQL Server. Once you’ve gotten used to it Intellisense makes trotting out T-SQL statements a breeze and you know what? It just feels cool, albeit in a really geeky sense of the term.

SQLBits V: Highlights from Friday Afternoon

Having attended the SQL Bits conference in Newport last month I decided to collate the little tips and tricks I learned at the event, in the challenging post-lunch session on Friday Thomas Kejser gave a talk on Designing I/O Systems for SQL Server – my notes from this talk are either short, sharp facts or references to thing I plan to research further…

The most important metrics are Throughput (measured in MB/sec or IOPS) and Latency (milliseconds).

A 10K RPM disk will give 100-130 IOPS, a 15K RPM disk will give 150-180 IOPS.

Performance can be increased by short-stroking disks, if you build a single partition on a drive using only 10% of it’s capacity this partition will reside physically on the outer edge of the disk.  Doing this reduces the amount of head movement required to read the data and improves performance.

Windows Server 2008 handles disk alignment automatically but for earlier versions the  offset should be 1024kb.

For testing the effect of configurations on performance use SQLIO.EXE or IOMeter, for stability testing use SQLIOSIM.

Check out the Pre-Deployment Best Practices Whitepaper from Microsoft.

The last talk of the day was by Allan Mitchell and was initially titled Common Integration Services Problems but was changed late-on to a more introductory Solving Problems in ETL using SSIS.  The only take-away I got from the talk was to check out a tool called BIDS Helper, I have no criticism of Allan though – he’s a good speaker but in this case I already understood the bulk of the content so it was hard to stay focused.