Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show. Here is what I found interesting this past week in SQL Land: SQL Server 2008 Auditing Resources Lara Rubbelke points us to some good SQL Server 2008 Auditing Resources Spatial Indexes in SP1, almost no hints required Bob Beauchemin is letting us know that with SQL Server 2008 Service pack 1 Spatial Indexes require almost no hints at all

I was listening to show number 426 on dotnetrocks: Rob Boucher on Application Architecture Guidance! They mentioned the Patterns And Practices Application Architecture Guide 2.0, this guide is available for free on codeplex. Although it is a Microsoft technology centric guide, there should be chapters for every developer in your group. Here is one example from the book Key Design Principles When getting started with your design, bear in mind the key principles that will help you to create architecture that meets “best practices,” minimizes costs and maintenance requirements, and promotes usability and extendibility.

Scott Guthrie (AKA The man!) has posted on his blog that you can download a free ASP.NET MVC eBook Tutorial in pdf format. The book is licensed under a “Creative Commons Attribution No Derivatives” license – this means you can share, distribute, print, or hand it out to anyone. This is what is covered in the book _Create a database Build a model with validation and business rules Implement data listing/details UI on a site using Controllers and Views

For quite a while, now my signature and IM status message has stated, “Backups are for sissies!!!”. Recently I had a coworker laugh about this when they saw it on Google chat. They thought it was hilarious. I couldn’t help but stop them and explain how the statement really wasn’t a joke and in reality was probably the most serious statement I’ve ever branded to my online handle. The true meaning goes deep into disaster and recovery of the systems I manage and put my reputation on the line daily by reaching uptime levels that surpass expectations.

Securing data services is part of a DBA’s critical tasks that should be performed. This goes beyond users, roles and schemas though. A DBA must take into account the concept of software installations in a growing and dynamic environment that often pose serious security threats. It’s very common for software to ship with the desktop version of SQL Server (MSDE2000 or SQL Express). This can be prepackaged and installed, configured and user created on the fly during installation.

You have an identity column in your database that was created as int identity(1,1). But after a good few years you are approaching the limit for a signed four-byte long integer, 2147483647. What do you do? Switch the column to bigint? Not yet! You do something like this: --What's the biggest signed four-byte long: --select power(convert(bigint, 2), 31) - 1 --2147483647 --what your table looks like CREATE TABLE tester ( testerid INT IDENTITY(1, 1) not null CONSTRAINT pk_tester PRIMARY KEY CLUSTERED, descr VARCHAR(100) ) -- simulate the identity column being near the end of its life (only 32 more rows will fit) DBCC checkident(tester, reseed, 2147483616) -- push the identity column to its absolute limit INSERT tester SELECT CONVERT(VARCHAR(100), newid()) WHILE @@ROWCOUNT < 16 INSERT tester SELECT CONVERT(VARCHAR(100), newid()) FROM tester SELECT * FROM tester -- you can see that you can't insert another one like so: -- insert tester select convert(varchar(100), newid()) --Redefine the identity column by moving tables in and out.

Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show. Here is what I found interesting this past week in SQL Land: How To Get High Quality Information About Query Performance Elisabeth Redei shows us how you can use the profiler, dynamic management views and SQLNexus to get high quality information about your query performance How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components

I read lots of blogs, More then 40 in total. And sometimes you find one that has an interesting post and then I add that to my google reader list. The next post I found via Jason bock’s blog. It’s about exception handling and the filter posibilities VB.Net has, well actualy the abality is there in the CLR it’s just no implemented in C# as of yet. And not any time soon as it seems.

Last week I got a new computer at work. A real beast I might add. I already wrote about what I wanted a while ago. But after a nice email conversation with the hardware supplier it actually became a bit better than that. Let’s start with the case. A Coolermaster Cosmos 1000. This is a wonderfull case, nice and big and nice and quiet and nice and cool. Even with 5 fans in it you hardly hear it.

So you want to spit out some XML from SQL Server into a file, how can you do that? There are a couple of ways, I will show you how you can do it with SSIS. In the SSIS package you need an Execute SQL Task and a Script Task. Let’s get started First create and populate these two tables in your database create table Artist (ArtistID int primary key not null, ArtistName varchar(38)) go create table Album(AlbumID int primary key not null, ArtistID int not null, AlbumName varchar(100) not null, YearReleased smallint not null) go insert into Artist values(1,'Pink Floyd') insert into Artist values(2,'Incubus') insert into Artist values(3,'Prince') insert into Album values(1,1,'Wish You Were Here',1975) insert into Album values(2,1,'The Wall',1979) insert into Album values(3,3,'Purple Rain',1984) insert into Album values(4,3,'Lotusflow3r',2009) insert into Album values(5,3,'1999',1982) insert into Album values(6,2,'Morning View',2001) insert into Album values(7,2,'Light Grenades',2006) Now create this proc