SQL Server Data Tools in Visual Studio 2012–Publish Database Profile

One of the new features in SSDT, and what I consider to be my favorite, is the Publish Database Profiles. With database projects you could set a multitude of settings, everything from ANSI NULLS to whether to drop and create the database with each build. The only issue was these settings applied to the entire project; you had to change them each time you wanted to deploy to a different server, or to change the rules (overwrite vs. incremental for example).

New with SSDT are Publish profiles. They allow you to establish a set of rules and save them for reuse. To start with, right click on the project name and pick Publish from the menu.

image

You’ll now see a blank publish page.

SNAGHTML360f9789

Let’s start by tweaking some database settings. Click the Advanced button on the lower right.

SNAGHTML36114075

Here you can get to all of the options you can use to fine tune your database deployment. The most common appear at the top, the less changed ones appear in the list below. In this image I’ve checked on the option to Always re-create the database. This option will wipe out the existing database and recreate it from scratch.

Use this particular option with caution, especially if you are doing it to a database you are sharing with your co-workers (or even worse, production!). When your rebuild the database you’ll also lose any data and have to reload. Sometimes this is a good option, especially in the early stages of development when you’ve made massive changes to the database, or perhaps have gone into the database and made a lot of changes outside the scope of SSDT.

There may be other options you need to change, based on your environment or DBA requirements. Once you’ve changed your options click OK to return to the previous screen.

Back on the Publish Database settings dialog I’ll set the target database connection, and the name I want to use for the database. I can also set the output script name if I wish.

 

SNAGHTML36153d0b

Next, I want to be able to save this profile so I can reuse it later. Check on the “Add profile to project” option in the lower left, then click the Save Profile As… button.

SNAGHTML36195ad8

I gave it a good name, and made sure to include the most important options such as RecreateDB to indicate a database recreate was one of the options.

As I write this however, there is a bug with SSDT. When you click the “Add Profile to project” button it immediately adds a profile with the original default name. Then when you click the Save button in the dialog above, it adds the profile again, totally ignoring the name you give it. Instead it uses the default name again, only this time with an _1.

I’ve been assured that this bug is already known and has been fixed, and will be released with the next update to SSDT in VS2012. So depending on when you read this, it may or may not be an issue. Regardless, the fix is very easy, just rename the new .publish.xml file to reflect what you wanted it to be.

Once saved come back and hit Publish. The database will now be deployed to the server and the profile will be added to the solution. Here it is, after I’ve renamed the publish profile.

image

Note that I’ve given it a naming convention that specifies the database name, the target server, and any critical options. Here I’ve added “Overwrite” to indicate what will happen when I run it.

To run it, just double click on it. First, Visual Studio will do a build of the SSDT project. If there are any errors the process will be halted and you’ll need to fix them. If not, you’ll be presented with the publish dialog, this time with everything filled out.

SNAGHTML98453db

All you have to do is click Publish and the database will be created/updated using the options you’d picked previously, to the server which you had previously indicated.

Now for the real fun. Repeat the above steps only this time do NOT check the overwrite database option. Now, (after renaming the new profile) you have two publish profiles to pick from.

image

Take this even further. In my current project I have 8 profiles. An incremental and overwrite option for my local computer, the development server, the user acceptance testing server, and the production server. (In my case it’s a one man project, I’m the developer and the DBA all in one.) No longer do I have to juggle the server name, or even worse do a publish but forget to change the server from production back to local.

By far I think this is my favorite feature in SSDT.

Duqu Worm Security Issue with Windows True Type Font Engine

Last week Microsoft revealed there is a serious security vulnerability with the true type fond rendering code built into the Windows kernel. By simply visiting an infected website the Duqu worm can get administrative level privileges to your system, thereby installing viruses / worms on  your system.  Malformed MS Word documents can also be an entry vector for Duqu.

While a more permanent patch is expected to be available within the next month, Microsoft has implemented a “Fix it” workaround you can access via this url:

