One of the great features of SQL Server 2008 is Filestream data support. I wrote a blog entry on this option which allows us to store large data objects outside of the database on the file system while benefiting from database transaction processing and synchronized disaster recovery. Configuration of this option has changed since the Nov 2007 CTP which "threw me for a loop" when I started testing the feature again this week with SQL Server 2008 RC0. Filestream data support is disabled by default. So how exactly do you configure it?

Read more

Recently, I have been teaching a lot of Internet-based classes using a remote learning technology called the "Virtual Classroom". The "Physical Classroom" is the traditional method of education where we all congregate in the same physical location and do some collaboration and learning. The trouble is, many times we have to travel to that physical location. This involves rising travel costs as well as time away from family and friends. Remote learning offers an alternative solution but can it offer the same educational experience (or better)?

Read more

I have been testing SQL Server 2008 RC0 (Release Candidate Zero) that was made available recently and I must admit I am beginning to like it. Even the new logo is growing on me. There are many new features that I am just beginning to test out as new builds become available. One of those features is the Multi-Server Query in SQL Server Management Studio.

SQL Server 2005, being a five-year release, included many architecture changes across the board and famously had so many new features that, in order to fit them all on one PowerPoint slide, Microsoft had to use a 10-point font! Well, in the words of David Campbell, General Manager of SQL Server at Microsoft, the SQL Server 2008 release, nicknamed "Katmai", is a "14-point font" release!

Read more

Have you ever received unwanted catalogs? Or multiple catalogs from the same company? Well, there's a cool new website that will help you eliminate unwanted catalogs to simplify your life and save natural resources. Sound good? Here it is http://www.catalogchoice.org/. But why do we get multiple catalogs from the same company? Even wanted ones...The answer is: those companies are not using "Fuzzy" technology. SQL Server Integration Services (SSIS) has a couple new transformations called the Fuzzy Lookup and the Fuzzy Grouping that may help.

Read more

SQL Server 2008 is generally an evolutionary release, building on the foundation of the 2005 release. This is a good approach in my opinion. It means we can build on our existing skills that we have worked hard to acquire since 2005. We can then just focus on learning the new features, right? One exception to this is Reporting Services 2008. The architecture has been changed and a new Report Designer has been developed. There's some good news and some bad news. Which do you want first?

Read more

