# Tuesday, November 23, 2010

SQL Server 2008 R2 provides some new tools to help deploy database schemas.

Compiled code and database schemas are frequently dependent on one another and must therefore be deployed simultaneously to a given environment. If a developer writes code that accesses a new table, that table must be available when the code runs. If the code is deployed and run before the table is deployed to the database, a runtime error occurs and the user experience will be unsatisfactory.

In the past, Microsoft has released tools to help developers manage the deployment of database schema changes. Visual Studio Database Edition (aka “Data Dude”) was bundled into Visual Studio 2010. But these tools are geared toward developers – not to DBAs. Most DBAs don’t work in Visual Studio. And in large enterprise applications, it is the DBAs who are managing the deployment of the database schemas. Without tools, this coordination requires a lot of manual steps.

SQL Server 2008 R2 includes support for Data Tier Applications. A Data Tier Application (mysteriously abbreviated “DAC”) is a package containing instructions for deploying a database schema. DBAs and developers can use SQL Server Management Studio to create “DACPAC” files for a given database. They can then use that DACPAC file to create or update a database with an existing schema.

DAC does not support all database objects. Standard objects, such as tables, columns, views, indexes are supported. Security objects such as users roles and logins are supported. Extended stored procedures and full text searches are not supported. The list of supported items is similar to the list of items supported in SQL Azure.

To create a DAC in SQL Server 2008 R2, launch SQL Server Management Studio, connect to a server, and expand the Databases node. Right-click the name of the database you want to deploy and select Tasks | Extract Data Tier Application… from the context menu. The Extract Data Tier Application wizard launches and an introduction window (Figure 1) displays.

Figure 1

Click the Next button. The Set Properties screen (Figure 2) displays.

Figure 2

At the Application Name field, enter a name for this DAC.

At the Version field, enter a version number. SQL Server does not auto-increment version numbers, so you will need to manage this yourself. It’s a good idea to keep major and minor versions of database schemas in sync with the version of the application that consumes them.

At the Description field, you may optionally enter a brief description of the schema.

At the Save to DAC package file field, enter the full path (including file name) where you want to store the deployment file. You may click the Browse button to select a folder. If you enter the name of a file that already exists, you must check the Overwrite existing file checkbox in order to proceed.

Click the Next button to perform a validation. The validation checks if DAC can handle all objects in the database. When the validation is complete, the Validation screen (Figure 3) displays. This screen displays items up to three sets of items: Items that can be deployed with DAC are displayed beneath a green Check icon; items that are not supported are displayed under a red X icon; and items that are supported, but rely on unsupported items are displayed under a yellow Exclamation icon.

Figure 3

Supported items present no problem to DAC.

Unsupported items appear as errors. Unfortunately, you will not be allowed to proceed until these items are removed from the database. Fortunately, the wizard is non-modal, so you can minimize it and switch back to Management Studio to remove these items before re-running the Validation.

Click the Next button to start building the DAC package.

When complete, the Build Package screen (Figure 4) displays, indicating success or listing problems that occurred.

Figure 4

Click the Finish button to close the wizard. A file with the extension DACPAC is created.

Deploying the Schema in a DAC

After creating a DAC, you can use SQL Server Management Studio to deploy the DAC’s schema to a new database. To begin, launch SQL Server Management Studio, expand the Management node, and right-click the Data-tier applications node. From the context menu, select Deploy Data-tier application. The The Introduction screen (Figure 5) of the Deploy Data-tier application wizard displays.

Figure 5

Click the Next button to display the Select Package screen (Figure 6).

Figure 6

Click the Browse button and select the DACPAC file created above. Click Open. Click the Next button to display the Update Configuration screen (Figure 7).

Figure 7

At the Name field, enter a name for the database you wish to create. This field defaults to the name of the database from which the DAC originated. If you are running this deployment on the same server instance on which you created the DACPAC, this will cause a conflict and SQL Server will notify you with a red exclamation icon next to the Name field.

Click Next to begin the deployment. When the deployment completes, the Summary screen (Figure 8) displays.

You should notice the new database appears beneath the Database node and the DAC is registered and appears beneath the Management \ Data-tier applications node. You may need to refresh these nodes in order to view them.

Note that the new database contains no objects – only database objects, such as tables, views, stored procedures, and users.

In this article, we discussed Data-tier applications and described how to use SQL Server Management Studio to create and deploy a DACPAC file.

Tuesday, November 23, 2010 11:55:00 AM (GMT Standard Time, UTC+00:00)
# Monday, November 22, 2010
Monday, November 22, 2010 2:53:03 PM (GMT Standard Time, UTC+00:00)
# Monday, November 15, 2010
Monday, November 15, 2010 2:01:36 PM (GMT Standard Time, UTC+00:00)
# Sunday, November 14, 2010