http://support.microsoft.com/kb/2639658

To enable the fix, scroll down and click the fix it button under “Enable”.

Please note: There is one drawback to this fix, once you enable it you will no longer be able to do a “Save As…” to PDF format from any Office app. You can restore this capability by disabling the Fix It by clicking the appropriate button under the “Disable” option in the above url.

I have successfully tested the fix enable / disable and was able to restore the ability to save as to PDF. For the time being I will be running with the fix enabled. If I need to export to PDF I can visit the site, disable the fix, and save to pdf, then re-enable. While disabled I would not be going to any websites. 

This is a fairly serious issue that is already being exploited to infect machines. To protect yourself, along with your business and / or clients, you should consider using this fix until a permanent solution is provided by Microsoft.

Also note that this week’s “patch Tuesday” updates included some critical security fixes. If you do not have your box setup to automatically apply updates, you should go to Windows Update and get the latest patches.

A big thanks to Steve Gibson (@sggrc) and his Security Now podcast on the TWIT.TV network, where I heard about this. If you aren’t listening to the Security Now podcast, you should. I’ve long held it should be required listening for any IT Professional.

A Week of Windows 7

Since my last post the only thing I have installed is the Visual Studio 2008 and SQL Server 2008 Development tools. Since then I have worked with the various apps and can report these items so far.

First, no problems as of yet with the development tools, although so far I’ve only been using SQL Server Management Studio.

WinAmp works OK for playing music, but when I rip a CD the Media Library doesn’t always refresh correctly. I have to exit WinAmp and restart. Note this only happened about 50% of the time, the other half it flickered, but recognized that I’d inserted a new disk.

The NVidia graphics drivers seem to crash fairly often, about one to two times a day. They usually restart and everything is OK EXCEPT the Zune software. The UI on it goes blank. It still works, it happened today while I was playing some music and the music kept on playing, you just can’t interact with it. So far I’ve used task manager to shut down the Zune software and then I can restart it. It will work fine after that (at least until the next time the graphics drivers crash).

Every so often everything just freezes. Mouse doesn’t work, no keyboard input, no screen updates. I’m guessing it’s a graphics issue, but not really sure.

When launching a Virtual PC, they seem to take a long time to connect to the network. They will eventually connect (5 minutes is about average). Just be patient.

The installer for the SQL Server 2000 Northwind database sample crashed while I was trying to install. Fortunately i was able to install by extracting the SQL scripts from the zip file and using them to create the pubs and Northwind databases. (I need these for some code samples).

Everything else I’ve used seems to work OK. So at this point I seem to have all my software installed, so now I’m going to settle in and let my focus return to Data Warehousing and Analysis Services.

Don’t forget the Alabama Code Camp coming up at the end of January. So far we’ve had no entries for Speaker Idol, so as of now your chances of winning that 1 year MSDN Subscription seem quite good!

Wonderful Wednesdays With Windows 7

I attended a great user group meeting tonight, where fellow MVP Jeff Barnes presented on Windows Azure. I learned quite a bit. I didn’t have much time to work with my Windows 7 install, but do have some link love to pass along. First off though, the apps.

WinAmp – First off was my old standby for ripping CDs (yes, that I legally own) and playing music files is WinAmp. I installed version 5.54 tonight, they player seems to work fine. The only problem I had was getting it to install skins. First, there was no file association setup for them. Easy to fix, first I had to download the skins to another drive, then set the file association for the .wal file to winamp.exe. But even then it did not install. I figured out it’s a permissions issue, by default Windows 7 requires elevated rights in order to write to the program files folder. I figured this out when I was copying the files from my download drive to the WinAmp Skins folder. It prompted me for permission to continue.

After copying the files, I was able to find the skins in the WinAmp Menu. I’m guessing the only thing I would have to do is run WinAmp as admin when I want to install new skins, or fiddle with the folder permissions for winamp.exe. Frankly I’m glad Windows 7 is restricting rights to the program files folder, although apps that write files (like Winamp with it’s skins) to the same folder as the application may run into issues. For me though it’s not a big deal, I generally only use 1 skin (MMD3) so I’m set.