There are many conferences out there that relate to SQL Server, but my favorite is the excellent PASS Community Summit held annually. "PASS" or the Professional Association for SQL Server is an independent, user-run, not-for-profit organization that supports both local "Chapters" and a worldwide presence. (http://www.sqlpass.org/). Its members are like you and me: DBA's and Developers who appreciate SQL Server but are not necessarily employed by Microsoft.

Read more

In the past, when we wrote applications that required the tracking of changing data, we typically implemented this through a series of Triggers and/or Timestamp columns. We could then write application code to copy changed data from one location to another using, for instance, sophisticated ETL tools such as Informatica or Microsoft's own Integration Services. This would not only be time-consuming but may be expensive too. Now in SQL Server 2008, Change Tracking and Change Data Capture can be enabled automatically at the Database and Table level.

These features allow SQL Server to track changes efficiently through its own internal system tables without the need for coding Triggers or defining extra columns.

Read more

Database Mirroring was introduced in SQL Server 2005. It was one of the main features that excited people when reading about the new release. Here was a capability for synchronizing a database across two servers providing automatic failover that did not need the Windows Cluster Service nor the expensive hardware and software that goes along with it. Yes, the “poor person’s” Failover Cluster at the database level. This feature has been enhanced in SQL Server 2008.

Read more

The concept of Slipstreaming is a very useful feature. It refers to the concept of having a single set of install files for a current version of a product even though updates such as service packs have been issued. An example is Microsoft XP SP2 or Microsoft SQL Server 2000 SP3a. Slipstream versions were created by Microsoft for these Service Pack releases. That way you could install once without having to apply the latest Service Pack - it is already there. This would save time and effort. For instance, some service pack installs take longer than the original installs themselves. For some reason Microsoft SQL Server 2005 does not officially support a slipstream version for SP1 or SP2. Let's hope that changes with SP3.

Read more

Looks like we are going through the same release schedule of SQL Server 2008 as we had in 2005. A new Service Pack has been announced for the current release which seems to herald a possible slippage of the Katmai release - now named SQL Server 2008. In May 2005, Microsoft released SP4 for SQL Server 2000. At the time, the Yukon release or SQL Server 2005 was slated for a "Summer release". Reading Microsoft release schedules are a bit like reading Real Estate listings - you have to read between the lines. When you see a house listed with an "easily maintainable garden" and "easy access to commuter rail" you know what they mean: small yard next to the station...right?

Read more

Anyone who has attended one of my classes knows that one of my passions outside of Database technology is soccer, or football as we call it outside the USA. Back in 1994 there was no professional league here in the United States but the USA was still given the privilege of hosting the World Cup, the single biggest sporting event in the world (it eclipses even the Olympics in terms of total viewers). At that time the internet was in its infancy, at least from a mainstream e-commerce point of view. It's hard to remember those days, right?

Read more

Analysis Services 2005 introduced a brand new architecture, namely the UDM (Unified Dimensional Model) allowing a level of abstraction to be created between the Business Analyst and the underlying Data Sources. This meant that the inherent complexity of the Multi-Dimensional database access would always be transparent to the end user, regardless of the storage options selected by the development team. This is all well and good and much appreciated by all concerned. The key object in the UDM is called the Data Source View (DSV) which creates the unified viewpoint of the data irrespective of where or how it is stored. It also opens the door to Real-Time Analytics, something that was not possible before. But is this really a viable option?

Read more

I recently made a reservation on American Airlines and noticed that my record locator was a six-character alphabetic code. This automatically generated code reminded me of a funny story regarding database design from early in my career. I was working as a consultant for a large multi-national automobile company in England, which shall remain nameless to protect the innocent. We were busy developing a purchasing system that would be used by the company's buyers throughout Europe. It was quite sophisticated for the time (around 1985) and included automatic bid generation and recording using an IMS Database on the IBM MVS platform and supported five native European languages - English, Spanish, Italian, German and Flemish.

Read more

I recently watched a great movie called "Sliding Doors" with Gwyneth Paltrow. When I say "with", I mean she was in the movie in the starring role, not that I watched it actually with her. Do you think the likes of Ms Paltrow would hang out with your average DBA? Not likely. No, she hangs out with your average rock star. (Namely, Chris Martin of Coldplay). It's a clever movie about what might happen if something fairly trivial like missing a train by seconds may affect one's life and it follows the two parallel threads of life, one where she makes the train and one where she doesn't. So what has this got to do with SQL Server you may ask?

Read more

I started out in Database Design early in my application development career. I still remember being issued with an official IBM stencil for drawing Entity Relationship Diagrams (ERDs). Change management was performed using erasers and pencils. You remember that, don't you? Maybe not. I ended up working for Bachman Inc, a great company that pioneered the graphical database design tools we know today. The important thing I learnt in those early days was how to build a blueprint for your database, much like an architect draws up a blueprint for your dream house - before a brick is laid.

Read more

The Yukon Beta program lasted over 2 years, culminating in the release of SQL Server 2005 Nov 7th 2005. I remember the date because I ran a training class that week using the production release of the software in the hands-on labs. Just-in-time training! Hit the ground running! How did we do it? Well, we had already offered "early adopter" training using the CTP releases from earlier in the Beta program. These proved popular for those who wanted to know what was coming down the pike. One feature we were all looking forward to was "Intellisense" within the SQL Query Editor...that would be cool...Visual Studio already had it, so SQL Server Management Studio (SSMS) would have it too, surely...

Read more

SQL Server Integration Services (SSIS) has superseded DTS in SQL 2005 and has some great new features beyond better performance, more scalability and an integrated debugging facility. As if that was not enough, we have been given a whole new set of pre-defined tasks and transforms. But wait; we can also write our own tasks and transforms, making SSIS a truly industrial strength extensible ETL tool that can now compete against the Informaticas of the world. One of the predefined transforms that you might find useful is the Slowly Changing Dimension (SCD) transform.

One of the major uses of ETL (Extract, Transform and Load) tools is to populate Data Warehouses.

Read more

The latest development of multi-core processors has opened the door to affordable performance that was only achievable before from multi-CPU machines. I gave an example recently with the 8-way dual-core HP ProLiant servers used by bostonmarathon.com. Intel is now busy rolling out its Core 2 Extreme processors with quad-cores. What's this got to do with SQL Server? Better performance?

Read more

SQL Server is architected so that you can recover ALL of the committed transactions right up to the point of failure. This fact did not go unnoticed by financial services companies on Wall Street during the early days of distributed applications - the early days of SQL Server. To these companies, even a few minutes of lost transactions can mean millions of dollars - yes, time is money. But we still need to practice disaster recovery, so that when disaster does strike, we are ready to play the hero. As a colleague of mine always says, you want to avoid any "resume generating events!"

Read more

A frequent question that students bring to class when I am teaching is this: "My transaction log is growing out of all proportion - what can I do?" I always answer with another question "Are you backing it up?" The student usually replies "Of course, we backup the entire database every night". "But are you backing up the LOG?" At this point the student lets me know that if we solve this dilemma, the boss will feel the cost of the class will be worth it right there and then. Well, the answer, believe it or not, is to back up the log twice. Yes, twice. Let me explain...

Read more