http://sqlblog.com/blogs/john_paul_cook/default.aspxenCommunityServer 2.1 SP2 (Build: 61129.1)http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspxMon, 17 Nov 2008 02:54:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9975John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/9975.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9975<p>My <a href="http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx" target="_blank">first post</a> on getting started with SQL Server 2008 spatial data showed the results of displaying geography data. Before moving forward to advanced topics, it's time to pull back and learn the basics. It's easier to learn the fundamentals by using the other spatial data type, geometry. We're going to figuratively get some graph paper and remember our high school algebra and geometry lessons. Don't worry, everybody's going to get a good grade!</p> <p>The spatial data types and objects are described <a href="http://msdn.microsoft.com/en-us/library/bb964711.aspx" target="_blank">here</a> in Books Online. We'll begin by creating a square box by using the polygon object. Although it is true that it takes four points to define a square, it takes five points to define a square using the polygon object. That's because we need to close the square by using the first point as the fifth and last point. Think of it as describing a path you are walking. After getting to the fourth point of the square, you want to walk back to the first point to close the loop. Here's the code to make and display a square two units in length on each side.</p> <p><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@smallBox<font color="#808080">;</font></font></font></p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_4.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="280" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_thumb_1.png" width="821" border="0" /></a> </p> <p>A single box isn't an interesting as two boxes, especially when the two boxes have something in common. We'll continue by creating a larger box that partially intersects the first box.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@smallBox<font color="#808080">, <font face="Courier New" color="#ff0000"><font color="#000000">@largeBox<font color="#808080">;</font></font></font></font></font></font></p> <p>We see the result result as before. That's not what we want to see. We want to see both polygons, both squares.</p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_6.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="277" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_thumb_2.png" width="821" border="0" /></a> </p> <p>In the previous code snippet, we treated the spatial objects as columns. That's why we didn't achieve the desired result. If the query is modified to return the large box first, only the large box appears. It is necessary to treat the spatial objects as rows to see all of them.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@smallBox<font color="#808080"> <br /><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#0000ff">union <font color="#808080">all <br /></font></font></font></font></font></font></font></font></font><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@largeBox<font color="#808080">;</font></font></font></font></font></font></p> <p>Now we see both polygons. Notice that the intersection of the two squares is a darker shade and easily identified visually. This is only a visual effect. To programmatically define and work with the intersection of the two polygons, a spatial data method call is required.</p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_8.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="278" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_thumb_3.png" width="818" border="0" /></a> </p> <p>To compute the intersection of two polygons, the STIntersection method is used. You can read more about geometry method calls <a href="http://msdn.microsoft.com/en-us/library/bb933960.aspx" target="_blank">here</a> in Books Online.</p> <p><font face="Courier New"><font color="#0000ff">select</font> @smallBox<font color="#808080">.</font>STIntersection<font color="#808080">(</font>@largeBox<font color="#808080">);</font></font></p> <p>If you modify the query slightly, you can get the coordinates of the polygon defining the intersection.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New"><font color="#0000ff">select</font> @smallBox<font color="#808080">.</font>STIntersection<font color="#808080">(</font>@largeBox<font color="#808080">)<font color="#808080">.</font><font color="#000000">ToString</font><font color="#808080">(</font><font color="#808080">);</font></font></font></p> <p><font face="Courier New">POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))</font></p> <p>Although the following query doesn't produce significantly different results in the spatial viewer, it is different because it is actually displaying three distinct polygons.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@smallBox <br /></font></font><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#0000ff">union <font color="#808080">all <br /></font></font></font></font></font></font></font></font></font><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#0000ff">select</font> <font color="#000000">@largeBox<font color="#808080"> <br /></font></font></font></font></font></font><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#000000"><font color="#808080"><font face="Courier New" color="#ff0000"><font color="#0000ff">union <font color="#808080">all <br /></font></font></font></font></font></font></font></font></font><font face="Courier New"><font color="#0000ff">select</font> @smallBox<font color="#808080">.</font>STIntersection<font color="#808080">(</font>@largeBox<font color="#808080">)<font color="#808080">;</font></font></font></p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_10.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="279" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_thumb_4.png" width="818" border="0" /></a> </p> <p>Change the query as shown and use the STUnion operator instead of STIntersection.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New"><font color="#0000ff">select</font> @smallBox<font color="#808080">.</font>STUnion<font color="#808080">(</font>@largeBox<font color="#808080">)<font color="#808080">;</font></font></font></p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_12.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="377" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/QLServer2008SpatialDataGettingStartedPar_105E9/image_thumb_5.png" width="817" border="0" /></a> </p> <p>Most geometry method calls don't return visual results. For example, STArea returns a scalar value for the area. By inspection, you can see that the total area of the union of the two square boxes is 12. STArea computes the area of a spatial object:</p> <font color="#ff0000"><font color="#0000ff"><font color="#ff0000"><font face="Courier New"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'</font><font face="Courier New" color="#808080">; <br /></font></font><font color="#ff0000"><font color="#ff0000"><font face="Courier New"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'</font><font face="Courier New" color="#808080">; <br /> <br /></font></font></font> <p><font face="Courier New"><font color="#0000ff">select</font> <font color="#000000">@smallBox<font color="#808080">.</font>STUnion<font color="#808080">(</font>@largeBox</font><font color="#808080">)<font color="#808080">; <br /></font></font></font><font face="Courier New" color="#000000">12</font></p> </font></font> <p>Other methods are Boolean and return either a 1 or a 0. Instead of seeing the intersection of two polygons, sometimes all you need to know is if the polygons intersect.</p> <font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@smallBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))'<font color="#808080">; <br /></font></font><font face="Courier New" color="#ff0000"><font face="Courier New" color="#ff0000"><font color="#0000ff">declare</font> <font color="#000000">@largeBox</font> <font color="#0000ff">geometry</font> <font color="#808080">=</font> 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'<font color="#808080">;</font></font></font> <p><font face="Courier New"><font color="#0000ff">select</font> @smallBox<font color="#808080">.</font>STIntersects<font color="#808080">(</font>@largeBox<font color="#808080">)<font color="#808080">; <br /><font face="Courier New" color="#000000">1</font></font></font></font></p> <p>More examples will be posted later.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=SQL Server 2008 Spatial Data - Getting Started, Part 2&amp;body=Seen on SQLblog.com: %0A%0A%09SQL Server 2008 Spatial Data - Getting Started, Part 2%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx" target="_blank" title = "Email SQL Server 2008 Spatial Data - Getting Started, Part 2">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started%2c+Part+2" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started, Part 2 to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx&amp;phase=2" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started, Part 2 to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started%2c+Part+2" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started, Part 2 to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started%2c+Part+2" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started, Part 2 to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started%2c+Part+2&amp;;top=1" target="_blank" title = "Add SQL Server 2008 Spatial Data - Getting Started, Part 2 to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9975" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspxSat, 18 Oct 2008 05:36:20 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9552John Paul Cook2http://sqlblog.com/blogs/john_paul_cook/comments/9552.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9552<p>There are all kinds of cool things you can do with SQL Server 2008 spatial data. A lot of people know that, but not that many have gotten started. Don't put it off, you can get started today using SQL Server 2008 Express. It's simple to get started. Here's how to take your first baby steps:</p> <ol> <li>Install Windows Installer 4.5 <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&amp;displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&amp;displaylang=en</a> </li> <li>Install .NET 3.5 SP1 <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&amp;displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&amp;displaylang=en</a> </li> <li>Install PowerShell <a href="http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx">http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx</a> </li> <li>Install SQL Server 2005 Express with Advanced Services <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&amp;displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&amp;displaylang=en</a>. Since you are trying to learn new things (not just spatial queries), I recommend installing all features. </li> <li>Install the AdventureWorks sample databases <a href="http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040">http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040</a> taking care to select the <b>SQL2008.AdventureWorks_OLTP_DB_v2008</b> and <b>SQL2008.AdventureWorks_DW_BI_v2008</b> files appropriate for your operating system. Once again I recommend installing all features. </li> <li>Do a spatial query. You'll need to find a spatial data column. How do you do that? The easiest way is to check the system metadata. <br /> <br /><font size="2"><font face="Courier New"><font color="#0000ff">select</font> </font></font><font size="2"><font face="Courier New"><font color="#808080">* <br /></font><font color="#0000ff">from</font> <font color="#008000">INFORMATION_SCHEMA.COLUMNS</font> <br /><font color="#0000ff">where</font> DATA_TYPE <font color="#808080">like</font> <font color="#ff0000">'geo%'</font></font></font> <br /></li> <li>The query reveals that the <b>Address</b> table in the <b>Person</b> schema has a column named <b>SpatialLocation</b> with a data type of <b>geography</b>. </li> <li>Issue a simple query against the table. <br /> <br /><font size="2"><font face="Courier New"><font color="#0000ff">select</font> <font color="#808080">*</font> <font color="#0000ff">from</font> Person<font color="#808080">.</font><font color="#0000ff">Address</font></font></font> <br /></li> <li>Notice the <b>Spatial results</b> tab. Select the <b>Robinson</b> projection. So what does the map depict? The Person.Address table contains the addresses of employees and customers around the world. Although the viewer shows only the first 5,000 rows of the 19,614 addresses in the database, you can see that the addresses define the continental United States and the east coast of Australia somewhat. Because the mouse pointer is hovering over an address stored in the table, you see the details of the row in the box that's displayed over the map surface. </li> </ol> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/SQLServer2008SpatialDataGettingStarted_887/image_4.png"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="343" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/SQLServer2008SpatialDataGettingStarted_887/image_thumb_1.png" width="846" border="0" /></a> </p> <p>The series continues with <a href="http://sqlblog.com/blogs/john_paul_cook/archive/2008/11/16/sql-server-2008-spatial-data-getting-started-part-2.aspx">part 2 here</a>.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=SQL Server 2008 Spatial Data - Getting Started&amp;body=Seen on SQLblog.com: %0A%0A%09SQL Server 2008 Spatial Data - Getting Started%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx" target="_blank" title = "Email SQL Server 2008 Spatial Data - Getting Started">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx&amp;phase=2" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started" target="_blank" title = "Submit SQL Server 2008 Spatial Data - Getting Started to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/18/sql-server-2008-spatial-data-getting-started.aspx&amp;title=SQL+Server+2008+Spatial+Data+-+Getting+Started&amp;;top=1" target="_blank" title = "Add SQL Server 2008 Spatial Data - Getting Started to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9552" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspxThu, 09 Oct 2008 16:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9388John Paul Cook3http://sqlblog.com/blogs/john_paul_cook/comments/9388.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9388<P>Everybody knows local temporary tables have scope only within a connection. Drop the connection and it goes away. The connection is at the server level, not at the database level. People commonly say things like "I'm connected to the database" when they are actually connected to the server. In SSMS, you right-click on a specific database and select the option to open a new query window. Since you initiation the connection from a specific database name in the Object Explorer, it does seem like the database owns the connection. But the database doesn't own the connection in any way.</P> <P>When you have a connection to a user database and create a local temporary table, the temporary table is created in tempdb. It's true that queries issued against your user database can access the temporary table, but they aren't related to each other, they are only sharing the same server connection. You can drop your database completely, but it does not affect any temporary objects you created in tempdb.<BR><BR></P> <P>Creating a different user database doesn't change anything if all of the work is done within the same query window in SSMS. Although you are attempting to create a temporary table from a different user database, the fact remains that tempdb is the same and is being referenced from the same connection. Referring to the code sample below, the fact that it seems like you issued the second create table statement "from" database DEMO2 is irrelevant. The temporary table is issued from the server connection, not from the database you issued the USE statement against.</P> <P>A&nbsp;user database named DEMO1 is created&nbsp; and a USE statement sets the query context to the DEMO1 database. Next, a temporary table is created. After that, the user database is dropped. Because your connection to tempdb persists, your temporary table persists. The point is that although your connection's context is the DEMO1 database, the temporary table really doesn't have anything to do with DEMO1. Although it arguably appears to have been created from demo1, it was created on the server in tempdb. The USE DEMO1 statement is irrelevant, it doesn't affect temporary objects in tempdb. The DEMO1 database doesn't in any manner have ownership of temporary objects in tempdb. Only the connection to the server owns the temporary objects. If you want to be able to create that local temporary table a second time without seeing an error message, you'll either have to drop it first or open a completely new query window in SSMS. If you don't, you'll see something like this:</P> <P><FONT face="Courier New" color=#ff0000>Msg 2714, Level 16, State 6, Line 2 <BR>There is already an object named '#demo' in the database.</FONT>&nbsp;</P> <P>Thanks to a reader for pointing out corrections to this post. Here's some code you can play with to see for yourself. </P> <P><FONT color=#0000ff>use master <BR>go </FONT></P> <P><FONT color=#0000ff>create database <FONT color=#000000>demo1 </FONT>on primary <BR><FONT color=#808080>(</FONT> <FONT color=#000000>name</FONT> <FONT color=#808080>=</FONT> <FONT color=#000000>demo1</FONT>, filename = <FONT color=#ff0000>'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1.mdf'</FONT>&nbsp;<FONT color=#808080>)&nbsp; </FONT></FONT><FONT color=#008000>-- alter filenames for your environment</FONT><FONT color=#0000ff><FONT color=#808080> <BR></FONT>&nbsp;<FONT color=#ff00ff>log</FONT> on <BR><FONT color=#808080>(</FONT> <FONT color=#000000>name</FONT> <FONT color=#808080>=</FONT> <FONT color=#000000>demo1_log</FONT>, filename = <FONT color=#ff0000>'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1_log.ldf'</FONT> <FONT color=#808080>) <BR></FONT>go </FONT></P> <P><FONT color=#0000ff>use <FONT color=#000000>demo1</FONT> <BR>go </FONT></P> <P><FONT color=#0000ff>create table <FONT color=#000000>#demo</FONT> <FONT color=#808080>(</FONT><FONT color=#000000>a</FONT> int<FONT color=#808080>)</FONT> <FONT color=#008000>-- succeeds</FONT> <BR>go </FONT></P> <P><FONT color=#0000ff>use master <BR>go </FONT></P> <P><FONT color=#0000ff>alter database <FONT color=#000000>demo1 </FONT>set single_user with rollback immediate <BR>drop database <FONT color=#000000>demo1</FONT> <BR>go </FONT></P> <P><FONT color=#0000ff>create database <FONT color=#000000>demo2 </FONT>on primary <BR><FONT color=#808080>(</FONT> <FONT color=#000000>name</FONT> <FONT color=#808080>=</FONT> <FONT color=#000000>demo2</FONT>, filename = <FONT color=#ff0000>'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2.mdf'</FONT> <FONT color=#808080>) <BR></FONT>log on <BR><FONT color=#808080>(</FONT> <FONT color=#000000>name</FONT>&nbsp;<FONT color=#808080>=</FONT> <FONT color=#000000>demo2_log</FONT>, filename = <FONT color=#ff0000>'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2_log.ldf'</FONT> <FONT color=#808080>) <BR></FONT>go </FONT></P> <P><FONT color=#0000ff>use <FONT color=#000000>demo2</FONT> <BR>go </FONT></P> <P><FONT color=#0000ff>create table <FONT color=#000000>#demo</FONT> <FONT color=#808080>(</FONT><FONT color=#000000>a</FONT> int<FONT color=#808080>)</FONT>&nbsp; <FONT color=#008000>-- fails</FONT> <BR>go </FONT></P> <P><FONT color=#0000ff>use master <BR>go </FONT></P> <P><FONT color=#0000ff>alter database <FONT color=#000000>demo2 </FONT>set single_user with rollback immediate <BR>drop database <FONT color=#000000>demo2</FONT> <BR>go</FONT></P> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Local Temporary Table Persistence&amp;body=Seen on SQLblog.com: %0A%0A%09Local Temporary Table Persistence%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx" target="_blank" title = "Email Local Temporary Table Persistence">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx&amp;title=Local+Temporary+Table+Persistence" target="_blank" title = "Submit Local Temporary Table Persistence to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx&amp;phase=2" target="_blank" title = "Submit Local Temporary Table Persistence to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx&amp;title=Local+Temporary+Table+Persistence" target="_blank" title = "Submit Local Temporary Table Persistence to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx&amp;title=Local+Temporary+Table+Persistence" target="_blank" title = "Submit Local Temporary Table Persistence to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/09/local-temporary-table-persistence.aspx&amp;title=Local+Temporary+Table+Persistence&amp;;top=1" target="_blank" title = "Add Local Temporary Table Persistence to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9388" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspxMon, 06 Oct 2008 12:50:02 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9273John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/9273.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9273<p>Download this new Microsoft whitepaper from <br /><a href="http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx">http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx</a>.</p> <p>SQL Server 2008 does perform very well in a Hyper-V environment when properly configured, which among other things means don't use the default of dynamically expanding virtual hard disk. Hyper-V isn't at all like Virtual Server or Virtual PC. It's best to drop preconceptions drawn from experience with those products.</p> <p>For best I/O performance in Hyper-V, use passthrough disks (this is when a physical disk is assigned directly to Hyper-V without using a vhd file). Fixed size virtual hard disks are a close second. Dynamically expanding disks and differencing disks provide convenience at the cost of performance and thus should not be used when I/O performance must be maximized.</p> <p>High network traffic imposes a burden on the CPU when running on Hyper-V (this is also true of Virtual Server). If a SQL Server configuration is CPU bound on bare metal, it might not have enough CPU cycles left for optimal networking performance. Also, keep in mind that a maximum of four logical processors can be assigned to a Hyper-V virtual machine, so if your SQL Server configuration needs more than four processors, it is not suitable for Hyper-V.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Running SQL Server 2008 in a Hyper-V Environment&amp;body=Seen on SQLblog.com: %0A%0A%09Running SQL Server 2008 in a Hyper-V Environment%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx" target="_blank" title = "Email Running SQL Server 2008 in a Hyper-V Environment">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx&amp;title=Running+SQL+Server+2008+in+a+Hyper-V+Environment" target="_blank" title = "Submit Running SQL Server 2008 in a Hyper-V Environment to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx&amp;phase=2" target="_blank" title = "Submit Running SQL Server 2008 in a Hyper-V Environment to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx&amp;title=Running+SQL+Server+2008+in+a+Hyper-V+Environment" target="_blank" title = "Submit Running SQL Server 2008 in a Hyper-V Environment to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx&amp;title=Running+SQL+Server+2008+in+a+Hyper-V+Environment" target="_blank" title = "Submit Running SQL Server 2008 in a Hyper-V Environment to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/10/06/running-sql-server-2008-in-a-hyper-v-environment.aspx&amp;title=Running+SQL+Server+2008+in+a+Hyper-V+Environment&amp;;top=1" target="_blank" title = "Add Running SQL Server 2008 in a Hyper-V Environment to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9273" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspxTue, 23 Sep 2008 15:39:44 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9054John Paul Cook1http://sqlblog.com/blogs/john_paul_cook/comments/9054.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9054<p>Sometimes you need to aggregate the results of multiple queries. There are several ways to accomplish this (a CTE is particularly elegant). I decided on the following solution because it provides instruction in aggregates, aliases, and when to use UNION vs. UNION ALL. <br /> <br />For this example, the COUNT aggregate function is used to find the number of rows in each of several tables. To find the total number of rows for all of the tables, you need to SUM the results of all of the COUNTs. In other words, the result sets from all of the queries need to become rows in a single table. To get the desired grand total, use the SUM aggregate on this single table.</p> <p>The following two queries return the total number of customers and the total number of employees: <br /> <br /></p> <font color="#0000ff"><font face="Courier New">select</font></font><font face="Courier New"> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font> <p><font face="Courier New">----------- <br />19820</font></p> <p><font face="Courier New">(1 row(s) affected)</font></p> <p><font face="Courier New">----------- <br />290</font></p> <p><font face="Courier New">(1 row(s) affected)</font></p> <p>Because the queries are identical in the number of columns and data types returned, they can be combined using the UNION operator. Simplistic and na&#239;ve testing suggests that this may work: <br /> <br /></p> <font color="#0000ff"><font face="Courier New">select</font></font><font face="Courier New"> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">UNION</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font><font face="Courier New"> <br /> <br />----------- <br />290 <br />19820</font> <p><font face="Courier New">(2 row(s) affected)</font></p> <p>UNION is not the correct operator to use as this test case reveals:</p> <p><font face="Courier New"><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee <br /></font><font face="Courier New"><font color="#0000ff">UNION</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font></p> <p><font face="Courier New">----------- <br />290</font></p> <p><font face="Courier New">(1 row(s) affected)</font></p> <p>UNION contains an implied DISTINCT operator that causes identical rows to disappear. This can lead to miscounts when SUM is used later. The correct operator to use is UNION ALL:</p> <p><font face="Courier New"><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee <br /></font><font face="Courier New"><font color="#0000ff">UNION</font> <font color="#808080">ALL</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font> </p> <p><font face="Courier New">----------- <br />290 <br />290</font></p> <p><font face="Courier New">(2 row(s) affected)</font></p> <p>Now we know that we need to build on the following query:</p> <font color="#0000ff"> <br /><font face="Courier New">select</font></font><font face="Courier New"> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">UNION</font> <font color="#808080">ALL</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font> <p>We need to SUM the results of our UNION ALL query, but SUM requires a column name. That means aliases (nrows) must be used.</p> <font face="Courier New"><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">UNION</font> <font color="#808080">ALL</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee</font> <p><font face="Courier New">nrows <br />----------- <br />19820 <br />290</font></p> <p><font face="Courier New">(2 row(s) affected)</font></p> <p>To query from our new result set, we once again must use an alias (counts) for our tabular result set to make the SELECT * FROM work:</p> <p><font face="Courier New"><font color="#0000ff">select</font> * <font color="#0000ff">from <br /></font></font><font face="Courier New"><font color="#808080">(</font><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">UNION</font> <font color="#808080">ALL</font> <br /><font color="#0000ff">select</font> <font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee<font color="#808080">)</font> <font color="#0000ff">as</font> counts</font> </p> <p><font face="Courier New">nrows <br />----------- <br />19820 <br />290</font></p> <p><font face="Courier New">(2 row(s) affected)</font></p> <p>To finish up, replace the wildcard with the SUM aggregate. Another alias (GrandTotal) is used (but not required) to provide a descriptive column header.</p> <p><font face="Courier New"><font color="#0000ff">select</font> <font color="#ff00ff">SUM</font>(nrows) <font color="#0000ff">as</font> GrandTotal <font color="#0000ff">from</font> <br /><font color="#808080">(</font><font face="Courier New"><font color="#0000ff">select</font> </font><font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>Sales<font color="#808080">.</font>Customer <br /><font color="#0000ff">UNION</font> <font color="#808080">ALL</font> <br /><font face="Courier New"><font color="#0000ff">select</font> </font><font color="#ff00ff">COUNT</font><font color="#808080">(*)</font> <font color="#0000ff">as</font> nrows <font color="#0000ff">from </font>HumanResources<font color="#808080">.</font>Employee<font color="#808080">)</font> <font color="#0000ff">as</font> counts</font></p> <p><font face="Courier New">GrandTotal <br />----------- <br />20110</font></p> <p><font face="Courier New">(1 row(s) affected)</font></p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Aggregating Aggregates and UNION vs. UNION ALL&amp;body=Seen on SQLblog.com: %0A%0A%09Aggregating Aggregates and UNION vs. UNION ALL%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx" target="_blank" title = "Email Aggregating Aggregates and UNION vs. UNION ALL">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx&amp;title=Aggregating+Aggregates+and+UNION+vs.+UNION+ALL" target="_blank" title = "Submit Aggregating Aggregates and UNION vs. UNION ALL to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx&amp;phase=2" target="_blank" title = "Submit Aggregating Aggregates and UNION vs. UNION ALL to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx&amp;title=Aggregating+Aggregates+and+UNION+vs.+UNION+ALL" target="_blank" title = "Submit Aggregating Aggregates and UNION vs. UNION ALL to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx&amp;title=Aggregating+Aggregates+and+UNION+vs.+UNION+ALL" target="_blank" title = "Submit Aggregating Aggregates and UNION vs. UNION ALL to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/23/aggregating-aggregates-and-union-vs-union-all.aspx&amp;title=Aggregating+Aggregates+and+UNION+vs.+UNION+ALL&amp;;top=1" target="_blank" title = "Add Aggregating Aggregates and UNION vs. UNION ALL to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9054" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspxSun, 21 Sep 2008 13:26:57 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9017John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/9017.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=9017<p>The best tool for converting virtual machines to Hyper-V is System Center Virtual Machine Manager 2008, which is available in October 2008. It has a physical to virtual (P2V) tool that works quite well. Unfortunately, it has to be installed on a domain joined machine, so it's not suitable for ad hoc, spur of the moment P2V conversions done by anyone, any time, anywhere. Sometimes you have just one virtual machine on Virtual PC or Virtual Server you need to migrate to Hyper-V. If you don't have a P2V tool, you have to recreate all of the settings because Hyper-V doesn't use vmc files.</p> <p>Matthijs ten Seldam wrote a vmc converter utility to simplify the creation of settings for a Hyper-V virtual machine. Read about it here: <a title="http://blogs.technet.com/matthts/archive/2008/09/12/vmc-to-hyper-v-import-tool-available.aspx" href="http://blogs.technet.com/matthts/archive/2008/09/12/vmc-to-hyper-v-import-tool-available.aspx">http://blogs.technet.com/matthts/archive/2008/09/12/vmc-to-hyper-v-import-tool-available.aspx</a>. You still have to deal with HAL issues, for which a complete article would be required to discuss in depth. If your virtual machine's operating system is Vista or 2008 Server, the HAL problem is easy to deal with because those operating systems have a new option in <strong>msconfig</strong> to force HAL detection. It's accessed by clicking the <strong>Advanced Options</strong> button on the <strong>Boot</strong> tab. You use this after booting your virtual machine in Hyper-V for the first time.</p> <p>When you manually migrate a VPC or Virtual Server vm to Hyper-V, the basic approach is to do this:</p> <ol> <li>Copy the vhd and work with the copy.</li> <li>Uninstall the Virtual Machine Additions.</li> <li>Copy the modified vhd to the Hyper-V machine.</li> <li>Use the wizard to create a new virtual machine from the vhd or use the vmc converter.</li> <li>Resolve any HAL problems.</li> <li>Install Integration Services.</li> </ol> <p>If you have access to SCVMM 2008 RTM, you can install it into a Hyper-V virtual machine if that is more convenient for you. You can even have it in its own virtual domain on your laptop or desktop. I find this approach more convenient and agile for ad hoc work than having it part of a production domain.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Moving Virtual Machines to Hyper-V&amp;body=Seen on SQLblog.com: %0A%0A%09Moving Virtual Machines to Hyper-V%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx" target="_blank" title = "Email Moving Virtual Machines to Hyper-V">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx&amp;title=Moving+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Moving Virtual Machines to Hyper-V to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx&amp;phase=2" target="_blank" title = "Submit Moving Virtual Machines to Hyper-V to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx&amp;title=Moving+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Moving Virtual Machines to Hyper-V to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx&amp;title=Moving+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Moving Virtual Machines to Hyper-V to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/09/21/moving-virtual-machines-to-hyper-v.aspx&amp;title=Moving+Virtual+Machines+to+Hyper-V&amp;;top=1" target="_blank" title = "Add Moving Virtual Machines to Hyper-V to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=9017" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspxFri, 15 Aug 2008 13:30:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:8397John Paul Cook1http://sqlblog.com/blogs/john_paul_cook/comments/8397.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=8397<P>Scripting installations of SQL Server 2008 is much easier than it is with SQL Server 2005 because of the <STRONG>ConfigurationFile.ini</STRONG>. Even if you don't script your installations, you should review the contents of this file to verify that the installation transpired as intended. Although it is possible to script a SQL Server 2005 installation, you can't capture an interactive installation and replay it. When&nbsp;SQL Server 2008 is installed, a <STRONG>ConfigurationFile.ini</STRONG> file is created in <STRONG>C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<EM>yyyymmdd_hhmmss</EM></STRONG>. To use <STRONG>ConfigurationFile.ini</STRONG> with the installation wizard, select <STRONG>Advanced</STRONG> and <STRONG>Install based on configuration file</STRONG>. If you need to make changes to the file before performing another installation, it is a best practice to make a copy and work with the copy. From an auditing and compliance perspective, each installation's <STRONG>ConfigurationFile.ini</STRONG> file should be preserved in its original state as verification of the initial state of the SQL Server installation.</P> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=SQL 2008 ConfigurationFile.ini file - not just for unattended installs&amp;body=Seen on SQLblog.com: %0A%0A%09SQL 2008 ConfigurationFile.ini file - not just for unattended installs%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx" target="_blank" title = "Email SQL 2008 ConfigurationFile.ini file - not just for unattended installs">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx&amp;title=SQL+2008+ConfigurationFile.ini+file+-+not+just+for+unattended+installs" target="_blank" title = "Submit SQL 2008 ConfigurationFile.ini file - not just for unattended installs to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx&amp;phase=2" target="_blank" title = "Submit SQL 2008 ConfigurationFile.ini file - not just for unattended installs to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx&amp;title=SQL+2008+ConfigurationFile.ini+file+-+not+just+for+unattended+installs" target="_blank" title = "Submit SQL 2008 ConfigurationFile.ini file - not just for unattended installs to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx&amp;title=SQL+2008+ConfigurationFile.ini+file+-+not+just+for+unattended+installs" target="_blank" title = "Submit SQL 2008 ConfigurationFile.ini file - not just for unattended installs to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx&amp;title=SQL+2008+ConfigurationFile.ini+file+-+not+just+for+unattended+installs&amp;;top=1" target="_blank" title = "Add SQL 2008 ConfigurationFile.ini file - not just for unattended installs to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=8397" width="1" height="1">SQL Server 2008installationhttp://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspxTue, 08 Jul 2008 02:46:46 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7706John Paul Cook2http://sqlblog.com/blogs/john_paul_cook/comments/7706.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7706<p>The official list of supported operating systems, service packs, and numbers of virtual processors can be found here: <a href="http://support.microsoft.com/kb/954958/en-us">http://support.microsoft.com/kb/954958/en-us</a>. If you are willing to venture into unsupported territory, you might find some surprises. I have both 32 and 64 bit Windows 2003 R2 Service Pack 2 virtual machines running with four virtual processors. The same is true of Vista Service Pack 1.</p> <p>Also of interest is what happens with PAE. Hyper-V requires that hardware Data Execution Prevention (DEP) be enabled in the BIOS. Intel calls this hardware feature the execute disable (XD) bit. AMD calls it no-execute page protection (NX). When DEP is enabled, Windows automatically enables Physical Address Extension (PAE) without having /PAE in the boot.ini. Because of this, your 32 bit guests will work with large ram as the screen captures show. For more information, see <a title="http://support.microsoft.com/kb/875352" href="http://support.microsoft.com/kb/875352">http://support.microsoft.com/kb/875352</a>.</p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/HyperVundocumentedandunsupportedfeatures_13211/PAE_2.jpg"><img style="border-top-width:0px;border-left-width:0px;border-bottom-width:0px;border-right-width:0px;" height="478" alt="PAE" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/HyperVundocumentedandunsupportedfeatures_13211/PAE_thumb.jpg" width="814" border="0" /></a></p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Hyper-V undocumented and unsupported features&amp;body=Seen on SQLblog.com: %0A%0A%09Hyper-V undocumented and unsupported features%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx" target="_blank" title = "Email Hyper-V undocumented and unsupported features">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx&amp;title=Hyper-V+undocumented+and+unsupported+features" target="_blank" title = "Submit Hyper-V undocumented and unsupported features to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx&amp;phase=2" target="_blank" title = "Submit Hyper-V undocumented and unsupported features to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx&amp;title=Hyper-V+undocumented+and+unsupported+features" target="_blank" title = "Submit Hyper-V undocumented and unsupported features to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx&amp;title=Hyper-V+undocumented+and+unsupported+features" target="_blank" title = "Submit Hyper-V undocumented and unsupported features to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/07/07/hyper-v-undocumented-and-unsupported-features.aspx&amp;title=Hyper-V+undocumented+and+unsupported+features&amp;;top=1" target="_blank" title = "Add Hyper-V undocumented and unsupported features to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7706" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspxThu, 26 Jun 2008 18:24:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7526John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/7526.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7526<P>Now that Hyper-V is RTM, why should you care? Because it performs very well! If you do demos using virtual machines on a laptop, do yourself a favor and upgrade to Hyper-V. If you have a production data center with lots of virtual machines, you might want to wait until System Center Virtual Machine Manager (SCVMM) is available. Management of virtual assets is critical in a production environment. SCVMM 2007 does not manage Hyper-V machines.</P> <P>If you are currently running virtual machines using Virtual PC or Virtual Server, you'll want to preserve your existing virtual hard drive (vhd) assets. You can do this manually be following these steps:</P> <P>1. Make a copy of your vhd.<BR>2.&nbsp;Run the vhd in Virtual PC or Virtual Server.<BR>3.&nbsp;If Hyper-V requires a higher service pack, apply&nbsp;it now.<BR>4. Remove the&nbsp;Virtual Machine Additions.<BR>5. Save and permanently commit the changes.<BR>6.&nbsp;Make the updated vhd available to Hyper-V (e.g., copy the vhd to the Hyper-V physical machine, put it on&nbsp;your SAN, etc.)<BR>7. Create a new Hyper-V virtual machine&nbsp;using the updated vhd.<BR>8. Install the Integration&nbsp;Services into the virtual machine.</P> <P>Must you apply a service pack while you are still in the Virtual PC or Virtual Server environment? It depends. There have been times I've seen the mouse unavailable in Hyper-V until Integration Services was installed. But Integration Services can't be installed until the machine is at the correct service pack level. Since Integration Services makes the mouse available but requires the service pack, you can be stuck installing a service pack without a mouse. It's a Catch-22. Avoid the trouble, install the service pack before moving to Hyper-V.</P> <P>With RTM, more operating systems and service packs are supported. XP service pack 2 is now supported. If you want to run XP using two virtual processors instead of one, you'll still need to upgrade to service pack 3. Windows 2000 with service pack 4 is now supported, which is great for those legacy machines running older versions of SQL Server.</P> <P>I'm running the RTM version of Hyper-V on my dual core laptop. Performance is excellent. My laptop is set to single boot into Windows 2008 Enterprise x64. It has the desktop experience, Aero, and audio enabled. The only feature it doesn't have that&nbsp;I miss is Windows Media Center.</P> <P>See John Howard's blog for version number information. <A href="http://blogs.technet.com/jhoward/archive/2008/06/26/hyper-v-rtm-announcement-available-today-from-the-microsoft-download-centre.aspx">http://blogs.technet.com/jhoward/archive/2008/06/26/hyper-v-rtm-announcement-available-today-from-the-microsoft-download-centre.aspx</A></P> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Hyper-V is RTM and How to Migrate Existing VHD Files&amp;body=Seen on SQLblog.com: %0A%0A%09Hyper-V is RTM and How to Migrate Existing VHD Files%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx" target="_blank" title = "Email Hyper-V is RTM and How to Migrate Existing VHD Files">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx&amp;title=Hyper-V+is+RTM+and+How+to+Migrate+Existing+VHD+Files" target="_blank" title = "Submit Hyper-V is RTM and How to Migrate Existing VHD Files to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx&amp;phase=2" target="_blank" title = "Submit Hyper-V is RTM and How to Migrate Existing VHD Files to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx&amp;title=Hyper-V+is+RTM+and+How+to+Migrate+Existing+VHD+Files" target="_blank" title = "Submit Hyper-V is RTM and How to Migrate Existing VHD Files to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx&amp;title=Hyper-V+is+RTM+and+How+to+Migrate+Existing+VHD+Files" target="_blank" title = "Submit Hyper-V is RTM and How to Migrate Existing VHD Files to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/26/hyper-v-is-rtm-and-how-to-migrate-existing-vhd-files.aspx&amp;title=Hyper-V+is+RTM+and+How+to+Migrate+Existing+VHD+Files&amp;;top=1" target="_blank" title = "Add Hyper-V is RTM and How to Migrate Existing VHD Files to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7526" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspxThu, 12 Jun 2008 03:59:09 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7259John Paul Cook4http://sqlblog.com/blogs/john_paul_cook/comments/7259.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7259<p>I've previously blogged about a DBA toolkit on a USB stick. Now you can get the Sysinternals tools from the cloud. Just browse to <a title="http://live.sysinternals.com/" href="http://live.sysinternals.com/">http://live.sysinternals.com/</a> and click on the tool you need. It doesn't install, it just runs. Below is a screen capture of Process Monitor running from the the Sysinternals web page where Procmon.exe was clicked.</p> <p><a href="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/DBAToolkitfromtheCloud_1433C/image_2.png"><img style="border-right:0px;border-top:0px;border-left:0px;border-bottom:0px;" height="302" alt="image" src="http://sqlblog.com/blogs/john_paul_cook/WindowsLiveWriter/DBAToolkitfromtheCloud_1433C/image_thumb.png" width="501" border="0" /></a> </p> <p>Alternatively, you can run tools from a command prompt using syntax following this pattern:</p> <p><font size="2"><font face="Courier New"><font color="#808080">C:\Windows\system32&gt;</font><strong>\\live.sysinternals.com\tools\procmon</strong></font></font></p> <p>If you are running Vista with UAC enabled, you should run your command prompt as administrator because you'll get your results much faster. Running from a non-privileged command prompt is noticeably slower.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=DBA Toolkit from the Cloud&amp;body=Seen on SQLblog.com: %0A%0A%09DBA Toolkit from the Cloud%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx" target="_blank" title = "Email DBA Toolkit from the Cloud">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx&amp;title=DBA+Toolkit+from+the+Cloud" target="_blank" title = "Submit DBA Toolkit from the Cloud to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx&amp;phase=2" target="_blank" title = "Submit DBA Toolkit from the Cloud to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx&amp;title=DBA+Toolkit+from+the+Cloud" target="_blank" title = "Submit DBA Toolkit from the Cloud to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx&amp;title=DBA+Toolkit+from+the+Cloud" target="_blank" title = "Submit DBA Toolkit from the Cloud to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/11/dba-toolkit-from-the-cloud.aspx&amp;title=DBA+Toolkit+from+the+Cloud&amp;;top=1" target="_blank" title = "Add DBA Toolkit from the Cloud to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7259" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspxSun, 08 Jun 2008 09:42:01 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7190John Paul Cook3http://sqlblog.com/blogs/john_paul_cook/comments/7190.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7190<p>With SQL Server 2008 RC0 available for download now, people are asking about Hyper-V versus Virtual PC and Virtual Server. For best performance, I recommend that you build a Hyper-V virtual machine. This raises a question about convenience. What if you have existing Virtual PC or Virtual Server virtual machines and want to migrate them to Hyper-V? It's easy if you understand what is required.</p> <p>You must remove the Virtual Machine Additions before you use your Virtual PC or Virtual Server vhd as the basis of a Hyper-V virtual machine. You also must have the virtual machine at the correct service pack level to take advantage of the performance optimizations Hyper-V Integration Services provide. (People have already posted about confusing SSIS and Hyper-V when only the words &quot;Integration Services&quot; are used.)</p> <p>Currently in Hyper-V RC1, these are the only Windows operating systems and patch levels that can take advantage of Integration Services (subject to change at RTM):</p> <ol> <li>XP Service Pack 3</li> <li>2003 Service Pack 2</li> <li>Vista Service Pack 1</li> <li>Windows 2008</li> </ol> <p>To avoid a Catch-22, you should install any required service pack in Virtual PC or Virtual Server before uninstalling the Virtual Machine Additions. If you uninstall the Additions before installing the service pack, installation of the service pack will take longer. If you install the service pack after switching to Hyper-V, you won't have mouse control until you've installed Integration Services, which requires that the service pack already be applied.</p> <p>Keep in mind that Hyper-V RC0 Integration Services worked only with Windows 2003 and 2008. RC1 added XP and Vista. RTM might support more operating systems and service pack levels.</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Upgrading Your Virtual Machines to Hyper-V&amp;body=Seen on SQLblog.com: %0A%0A%09Upgrading Your Virtual Machines to Hyper-V%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx" target="_blank" title = "Email Upgrading Your Virtual Machines to Hyper-V">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx&amp;title=Upgrading+Your+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Upgrading Your Virtual Machines to Hyper-V to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx&amp;phase=2" target="_blank" title = "Submit Upgrading Your Virtual Machines to Hyper-V to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx&amp;title=Upgrading+Your+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Upgrading Your Virtual Machines to Hyper-V to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx&amp;title=Upgrading+Your+Virtual+Machines+to+Hyper-V" target="_blank" title = "Submit Upgrading Your Virtual Machines to Hyper-V to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/08/upgrading-your-virtual-machines-to-hyper-v.aspx&amp;title=Upgrading+Your+Virtual+Machines+to+Hyper-V&amp;;top=1" target="_blank" title = "Add Upgrading Your Virtual Machines to Hyper-V to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7190" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspxWed, 04 Jun 2008 13:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7133John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/7133.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7133<P>On the first day of TechEd for developers, I answered quite a few questions about Hyper-V. Today from 11:00 a.m. - 12:30 p.m. (Wednesday June 4, 2008) and tomorrow from 1:30 - 2:30 p.m. I'm presenting on incorporating Hyper-V into the application lifecycle management process. Look for me at the Technical Learning Center's Red Section. I'll talk about agile testing using Hyper-V and using virtualization to shorten the systems development lifecycle.</P> <P>Yesterday a few people asked about virtualizing SQL Server. It all depends on the workload. Many small and medium businesses are running SQL Server production systems on Virtual Server today. Hyper-V provides greatly improved performance over Virtual Server. However, no matter who the vendor is or what the platform is, virtualization does introduce some overhead. Because of that, it's not suitable for building an OLAP cube or high volume OLTP.</P> <P>Another questions concerned the migration of physical machines into a virtual environment. This is known as P2V, short for physical to virtual. Microsoft's System Center Virtual Machine Manager (SCVMM) includes a P2V tool for migrating an entire physical machine into Hyper-V. For those who don't purchase SCVMM, there are third party P2V products. Acronis True Image with Universal Restore is one of them.</P> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Hyper-V at TechEd, Questions and Answers&amp;body=Seen on SQLblog.com: %0A%0A%09Hyper-V at TechEd, Questions and Answers%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx" target="_blank" title = "Email Hyper-V at TechEd, Questions and Answers">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx&amp;title=Hyper-V+at+TechEd%2c+Questions+and+Answers" target="_blank" title = "Submit Hyper-V at TechEd, Questions and Answers to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx&amp;phase=2" target="_blank" title = "Submit Hyper-V at TechEd, Questions and Answers to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx&amp;title=Hyper-V+at+TechEd%2c+Questions+and+Answers" target="_blank" title = "Submit Hyper-V at TechEd, Questions and Answers to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx&amp;title=Hyper-V+at+TechEd%2c+Questions+and+Answers" target="_blank" title = "Submit Hyper-V at TechEd, Questions and Answers to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/04/hyper-v-at-teched-questions-and-answers.aspx&amp;title=Hyper-V+at+TechEd%2c+Questions+and+Answers&amp;;top=1" target="_blank" title = "Add Hyper-V at TechEd, Questions and Answers to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7133" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspxTue, 03 Jun 2008 15:03:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7118John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/7118.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7118<P>It's early in the morning here at the TechEd conference for developers in Orlando, Florida. I've already helped two people with Hyper-V problems caused by incompatible version numbers. Since blog posts live on long past the date of publication, I'm going to speak about the root cause of the problem and not get into the specific KB articles and patches.</P> <P>Here is what you need to know. Windows 2008 Server comes with the Hyper-V beta bits, which are quite outdated now. If you enable the Hyper-V role from original Windows 2008 Server media, you'll need to update Hyper-V from beta to what is current. Windows Update will provide the necessary update or you can find the current patch and manually apply the update. Once you update pre-RTM Hyper-V, any Hyper-V virtual machines need to be updated to&nbsp;the Integration Services components in your updated Hyper-V.</P> <P>As of today, Hyper-V is at RC1. It is incompatible with the current beta of SCVMM 2008. The SCVMM 2008 beta version available now is compatible with the RC0 version of Hyper-V.</P> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Using Hyper-V? Be careful of versions and patches.&amp;body=Seen on SQLblog.com: %0A%0A%09Using Hyper-V? Be careful of versions and patches.%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx" target="_blank" title = "Email Using Hyper-V? Be careful of versions and patches.">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx&amp;title=Using+Hyper-V%3f+Be+careful+of+versions+and+patches." target="_blank" title = "Submit Using Hyper-V? Be careful of versions and patches. to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx&amp;phase=2" target="_blank" title = "Submit Using Hyper-V? Be careful of versions and patches. to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx&amp;title=Using+Hyper-V%3f+Be+careful+of+versions+and+patches." target="_blank" title = "Submit Using Hyper-V? Be careful of versions and patches. to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx&amp;title=Using+Hyper-V%3f+Be+careful+of+versions+and+patches." target="_blank" title = "Submit Using Hyper-V? Be careful of versions and patches. to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/06/03/using-hyper-v-be-careful-of-versions-and-patches.aspx&amp;title=Using+Hyper-V%3f+Be+careful+of+versions+and+patches.&amp;;top=1" target="_blank" title = "Add Using Hyper-V? Be careful of versions and patches. to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7118" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspxSat, 31 May 2008 19:48:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7093John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/7093.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=7093<p>It's frustrating when you encounter a bug and even more frustrating when customer support won't help you. Windows Media Encoder is a great way to document bugs that are particularly difficult to describe.</p> <p>In case you don't know, the place to file bug reports on Microsoft products is <a title="http://connect.microsoft.com/" href="http://connect.microsoft.com/">http://connect.microsoft.com/</a>. It should be obvious that a clear statement of the problem and a complete and detailed list of the steps you undertook are essential to filing a good bug report. If your problem isn't understood or can't be reproduced, resolution is unlikely. Sometimes words just fail us. There really isn't a way to effectively describe some bugs using written language. When words fail, make a movie - lights, camera, action!</p> <p>It's really easy to make a video screen capture demonstrating a bug and showing every single step you followed. Go to <a title="http://www.microsoft.com/windows/windowsmedia/forpros/encoder/default.mspx" href="http://www.microsoft.com/windows/windowsmedia/forpros/encoder/default.mspx">http://www.microsoft.com/windows/windowsmedia/forpros/encoder/default.mspx</a> and download Windows Media Encoder. Notice there is a 64-bit version in case you have a 64-bit OS. As the saying goes, a picture is worth a thousand bytes. The Connect site has a feature that allows you to upload files and attach them to your bug reports. Use this to upload your video file to Microsoft.</p> <p>Before you make your video, plan ahead. Think of how you can enhance it. I do not record my voice to emphasize key steps and outcomes. Instead, I use Notepad and mouse movements to highlight key steps. For example, if clicking a certain button triggers the bug, I move the mouse cursor back and forth above the button a few times before clicking the button. Notepad is handy when you need to demonstrate a problem with a password, for example. Quicken has a bug with using special characters in a password. In one dialog box, it is possible to have a password containing special characters. In another, it is not possible. This makes it impossible to get one feature to work. To make it clear what the problem was, I put various passwords into Notepad so that all of the characters in the passwords could be seen. I used copy/paste to transfer the passwords from Notepad into the application's dialog boxes. If I had just typed the passwords into the dialog boxes, it wouldn't have been obvious which special characters were being entered because of password masking.</p> <p>Unfortunately, no matter how good your video is, it won't help when customer service is unresponsive. I created a video and sent it to Intuit to demonstrate the password bug. They haven't helped resolve the bug or even acknowledged that the bug exists. On this and another unresolved Quicken bug, Intuit has sent multiple &quot;Quicken Customer Care Survey&quot; emails asking me questions about my experiences with their customer support staff. I dutifully respond that my problems are unresolved and they don't respond. What is really interesting about the emails they send is that the emails actually end with the following <em>text</em>:</p> <blockquote> <p>6) Please provide any additional comments about your customer support experience.</p> </blockquote> <blockquote> <p>[Submit]&#160; [Cancel]&#160; [Clear]</p> </blockquote> <p>Understand that it is text. No buttons (not that they would work without InfoPath or some other enabling technology), just text that says [Submit].</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=How to File Better Bug Reports&amp;body=Seen on SQLblog.com: %0A%0A%09How to File Better Bug Reports%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx" target="_blank" title = "Email How to File Better Bug Reports">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx&amp;title=How+to+File+Better+Bug+Reports" target="_blank" title = "Submit How to File Better Bug Reports to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx&amp;phase=2" target="_blank" title = "Submit How to File Better Bug Reports to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx&amp;title=How+to+File+Better+Bug+Reports" target="_blank" title = "Submit How to File Better Bug Reports to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx&amp;title=How+to+File+Better+Bug+Reports" target="_blank" title = "Submit How to File Better Bug Reports to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/31/how-to-file-better-bug-reports.aspx&amp;title=How+to+File+Better+Bug+Reports&amp;;top=1" target="_blank" title = "Add How to File Better Bug Reports to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=7093" width="1" height="1">http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspxMon, 26 May 2008 20:29:34 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:6985John Paul Cook0http://sqlblog.com/blogs/john_paul_cook/comments/6985.aspxhttp://sqlblog.com/blogs/john_paul_cook/commentrss.aspx?PostID=6985<p>Overclocking is a line most people won't cross. Many perceive the risk or inconvenience as too high, but the performance gains can be significant. It's not necessary to resort to water cooling or other extreme measures. The Q6600 quad core cpu from Intel can be overclocked if you don't mind removing it from its socket. Since my machines were built by me, I didn't mind doing this. By covering one of the pins on the Q6600 with electrical tape before reinserting it, the front side bus (FSB) goes from 1066 MHz to 1333 MHz. See <a title="http://forums.nvidia.com/lofiversion/index.php?t58361.html" href="http://forums.nvidia.com/lofiversion/index.php?t58361.html">http://forums.nvidia.com/lofiversion/index.php?t58361.html</a> for more details. Why not overclock by changing settings in the BIOS instead? Not all motherboards have BIOS options for overclocking. Under heavy load, my Q6600 now hits 3 GHz instead of its official maximum of 2.4 GHz. I haven't observed higher processor temperatures.</p> <p>It's great being able to get my work done faster. If only someone would figure out some pin mods for laptops that have removable processors!</p> <BR><div class = "shareblock"><span class = "shareblockTitle">Share this post:</span><span class = "shareblockLink"> <a href = "mailto:?subject=Overclocking made simple&amp;body=Seen on SQLblog.com: %0A%0A%09Overclocking made simple%0A%0Ahttp://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx" target="_blank" title = "Email Overclocking made simple">email it!</a> | <a href = "http://del.icio.us/post?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx&amp;title=Overclocking+made+simple" target="_blank" title = "Submit Overclocking made simple to del.icio.us">bookmark it!</a> | <a href = "http://www.digg.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx&amp;phase=2" target="_blank" title = "Submit Overclocking made simple to digg.com">digg it!</a> | <a href = "http://reddit.com/submit?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx&amp;title=Overclocking+made+simple" target="_blank" title = "Submit Overclocking made simple to reddit.com">reddit!</a> | <a href = "http://www.dotnetkicks.com/submit/?url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx&amp;title=Overclocking+made+simple" target="_blank" title = "Submit Overclocking made simple to DotNetKicks">kick it!</a> | <a href = "https://favorites.live.com/quickadd.aspx?marklet=1&amp;mkt=en-us&amp;url=http://sqlblog.com/blogs/john_paul_cook/archive/2008/05/26/overclocking-made-simple.aspx&amp;title=Overclocking+made+simple&amp;;top=1" target="_blank" title = "Add Overclocking made simple to Live Bookmarks">live it!</a></span></div><img src="http://info.sqlblog.com/a.aspx?ZoneID=0&BannerID=12&AdvertiserID=1&CampaignID=12&Task=Get&Mode=TEXT&SiteID=1&RandomNumber=463323" width="1" height="1" border="0"><img src="http://sqlblog.com/aggbug.aspx?PostID=6985" width="1" height="1">