Pismo File Mount – The second tool I installed was a freeware ISO mounter named Pismo File Mounter. One of my Twitter friends (@cfrandall) kindly pointed it out to me. It’s pretty simple, just right click on an ISO and click Mount from the menu and there it is. Seems to work fine, I was able to browse files and what not. Tomorrow I will start installing some applications from ISO and let you know how well it works.

Now for a little link love.

Windows 7 Beta Home – The official Microsoft Windows 7 home page, has links to the beta program so you can get your own copy of Windows 7 and be one of the cool kids. Also has links to the Windows 7 blog, desktop themes, and more.

Tim Senath’s Musings – Tim is a client platform guy form Microsoft. His blog has a great bumper crop of Windows 7 secrets. I picked up several valuable tips that I’m already using. I love the one of double clicking on the upper or lower border of a window and it maximizes the window height wise, but leaves the width alone. Using WIN+SHIFT+LEFTARROW and WIN+SHIFT+RIGHTARROW to move a window back and forth between monitors is also becoming a favorite. Check out his blog post for a lot of other great tips and tricks, some of which even work under Vista.

Marlon Ribunal’s Blog – Marlon has a good post with links to Windows 7 Beta Reviews and other articles.

Windows SDK for Windows 7 and .Net Framework 3.5 SP1 Beta – If you are doing development specifically for the Windows 7 platform, you will likely want this SDK for Windows 7 and .Net 3.5 SP1. Like Windows 7, this SDK is also in Beta.

And finally, if you are tired of answering the “well what’s new in Windows 7?” question from all your friends, family, and co-workers, point them at Paul Thurrott’s SuperSite for Windows. He has a Windows 7 FAQ that answers all sorts of questions and has a nice list of all the new features.

Tuesdays are Terrific with Windows 7

It’s Tuesday, and the march to install software in my Windows 7 install goes on. Good news for today, everything was favorable although I did get slowed down downloading the latest VMWare. Speaking of which, we’ll let it start the list.

VMWare Workstation 6.5 – Installed with no problems, everything seems to be working fine. It recognized my USB devices, network, etc.

Camtasia Studio 5 – Installed and works no problems.

SnatIt SnagIt 9 – Had a hic-cup installing the first time, just seemed to install. In retrospect I may not have given it long enough. I rebooted, and to be safe started the install in Admin mode, it installed and works just fine.

Bayden SlickRun – works great, no problems.

TrueCrypt 6.1 – Works fine, mounted the drive OK.

Live Mesh – Works fine, I loaded it on my Windows 7 machine and was able to login to the website on another computer and remote control my Windows 7 box with no problems.

Corporate VPC – My companies VPC software installed and ran just fine with Windows 7. I can’t say much else about the software since it’s something proprietary but my co-workers will be pleased to know it works.

And that’s it for today. All in all I have been very pleased with my Windows 7 experience. I have been taking it slow, installing my software one at a time, testing, and verifying basic functionality. Tuesday night I have a Bug.Net meeting on my calendar, so it may be Thursday before I get a chance to do more software installs.

One follow up from yesterday, I was told Virtual Clone Drive will work under Windows 7, but causes the Windows 7 shutdown to hang. Haven’t tried it yet, if anyone knows a free ISO reader that works under Windows 7 please leave a comment.

A Weekend with Windows 7

In-between other household duties I spent most of this weekend with the new Windows 7 Beta 1. While I probably would have been more sensible to install it in a Virtual PC, I really wanted to experience it, and the best way to do that is by using it. Thus I installed it on my HP Pavilion DV-8000 laptop.

