Voice of the DBA

Tag: sql server

  • Filestream and Backups

    What happens when you backup a filestream enabled database in SQL Server 2008? According to BOL, your data is backed up as part of the normal backup process.

    Does it work? Let’s do a little test. I made a simple AdventureWorks2008 backup like this:

    backup database AdventureWorks2008 to disk = ‘c:\sqlbackup\ADW2K8test.bak’

    That gave me a single file, 222MB in size. This includes the filestream data, which by default is located in the \Documents folder below your default data folder in SQL Server 2008.

    filestream

    I can create two new folders. I created a “test” folder below my data directory on the test machine, and then a “FilestreamDocs” folder below that.

    filestream2 

    I then issued this restore:

    RESTORE DATABASE [ADW2k8Test] 
 FROM  DISK = N'C:\SQLBackup\adw2008.bak' 
 WITH  FILE = 1,  
 MOVE N'AdventureWorks2008_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\adw2008.mdf',
 MOVE N'AdventureWorks2008_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\adw2k8.ldf',  
 MOVE N'FileStreamDocuments' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test\filestreamdocs',  
 NOUNLOAD,  REPLACE,  STATS = 10
GO

    That completed, and I had a fully functional database that included my filestream data.

  • Slide Decks for PASS, SQL in the City, and SQL Server Connections

    I’ve sent my slide decks to the organizers, but I know they don’t always get posted. So I’m putting a few links up here for my talks.

    If you check the page for my Preparation for Disaster talk, you’ll find I have links for the two recent events to the decks. I often version the decks after talks, and so I’ve decided to keep all versions live.

    I’ve also uploaded the decks from PASS if you’re interested.

    PASS Summit 2011

    SQL in the City – LA 2011

    SQL Server Connections

  • ETL

    The phases of ETL

    Today we have an editorial that was originally published on Aug 31, 2006 as Steve is traveling at DevConnections.

    I’ve followed the development, release, and subsequent news on SQL Server 2005 for over two years now. It’s been an interesting journey and I’ve had the chance to see some evolutions in the product over time as well as observe the reaction to different subsystems.

    At the Colorado Code Camp, where I got roped into speaking, I was asked what I thought was the part of SQL Server 2005 that had the most impact. I’m not a heavy user of the product, but since I gather, edit, and follow the news I answered that Integration Services was one of the most written about, blogged about, newsworthy systems. It seemed more people were really excited about SSIS than anything else, despite the fact that I think the Service Broker and other technologies are “cooler” to me.

    So when I saw some people knocking SSIS, as well as this blog post by Jamie Thomson, I decided this would make a good poll.

    Is SSIS a professional data integration tool?

    I’m not a BI guy, and not deep into the ETL world like many of you out there. However it does seem to me that while it may not offer all the features of tools costing thousands of dollars, it does a great deal of the things I need in a professional ETL tool.

    Most of us don’t deal with terabyte databases. We don’t roll up data from 30 separate applications into one humongous data warehouse and then spin off a dozen cubes for different departments.

    I’ve done some of that on a smaller scale and I thought DTS was up to the task most of the time. Sure the error handling wasn’t great and there were times we had to restart things and do some manual cleanup or script editing, but it worked very well for most of my needs. From what I’ve seen of SSIS, it’s more robust and does an even better job of being a professional tool I can use.

    As to whether it’s professional enough or enterprise ready, I’d like to know what you think.

  • The Cost of Architecture

    Falling Water is an amazing architectural wonder.

    I saw a note recently about the cost of decisions in programming made over time (thanks to BrentOzar, PLF). It’s an interesting look back at what decisions might have ended up costing computing a tremendous amount of pain and problems over time. It’s a nice read, especially if you remember something about the history of computing and how technology has evolved over the last forty or so years.

    Making decisions and choices early on in the development of a system is hard work. There’s a reason why architects are paid well, and sometimes seem slow to decide how to proceed. The high pay, however, is a reason why so many people crave the architect title, and even find reasons why they should get it, even when under qualified. Unfortunately we have no good guidelines on how to decide what titles someone should have, or they have earned.

    A great developer, a great architect, is often worth much more productive than an average one. Not only while building a system, but also in the future as the system is used, evolves, and is improved by others. Making good choices early on can result in much better software over time, as the ways in which software is improved are often based on the way it was originally built. Future developers try to continue to add to the project, or are constrained, by the early decisions made about the architecture.

    Hindsight always gives you a better view of a decision than you have when you are making it. Even in the article the author points out that the biggest mistake he found in computing was still the best decision at the time. We will hopefully make many good decisions, but all of us will make some bad ones. Whether in specifying hardware, writing T-SQL, or some other choice, we will find over time that some of our choices were poor ones.

    The best thing we can do is learn to look back at our architecture decisions over time and evaluate whether they were good or bad. Learn from our mistakes, and learn from the mistakes others have made, trying to strive to do better in the future with the knowledge that comes from examining the past.

    Steve Jones

    The Voice of the DBA Podcasts