Background

Aptera Software Inc. has frequently been tasked with helping to identify and provide our valued clients with appropriate recommendations for development, maintenance, and administration standards relating to SQL Server's Integration Services component. These standards, compiled over time, are based on detailed analysis of the clients' needs, practices, and requirements, as well as Aptera Software's pool of knowledge and Microsoft Corporation's own guidelines and whitepapers. The ideas provided in this publication are not "set in stone" and should be adapted to fit any specific business and technology needs.

SQL Server Integration Services (SSIS) Standards and Best Practices

Integration Services is ...

Once of the common pain points for me as a developer is deploying database changes from my development environment (generally my laptop) out to a test/staging server.  It's not always a great idea to just blow away the test/staging database and replace it with my local copy, as there may be other things in place that I don't want to get rid of.  Besides, there are usually differences between the security/user configurations on my local machine as compared to my test/staging servers.

I was working on a project the other day and I needed to migrate/convert some excellent .NET code into a T-SQL stored procedure.  Not a terribly daunting task, but a couple of speedbumps along the way.  One such speedbump came when converting some code that used DateTime.Date and DateTime.DaysInMonth methods.  I was also creating new DateTimes based on the month and year of a given DateTime to get a “first of the month” datetime.

After a litle time spent on a un-named search engine, I found some code that makes clever use of T-SQL datetime math to achieve the results I was looking for.  After some analysis, I ...

In SQL Server, an execution plan is a calculation of the most efficient way to perform a query (or set of queries).  So the database engine doesn't have to recalculate the optimal execution plan each time the query is requested, there is Execution Plan Caching.  The cache is just part of the database memory pool that stores the query plan and execution context.

It sounds great, but there is the potential to create a problem.  As the database schema evolves and the actual data changes, it's possible that some cached execution plans are no longer optimal and need to be recalculated.

A recent project involved an database ...

Microsoft has released SQL Server 2008 Service Pack 1.  This is a bug fix release that consolidates Cumulative Updates 1 - 3, and all other released fixes.  There are however a couple new features.

• You're now able to Slipstream service packs and hotfixes into the base installation files, and then install everything in a single step.
• You can uninstall a service pack without uninstalling the instance.
• The ClickOnce version of Report Builder 2.0 is included. 

Download the service pack from here:

SQL Server 2008 Service Pack 1

Release Notes

List of the bugs that are fixed in SQL Server 2008 Service ...