Modifying SQL Server 2005 Port Numbers

And so the tests continues...One of the things that I need to test during the upgrade was database connectivity. We know the standard osql, sqlcmd, isql and Query Analyzer tests from a remote client where you supply either a Windows or SQL Server account to login to the database server. Once connected, we are practically sure that database connectivity works fine. But that doesn't stop there. We need to make sure that other applications can connect to the database server as well. This is where the importance of SQL Server port numbers come into the picture. We usually recommend to change the default port numbers 1433 and 1434 for security reasons as anybody who knows SQL Server has a high probability of knowing this port. How do we change the port number that SQL Server uses. Open SQL Server Configuration Manger -> SQL Server 2005 Network Configuration -> Protocols for InstanceName. Open the TCP/IP protocol properties either by right-clicking and selecting Properties or double-clicking. Go into the IP Addresses tab of the TCP/IP Properties window, scroll all the way to the bottom to the IPAll section, change TCP Dynamic Ports to be blank and put any value, say 1234, or whatever your chosen SQL Server port number is, in to the TCP Port box (I just used 1433 for ease of explanation). Make sure you restart your SQL Server service for the changes to take effect.



NOTE: If the value of Listen All on the Protocol tab is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll. If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.

Upgrading to SQL Server 2005 from SQL Server 2000 or 7.0?

What a way to get back to work after Chinese New Year! We need to migrate an existing Windows 2000 Advanced Server with SQL Server 2000 to a new Windows Server 2003 with SQL Server 2005. Now, I've done a lot of SQL Server 2005 installations in the past but they are practically effortless as they were all new installations. You just need to plan properly and everything goes as plan. This one is totally different. We have an existing application which needs to be moved from an old box to a new one. One way to find out about the potential database issues during the upgrade is to use the SQL Server 2005 Upgrade Advisor. This tool analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. You install it on the existing SQL Server 2000 machine before you even start moving the databases to the new SQL Server 2005 box. For a more detailed information on your upgrade strategies, you can download the SQL Server 2005 Upgrade Technical Resource Guide. This document contains guidance for SQL Server administrators, developers, and IT decision makers who want to move their older version of SQL Server databases (v6.5 is not included) to SQL Server 2005.

Now, let's see how we go this coming weekend. I'm crossing my fingers as the server also contains an ASP.NET web service running on .NET Framework 1.1. For those .NET developers out there, I think you know what I'm trying to drive at.

Error loading AJAX Control Toolkit in Visual Studio 2008

I have used AJAX in ASP.NET way back when it was still codenamed ATLAS. In Visual Studio 2008, AJAX has already been integrated as part of the framework and is now easy to use unlike when it was still called ATLAS. Unfortunately, though, the AJAX Control Toolkit isn't. It is a shared source project built on top of the Microsoft ASP.NET AJAX framework and is a joint effort between Microsoft and the ASP.NET AJAX community that provides a powerful infrastructure to write reusable, customizable and extensible ASP.NET AJAX extenders and controls, as well as a rich array of controls that can be used out of the box to create an interactive Web experience. The fact that it is a "community" project makes it a non-Microsoft supported product. But, of course, nobody is stopping you from taking full advantage of the toolkit.

If you're like me who has both Visual Studio 2005 and 2008 running on the same machine, chances are that you will start using this toolkit. You simply download the files from CodePlex and add the assembly in your Toolbox. The challenge begins when you realized that you downloaded the incorrect version and you get this error message or something similar

There was an error loading types from assembly 'C:\Program Files\AJAX Control Toolkit\Binaries\AjaxControlToolkit.dll''Could not load file or assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture= neutral, PublicKey Token=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.'

Make sure that you downloaded the one with the Framework3.5 in the filename if you plan to use it in Visual Studio 2008. Otherwise, use the AJAX v1.0 for ASP.NET v2.0

LINQ to SQL Debug Visualizer in Visual Studio 2008

There are debug visualizers in Visual Studio which helps debugging code a lot easier for developers. I first encountered this in Visual Studio .NET 2003 (if I can remember correctly) and I can simply display my debugger text, HTML or in XML. I normally use this whenever I pass parameter variables or even SQL statements just to check if they were indeed passed correctly. Now that we have LINQ in .NET Framework 3.5, I was thinking that Visual Studo 2008 will have the same thing. Unfortunately, out-of-the-box Visual Studio 2008 does not have it. Good thing Scott Guthrie, a program manager at Microsoft, blogged about LINQ to SQL Debug Visualizer. This is the ability to hover over a LINQ expression while in the debugger mode and check the raw SQL that the compiler will ultimately execute at runtime when evaluating the LINQ query expression. You can even execute the SQL code and see the generated result.

Like I said, it does not come out-of-the-box in Visual Studio 2008. Scott provided the source code and assembly to integrate it inside Visual Studio 2008. To install the LINQ to SQL Debug Visualizer component, do the following

1. Close all running versions of Visual Studio 2008
2. Copy the SqlServerQueryVisualizer.dll assembly from the \bin\debug\ directory in the .zip download above into your local \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\ directory
3. Open Visual Studio 2008 again.

Now, when you use the debugger with your LINQ to SQL code, you should be able to hover over your LINQ query expressions and have the LINQ to SQL Debug Visualizer option available for you. You can even see the results of your query if you wish to execute it prior to seeing the results on your application. Go try it out now and see for yourselves