Stop using SQL Server path’s work with a SQL Alias

A lot of times i run into customers how started out with one SQL server and run in to problems when these servers have to be upgrade or the databases needs to be migrated to different SQL servers of clusters because of expansion or recourses. Let’s say you have an application that has a specified database connection that I either can’t or don’t want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/or database?

By using an alias! We can keep the application configuration the same, but tell the operating system to look somewhere else for the database. Another plus is that once an alias is created, it is set at the operating system level. That means if we have two applications which connect to MySQLServer on that computer, once the alias is created, both will be re-pointed based on the alias. You can create an alias using one of two utilities:

  1. SQL Server Configuration Manager
  2. SQL Server Client Network Utility

SQL Server Configuration Manager

Let’s start by looking at how to do it using SQL Server Configuration Manager. In the left pane of SQL Server Configuration Manager, if you expand the SQL Native Client Configuration folder, there is a subfolder called Aliases (see Figure 1). If we click on this subfolder, we’ll see any aliases that have been defined for the system shown in the right pane. In most cases, there won’t be any defined. To create a new alias, right-click on Aliases and choose New Alias… from the pop-up menu.

Figure 1:

1620_CreatingAliases_01

A dialog window comes up (Figure 2) which allows us to specify our new alias. In the Alias Name property, you specify the name the application uses. For instance, if it connects to a SQL Server called MyOldServer, that’s what you enter in here. You can also enter an alias that appears to point to a named instance, such as MyOldServer\MyInstance. You can then specify the server where the database really is, to include configuring the protocol, the server name, and the port. Clicking the OK button will create the alias. In the case of Figure 2, I’m creating an alias called MyOldServer which points to my local server’s default instance.

Figure 2:

1620_CreatingAliases_02

You should now see the new alias listed in the right pane (Figure 3).

Figure 3:

1620_CreatingAliases_03

 

The next step is to test the connection. You can do so quickly by either creating and testing a Data Source Name (DSN), or if you have the SQL Server client tools installed, by using SQL Server Management Studio (SSMS). I’ll do so using SSMS. Figure 4 shows the dialog window to open a new connection to a database engine. For Server name:, enter in the alias you specified. In the example we called the alias OldServerName, so that’s what we’ll use here.

Figure 4:

1620_CreatingAliases_04

If the alias is pointed correctly to the new location, a successful connection should be established. In SSMS, I was opening a new connection for Object Explorer. Figure 5 shows that the connection was successfully made using the alias. Note that as far as SSMS is concerned, the server name is the alias.

Figure 5:

1620_CreatingAliases_05

SQL Server Client Network Utility

If you haven’t installed the SQL Server 2005/2008 client tools, you can still create an alias using the SQL Server Client Network Utility. This has come installed automatically on every operating system from Windows 2000 on. To bring up the utility, click on Start, then Run, and run cliconfg.exe. To view or create aliases, click on the Alias tab (Figure 6). As you can see from Figure 6, aliases created by SQL Server Configuration Manager can be seen by the SQL Server Client Network Utility. The reverse is also true.

Figure 6:

1620_CreatingAliases_06

 

To create a new alias, click on the Add… button. This will bring up a new dialog window where you can specify your alias (Figure 7). Just as with SQL Server Configuration Manager, the Server alias is what the application will attempt to connect to. You can specify where that alias points to by specifying the network library, the real server name, and any additional configuration parameters. In Figure 7 I’m creating a new alias called MyOldServer2 that also points to the default instance of my local server.

Figure 7:

1620_CreatingAliases_07

 

Click the OK button to create the new Alias. In order for the alias to actually be created, however, you must click the OK button for the SQL Server Client Network Utility, which will create the alias as it closes.

Conslusion

Now that you know how to create an alias to a SQL Server instance look at this option instead of having hard SQL connections to server and run into problems when you want to reinstall or rename your servers. Also some application do not give you the ability to connect to a named instance, so this is another way of connecting without having to use the a default instance.

Microsoft® SQL Server® 2008 R2 Service Pack 1 is out

What’s New in SQL Server 2008 R2 SP 1 ?

Dynamic Management Views for increased supportability:

sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.

ForceSeek for improved querying performance :

Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.

Data-tier Application Component Framework (DAC Fx) for improved database upgrades:

The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

Disk space control for PowerPivot:

This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

Fixed various issues:

This update fixes many knowledge base issues that are listed in this Master KB article.
Link: http://support.microsoft.com/kb/2463333

For a detailed list of new features and improvements that are included in SQL Server 2008 R2 SP1, review the What’s New Section in Release Notes.
Link: http://go.microsoft.com/?linkid=9764593

Now Sp1 is officially released, you can download the service pack from the Download Center:
Download: http://www.microsoft.com/download/en/details.aspx?id=26727

If you’re looking for SP1 for SQL Server Express:
Download: http://www.microsoft.com/download/en/details.aspx?id=26729

And the updated feature packs can be found here:
Download: http://www.microsoft.com/download/en/details.aspx?id=26728

The build number is 10.50.2500. SELECT @@VERSION will now yield something like this:

Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Microsoft Releases SQL Server 2008

Microsoft Corp. today announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company’s acclaimed data management and business intelligence platform. This version of SQL Server provides powerful new capabilities such as support for policy-based management, auditing, large-scale data warehousing, geospatial data, and advanced reporting and analysis services. SQL Server 2008 provides the trusted, productive and intelligent platform necessary for business-critical applications.

“Microsoft developed this release of SQL Server with the customer in mind,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft. “SQL Server 2008 is the only major database that includes comprehensive, tightly integrated functionality for data management as well as advanced business intelligence out of the box. By offering a complete solution, we save customers time and money and allow them to focus on deriving the most value from their data assets.”

SQL Server 2008 Released With more than 450,000 customer and partner downloads of SQL Server 2008’s community technology previews (CTPs), more than 75 large-scale applications already in production and more than 1,350 applications being developed by nearly 1,000 independent software vendors (ISVs) on SQL Server 2008, it’s clear that customers and partners are excited about the capabilities now available in SQL Server 2008. Tim Whitehorn, founder and chief executive officer of event management software provider ServiceU Corp., said, “We selected SQL Server 2008 because we can entrust it with our critical business applications. SQL Server 2008 is highly secure and reliable, and offers the best value on the market today. In addition, it accelerates the time frame for deploying new features and enhancements, and that to us makes SQL Server a winner.”

Continue here: http://www.microsoft.com/presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

Windows Server 2008, Microsoft SQL Server 2008 and Microsoft Visual Studio 2008 Launched!

Largest enterprise launch in company history includes Windows Server 2008, Microsoft SQL Server 2008 and Microsoft Visual Studio 2008; focuses on security, Web, virtualization and better business intelligence.

Kicking off more than 225 events around the world and joined by more than 4,000 customers and partners, Microsoft Corp.’s Chief Executive Officer Steve Ballmer today showcased the next generation of infrastructure and application platform products, including Windows Server 2008, Microsoft Visual Studio 2008 and Microsoft SQL Server 2008.

The launch represents a major milestone to help customers on the road to Dynamic IT, Microsoft’s initiative to help customers optimize their people, processes and technology, and in turn position IT as a strategic asset for their business. These new enterprise products help customers more efficiently and securely manage their entire infrastructure and move to a virtualized environment while also delivering business intelligence and next-generation Web experiences to boost business results. The theme of the events, “Heroes Happen Here,” highlights the outstanding work that IT professionals, developers and partners do every day to create solutions and cutting-edge applications that keep global commerce and industry running.

Microsoft CEO Steve Ballmer delivers the keynote at the launch of Windows Server 2008, SQL Server 2008 and Visual Studio 2008. Four thousand customers and partners attended the live event in Los Angeles, Calif. Feb. 27, 2008.

“IT professionals and developers tell us they spend too much time and money managing existing systems and not enough investing in new capabilities that create strategic advantage,” Ballmer said. “That feedback is at the core of the innovations in this new wave of products. Already, the overwhelming response from thousands of IT professionals and developers around the world is that this is the most secure enterprise platform we have ever delivered, and that it will simplify management and enable them to focus more on driving their businesses forward.”
Continue reading