Voice of the DBA

Author: way0utwest

  • 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.

  • Laptop Boy Scouts

    Are you prepared for remote work?

    This editorial was originally published on Jan 10, 2007. Steve is at DevConnections, so it is being republished. 

    So class, for today’s protection mechanism you’ll need: a mouse, a loop of wire, a lock, a key, and a link. Sounds like MaGyver, huh? This article on hot spot security gives you some software hints and covers most of the items I listed above. The exception is the loop of wire.

    If you’re working for some time at a place, especially a place that’s traffic’d well, you might want to lock your computer to the desk or table. It will discourage iJacking or the chance that someone will walk by, grab your laptop and run. This is more likely in a cafe or Starbucks than an airport, but you never know.

    The advice is good and it makes sense that most of the discovery and openness of Windows is rapidly becoming a liability. Even out here in the country, where I live and my nearest neighbor is nearly 1/4 mile away, I have keys on our wireless routers. I know it’s not the best security, and I did get some complaints about making everyone have a key until a neighbor’s router showed up on our computers. Makes you realize just how far away someone could be and reach your system.

    As handy as all the capabilities are in wireless networking, as with anything else, they need to be secured and they should be turned off by default. Use them when you need them, but the rest of the time, try to be a bit more secure and limit access to your system.