This weekend, I experienced something that most people never will. My son Nick played in a Division 1 basketball game. He wore #40 for the Michigan State University Spartans and played 2 minutes in a 96-66 win over Eastern Michigan. The Spartans have an excellent team this year – they are currently ranked #2 in the nation after coming off 2 straight Final Fours – yet Nick made the roster as a walk-on.

The most important thing in my life is the success and well-being of my two sons. Most of my life the last couple decades has revolved around them and I often take more joy in their successes than in my own.

I have followed Michigan State sports for even longer. MSU is my alma mater and I have supported and defended her athletic teams through good times and bad the past 30 years.

Friday night in East Lansing with 2 minutes to go in the game, two of my favorite things were mystically combined into a new ultra-super-awesome-mega-favorite thing, that was greater than the sum of its parts. It was like Reese’s Peanut Butter Cups times 1,000 – It was my son and my favorite team.

I will never forget the moment my son’s name was announced and he ran onto the Breslin Center court. What he did during those 2 minutes was unremarkable and unimportant. The fact that he was out there, wearing the Green and White and 15,000 fans were cheering for him (none more than this one) is important and remarkable.

I am enormously proud of Nick for the hard work he put into getting here. And I am grateful for this gift he has given me.


More photos

Sunday, November 14, 2010 7:00:00 PM (GMT Standard Time, UTC+00:00)
# Tuesday, November 9, 2010
Tuesday, November 9, 2010 1:20:00 AM (GMT Standard Time, UTC+00:00)
# Sunday, November 7, 2010

Do you like computers and other technology?

Do you have the time, talent and desire to create a quality video or screencast about technology?

Would you like to win an MSDN Universal subscription (approximate value: $12,000)?

If so, read on.

I am hosting a contest and the first prize is an MSDN Universal license.

Send me a 1-10 minute video showing off some aspect of computer software, hardware, other technology, or technologists. This video can be a tutorial, an interview, a screencast – anything that gets the point. For ideas, you may look at past episodes of my Technology and Friends show  at http://TechnologyAndFriends.com. But you need not stick to the same format that I use.

This contest will run until December 1 2010, at which time I will select a winner.

Here are the rules

  • Contest closes at 5PM EST on December 1 2010.
  • The submitter must own the rights to all material in the video. No video or audio from another source to which another owns the copyright, unless explicit permission is obtained. No reading aloud someone else’s blog or book without permission.
  • The topic must be related to software development, computers, or the people who work in the computer industry. If you are unsure about a topic, send me an e-mail. My address is below.
  • No nudity, violence, pornography or profanity (visually or verbally) . I am the judge of what falls into these categories.
  • No racism or sexism or hating against any group. Criticism of technology is OK, but slander of people is not. Please keep your message positive. I am the judge of what falls into these categories.
  • The video must be between 1 and 10 minutes in length
  • The winner will be chosen by me and I am permitted to use whatever criteria I want to determine which one is “best”. This might be the most educational, the most entertaining, the best production, or a random draw if I can’t decide.
  • You retain copyrights to the video, but you give me permission to republish it with attribution. I plan to show the top videos in an upcoming episode of Technology And Friends. This show airs on the web at http://TechnologyAndFriends.com and on channel 17 of my local cable TV network. I have no plans to make money directly off the submitted video. I will not sell them or charge viewers to see them.
  • First prize is a not-for-resale MSDN Universal subscription. The retail value of this subscription is $11,899.
  • Acceptable video formats are WMV, MPG, FLV, MOV, AVI, and MP4. If you have another video format, please e-mail me. My e-mail address is below.
  • To enter, e-mail to me (e-mail address is below) a link to the video. You may make the video available in any of the following ways
    • Upload a video file to an FTP site and provide me with download information
    • Upload a video file to a file sharing site, such as box.net, RapidShare, Drop.io, FileDropper, FileSavr, or Driveway. and provide me with download information.
    • Publish the video to a video hosting site, such as YouTube, Vimeo or Viddler. I may follow up with you and ask for the original, if I am unable to download from this site.

To enter or to request more information, contact me at DavidGiard@DavidGiard.com.

Good luck.

Sunday, November 7, 2010 5:02:02 PM (GMT Standard Time, UTC+00:00)
# Monday, November 1, 2010

Episode 123

Joe Ross on Ford Sync

Monday, November 1, 2010 6:21:00 PM (GMT Standard Time, UTC+00:00)
# Wednesday, October 27, 2010
Wednesday, October 27, 2010 9:14:31 PM (GMT Daylight Time, UTC+01:00)
# Tuesday, October 26, 2010
Tuesday, October 26, 2010 1:12:00 AM (GMT Daylight Time, UTC+01:00)
# Monday, October 18, 2010
Monday, October 18, 2010 7:18:00 PM (GMT Daylight Time, UTC+01:00)