Setup supports a direct upgrade from SQL Server 2000 and 2005. Those of you still on SQL 7.0 will need to upgrade to SQL Server 2000 or 2005 first before upgrading to SQL Server 2008. Since the product development cycle was shorter than that of SQL Server 2005, the changes and issues involved with upgrading from SQL Server 2005 to SQL Server 2008 are minimal compared with the move from SQL Server 2000 to SQL Server 2008.
You can upgrade to SQL Server in two ways:
The in-place upgrade is the more automated of the two options, but might not be the best option for some organizations. An in-place upgrade takes down the server for a duration of time, and without previous testing, who knows what will happen after the upgrade is complete? The positive aspect of the in-place upgrade is that when the upgrade is completed, the instance name of the new SQL Server is the same as it was pre-upgrade, so existing applications do not need to change any connection string information. With a side-by-side upgrade, the new installation uses a new instance name, so applications might need to have their connection string information changed to point to the new instance.
To help you with the upgrade, Microsoft released a free tool called Upgrade Advisor, which essentially scans your existing SQL Server instance and gives you a report on any potential issues you will have after the upgrade. But before we talk about using Upgrade Advisor, we need to discuss planning the upgrade. If you were going to simply upgrade your production servers without a plan, then you may want to consider freshening up your resumÃ©. A plan is critical to the success of an upgrade.
Planning the Upgrade
A smooth upgrade requires a good plan. Before you upgrade, you need to prepare your environment. The pre-upgrade tasks include the following:
Ensure the server hardware that will host SQL Server 2008 will be sufficient.The server hardware should meet the minimum hardware requirements, such as the amount of available RAM and disk space. The operating system on the server hardware should also contain the latest service pack or the minimum service pack required by SQL Server 2008.
The last item is particularly important, because different components may have different upgrade paths. For example, if you’re using Analysis Services, with an in-place upgrade, you can use the setup wizard just as you would with upgrading the database engine. To perform a migration, you can use the Migration Wizard tool available from the server node context menu off of an analysis server connection in SQL Server Management Studio.If you have Analysis Services projects written using Business Intelligence Development Studio (BIDS), you can simply import into BIDS in SQL Server 2008.
With another component within the SQL Server product, Reporting Services, the migration process is slightly different. Again, if you choose to perform an in-place upgrade, the setup wizard will perform most of the heavy lifting. If you choose to migrate, there is a list of things to do. SQL Server Books Online describes the migration of Reporting Services very well in an article entitled “How to: Migrate a Reporting Services Installation.”
As you can see, it is imperative to consider all the components that you are using within the SQL Server product.
Using Upgrade Advisor
The Upgrade Advisor (UA) tool is available on the SQL Server 2008 installation disks, in the ServersRedistUpgrade Advisor folder.
Here, we’ll walk through an example of running UA against a SQL Server 2005 instance.
Upgrade Advisor welcome screen
Choosing SQL Server components
Specifying connection parameters
Choosing SQL Server parameters
Completed UA analysis
Report generated by UA
UA creates a directory under the My Documents folder to store its reporting information. UA allows you to analyze multiple servers. For each server analyzed, a subfolder is created. To view reports, click the Launch Upgrade Advisor Report Viewer link from the main UA screen.
You’ll notice in this example that the number of issues upgrading SQL Server 2005 to SQL Server 2008 was fairly minimal. If you are upgrading from SQL Server 2000, expect the list to be a bit longer.
Performing the Upgrade
Now that you have decided on what type of upgrade you will perform and have gone through all the items in the pre-upgrade tasks list, it is time to perform the actual upgrade.
Before you go ahead and run the setup wizard, or start migrating data from your original SQL Server instance, it is important to make sure your environment is ready for the upgrade. The first task is to check the database consistency for those databases being upgraded. This is as simple as issuing a DBCC CHECKDB command against each of the databases for the instance you are upgrading.
After you’ve checked your databases for consistency, take a backup of all the databases in your SQL Server instance. When the backups are complete, verify the backups as well to ensure their own consistency. At this point, you are ready to start the upgrade or migration process.
In this section, we will walk through a basic installation of SQL Server 2008. Although there is a nice HTML user interface that can be brought up when you insert the SQL Server media, we chose to run the setup program directly from the Servers folder on the SQL Server installation disk.
The first thing the setup program does is to detect whether you have .NET Framework 3.5 installed. If you do not, it installs that version of the .NET Framework. Next, you are presented with the infamous license agreement to which you must agree in order to proceed. Then setup will copy some support files. Once this is complete, the real installation begins.
You will see the SQL Server Installation Center screen. From here, you can create a new installation, upgrade an existing SQL Server instance, and create or change an existing clustering configuration.
SQL Server Installation Center screen
Clustering support in setup is of great value. If you are running SQL Server 2008 on Windows Server 2008, you are already gaining a huge number of features, including support for up to 16 nodes and an ease on restrictions like the requirement that nodes be on the same subnet. This means that Geoclusters are now a viable option for SQL Server deployments. A Geocluster is a clustering configuration where the active and passive servers are physically separated by large geographical distances.
These types of clustering configurations required special considerations during deployment. Windows Server 2008 implemented features that makes Geoclustering easier. The increased support for clusters doesn’t end with what Windows Server 2008 provides to SQL Server 2008. The SQL Server Installation Center offers access to a few wizards to make it really easy for administrators to set up and maintain a clustered configuration.
In this example, we are installing a new instance of SQL Server 2008 on a server that already has SQL Server 2005 installed. This is what is known as a side-by-side configuration. When we choose New Installation from the SQL Server Installation Center screen, the New Installation Wizard starts. This wizard will immedately perform a system configuration check.
System Configuration Check step for a new installation
If you fail the system configuration check, you won’t be able to continue installation unless you address the issue. SQL Server is fairly strict on the requirements for service packs. For example, if you are using Windows Server 2003, you must have at least Service Pack 2 installed.
Once you have passed the checks, you are ready to select which components to install. The setup program allows for a variety of configurations. You can use it to install just the client tools, SQL Server Books Online, or every feature. Once you have made your selections, the wizard will dynamically add steps as needed.
In our example, we are installing SQL Server 2008 side-by-side with SQL Server 2005. You can see the existing SQL Server instance that is already installed on this server, as shown in Figure. From this page, you can set the instance name, ID, and root directory for the new SQL Server 2008 instance.
Feature Selection step for a new installation
Instance Configuration step for a new installation
The Server Configuration page, shown in Figure, is where you enter credentials for the various services that will be installed. This user interface is much more intuitive than in previous versions of SQL Server. You can clearly see which services are assigned to which accounts. You can even apply the same account and password to all services, using the fields at the bottom of the page. Although this is not a security best practice, if your organization allows the same credentials to be used, clicking the Apply to All button saves a lot of typing.
Server Configuration step for a new installation
The Server Configuration page also contains a Collation tab. With the SQL Server 2008 setup, you’ll find that the most commonly used and configured settings are presented to you directly, and the more advanced or less used features are usually available on a different tab.
Since in this example, we requested that setup install the database engine, we are presented with a page that will allow us to provide some key configuration information, as shown in Figure. While the security mode is nothing new, having to specify a SQL Server administrator is. By default, there is no one defined to be the administrator. You must provide a valid username for this purpose.
Database Engine Configuration step for a new installation
The Data Directories tab of the Database Engine Configuration page, shown in Figure, allows users to change the directories of all the database files. To change the root location for all directories in one shot, just change the Data Root Directory setting. This feature would never make the key bullet points of the product, yet it’s subtle but useful improvements like this that make the setup experience much more pleasant than with earlier versions.
Data Directories tab of the Database Engine Configuration page
While there aren’t a lot of options to configure with respect to Analysis Services, you can easily add the current user as an administrator, as shown in Figure. The Data Directories tab allows you to specify which directories you want to use for the data, log, temp, and backup files.
Analysis Services Configuration step for a new installation
Since we also wanted to install Reporting Services, the wizard added a Reporting Services Configuration page, as shown in the Figure. This page allows you to install Reporting Services and perform the configuration later, install in SharePoint mode, or install and configure Reporting Services as part of the initial installation. As with SQL Server 2005, Reporting Services contains a configuration tool that is used to configure the Reporting Services instance on the server.
Reporting Services Configuration step for a new installation
The next page is the Error and Usage Reporting page. It contains two check boxes, which basically ask you if it’s OK if SQL Server periodically sends usage information to Microsoft. This transmission doesn’t contain any personal data. The information that is obtained is intended to answer questions about how you use the product. The results of these queries across many organizations throughout the world ultimately allow Microsoft to design a better product.
At this point, you are ready to begin installation of the product. As with other software installation procedures, you are kept up-to-date on the installation progress. If for any reason something fails to install, you will be able to read the setup log files, which are located on the installation drive under the path Program FilesMicrosoft SQL Server100Setup BootstrapLog. The setup program will create multiple text files that you can sift through. That should give you a hint as to which component had the problem.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.