The first pass I did Friday night, when I installed Windows 7 as an upgrade to my installed Vista SP1. Now, let me say Microsoft has clearly stated you should only install the Beta as a clean install, not as an upgrade. However I figured since it was going to get wiped anyway, I might as well see what the experience was like. The upgrade took about 2 hours and afterward things were not overly stable. Some things worked fine, but other things did not. For example, Virtual PC’s built in network drivers quit working, although I could still use Shared NAT. My Zune software also started acting odd, it would no longer connect to my Zune. The PC knew the Zune was connected, the message just didn’t get to the Zune software.

Saturday morning I played with it a bit more, and being unable to resolve my Zune issue decided to take the plunge, reinserted my Windows 7 DVD, and reformatted my C drive so I could do a clean install. The install went very quickly, around half an hour not counting the formatting. Since then I have been slowly restoring my various applications, and wanted to share a run down on what I’ve done so far.

Before I go any further though, one very critical item. One of my Twitter friends @devhammer alerted us to a bug for Windows Media Player in Windows 7. It is Support Article 961367, and it fixes an issue with Media Player corrupting MP3 files. The first thing you should do install it!

Next, after the Windows 7 install I found my resolution stuck at 800×600. Yuck! So I ran Windows Update, and it found a driver for my NVidia chipset and installed. (Hooray for Windows Update!) After the reboot I was returned to 1400×900 on my laptop display and 1600×1200 on my external monitor. But not all was well with the world, there is one odd bug. By default the wallpaper is this bluish looking fish. Not being a fish person I switched to the Landscape theme. Windows 7 has this cool feature where you can pick multiple desktop wallpapers, and it will rotate through them at a frequency you can set,  the default being every 30 minutes. This though seemed to cause an issue with the Zune software, every time the wallpaper changed, my Zune software went completely blank and never came back. It was still working, my Zune player was showing data being synced, but the display went blank. I used task manager to shut it down then could simply launch the Zune software again with no problems.

The moral of the story, if you have NVidia graphics, set the rotating wallpaper on, and have display issues, simply pick ONE wallpaper and disable the rotation. Once I did all was well with the world. Now onto my software installs. 

Norton Anti-Virus, Corporate Edition – Seems to work OK, but I get an error message about the End Point process being shut down for compatibility issues. Since I hear Norton has discontinued this product, I will likely move to either Windows Defender or purchase the full blown Norton closer to the Windows 7 release date.

FireFox 3 – Works great, no issues.

UltraEdit 14 – Also works great, no issues.

TouchCursor 1.6 – After I installed I had to reboot to get it to take effect, but once I did it’s worked great. (If you don’t know what TouchCursor is, go to http://touchcursor.com, great utility!)

Zune – Software installed fine, but of course switching to what appeared to the Zune as a new PC caused me to need to reset my Zune so I didn’t wind up with a big blob of “unreachable” disk space. I had backed up all my Podcasts, and copied them back over and the Zune software recognized them all, but I still had to go to each one, right click, pick Subscribe. Fortunately I have a second PC in my office where I played some videos on http://www.jumpstarttv.com/ while clicked endlessly. (I subscribe to a LOT of podcasts.)

Office 2007 Enterprise – Installed just fine with no problems. Well no software issues, my backup of my main PST was corrupt so I lost most of what was in it (drat). Good lesson here kids, with something really important, make TWO copies on different drives during back up!

Microsoft Virtual PC 2007 SP1 – The only issue I had was with the built in firewall, I had to create a new rule for ANY to allow things other than UDP and TCP to work. Go to Start, Control Panel, System and Security, Windows Firewall, Advanced Settings (over on the left), Inbound Rules (in the new dialog that appears), then I copied one of the existing rules for Virtual PC 2007 SP 1 (there should be 2, one for UDP the other for TCP). In the copy, open it, go to the Protocols and Ports and pick Any. You’ll get an error that says “Edge traversal can’t be set to ‘Defer to User’”, so go to the Advanced tab and pick either “Allow” or “Block”. I picked Allow because I’m very cautious about where I go in my VPCs.

Live Writer – I went to the http://windows.live.com and downloaded the LiveWriter tool, which I’m composing this post in.

Notable mention: I had to copy a little over 3 gig of files, it was fast in Windows 7, took under 3 minutes.

A few things I’ve heard about, but haven’t yet experienced:

I’m told there’s a copy / paste issue between Word 2007 and Live Writer. Haven’t tried it.

I’m told Virtual Clone Drive, which I used in Vista to mount ISOs as virtual drives, won’t work in Windows 7. Instead I had PowerISO recommended to me.

That’s my progress for now, I will update you as time goes by. Remember if you decide to install and use Windows 7, it IS a beta, so your stability may be different depending on the state of your machine’s drivers. I also haven’t decided how long I will run Windows 7. If it’s stable, and some critical pieces of software work (like my VPN software for work) then I may keep it a long time. However if stabiltiy becomes an issue or key software doesn’t run I may have to return to Vista, I will just have to see how it all shakes out. I would like to keep it around for a bit though so I can give it a good shake and let our friends in Redmond know of any issues so they can fix now and perhaps save someone else headaches when it goes to production.

I have also been Twittering my progress using the #win7 tag, if you want to follow me there.

Differencing Disks in Virtual PC 2007

Yesterday I mentioned I was going to get SQL Server 2008 installed in a Virtual PC (VPC). Now, I could have setup a virtual machine from scratch, or copied an existing one. But there’s a better way: differencing disks. Differencing disks allow you to create a virtual machine, then use it as a base for new machines. Much like you would create a base class and then let new classes inherit from your base.

My first step was to create a brand new virtual PC. I chose Windows Server 2003, using the one from my MSDN license. I could also have gone with XP, or the advanced versions of Vista licenses you to install up to four virtual machines in addition to itself as the host. So I get my VPC setup with Windows Server 2003, and make sure all of the windows updates have been applied, service packs, etc. In addition, if there are any additional tools / utilities I’d like to have available for every machine I’ll be wanting create from it I’ll install those as well. I’m thinking of things like UltraEdit / Notepad++, IE7, AllSnap, etc. I finally conclude by shutting down the machine.

Exit Virtual PC, and go to the folder where your virtual PC’s reside. First delete the VMC file (the small one) of your Virtual PC. You won’t need it any more, as you’ll never open this VPC directly. If you did, you would break all the machines that inherit from it. Again, not unlike changing the signature of a base class.

Next, mark the VHD, the hard drive as Read Only. Again this is for your protection, to keep you from doing something accidental to the base. At this point we have our base machine created, and can now make new machines from it.

Launch Virtual PC again. Click on File, Virtual Disk Wizard. You are given a simple dialog that lets you know you’re in the Virtual Disk Wizard. Click Next to move along.

[Picture 1 - Welcome to Virtual Disk Wizard]

Next it asks if we want to create a new disk or edit an existing one. We’ll want to create a new one, so just click Next.

[Picture 2 - Create a new virtual disk]

Now it wants to know what kind of disk to create. We’re doing a hard disk, so just take the default of virtual hard disk and click next.

[Picture 3 - Disk Type]

Now it asks where you want to put your virtual hard disk. I keep mine on my D drive, and use a naming convention. I start with the OS, then the main software I am using. I then use either the word Working, to indicate it’s alright to launch and work in it, or Base, to show the vhd should only be used to inherit from and not be launched. In this case I will be using this as a working area for my SQL Server 2008 CTP6, so I used Working. You are free of course to name it whatever you want, use a name like “Hanselman is cool.vhd” if you like I just prefer something a bit more logical.

[Picture 4 - Disk Location]

OK, this is where you need to pay attention, as this is the first time you’ll need to change a default. Here you are asked what type of virtual hard drive to create. The default is dynamically expanding, and it’s what you’d want to use if you are installing an OS from scratch or are creating a second hard drive for your virtual machine. Fixed size would be used if you are creating a disk for something like a USB drive and want to make sure it won’t get too big. Again, this would be used when you need an empty drive.

In our case we want the third option, Differencing. What this does is tell the Virtual PC application to base the new hard drive on an existing one. From here on out, only the changes you make to the virtual drive will be recorded. This has a lot of benefits. First it saves you disk space, in that you can use the same base with multiple virtual machines. Second, it lets you install the base OS only once, and not have to keep recreating it over and over. Finally, you can create multiple generations of disks. For example, I could create a base of Windows 2003, then another base with Visual Studio added. I could then use that base to inherit from, and create two drives. One could be used with SQL Server 2008, the other with SQL Server 2005. In our case we’re keeping it simple, so pick Differencing and click next.

[Picture 5 - Hard Disk Options]

Next we need to pick the virtual hard drive we want to base our new machine on. In this case I am selecting my Windows Server 2003 core base, and clicking next.

[Picture 6 - Pick Base Hard Drive]

Next we are told it has all the info it needs. All we have to do is click Finish and we’ll have our new Virtual Hard Disk.

[Picture 7 - Complete Disk Creation]

Virtual PC thoughtfully tells us it was successful.

[Picture 8 - Confirmation Message]

OK, we have a new disk, but now we need to tell Virtual PC we want to use it. Back on the Virtual PC Console, Select File, New Virtual Machine Wizard OR click the New… button on the console. Virtual PC has a need to tell us what we just picked, so just click Next.

[Picture 9 - Create Machine Wizard]

This time we are creating a new virtual machine, which will be based on the virtual hard drive we just created, so take the default and click next.

[Picture 10 - Create a machine]

Next we need to give our machine a name. I usually give it the same name as the hard drive, except for the vmc extension. Name yours and click next.

[Picture 11 - Macine name and location]

Now it asks what OS we’ll be using. Note it has automatically detected that I’m using Windows Server 2003, so all I have to do is click next.

[Picture 12 - Confirm Operating System]

Now it asks what my default RAM size will be. I figure 256 MB is a bit small, since I have the ram I upped it to 768 MB. Set yours according to the free space you can spare and click next.

[Picture 13 - Select default amount of memory]

Now we’re asked if we want to use an existing disk or create a new one. Obviously we want to use the differencing one we just created, so click next.

[Picture 14 - Existing Disk or New Disk - We want existing]

Next it asks where our existing drive is, pick it out using the Browse… button or type it in.

[Picture 15 - Pick name of existing disk]

Let me call your attention to the Check Box, “Enable undo disks”. If you leave this unchecked, your virtual machine will behave like a normal computer. Any changes you make are applied and saved. If you check the undo option on, then during your session any changes are written to a temporary file. When you exit the VPC, you are asked if you want to save any changes you made. If you say yes, they will be permanently applied to the virtual machine. If you say no, they are discarded, lost forever. Undo disks are ideal for test situations where you want to run the same changes over and over but not save them. Testing software installs, for example, or in a classroom where you want the students to do labs but not save them.

While Undo Disks can be very helpful, in this situation I don’t really need them as I want to keep all my changes so I will leave this unchecked and click next.

[Picture 16 - Complete Machine Wizard]

OK, we’re at the finish line. All we have to do is click Finish to complete the creation of our new virtual machine.

Let me call your attention to the file sizes of our new machine. Take a look at them in explorer…

[Picture 17 - Explorer snapsho showing small size of vhd]

Note how tiny the vhd file is right now. That’s because it’s based on another drive, where all the OS bits are. As we open it and apply changes (such as installing SQL Server 2008) it will grow in size, but we’ll always be able to save the disk space of the OS as it’s coming from another file.

The down side to differencing disks is speed, because they are in multiple files the performance won’t be as great. Additionally you can’t update the base machine without breaking its descendants. However, differencing disks offer several advantages as well. They save you time, in that you can create a base OS once and use it over and over. As you can see above they can also save you disk space, in that the core OS only takes up space once on your drive and not over and over.

Consider Differencing Disks, and whether they might be appropriate to your development environment.

Follow

Get every new post delivered to your Inbox.

Join 104 other followers