Very good link which can support you while migration (especially in Side by Side or New installations)
http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133
http://technet.microsoft.com/en-us/library/ms137870.aspx
Thursday, October 28, 2010
Moving the users between SQL Servers 2000 to 2005/2008/R2,2005 to 2008/2008R2
http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133
http://technet.microsoft.com/en-us/library/ms137870.aspx
Monday, October 25, 2010
Side by Side Installation of SQL Server 2008 R2 with SQL Server 2000 SP4 in Windows 2003 SP2 Cluster Environment...
There is one SQL Server 2000 Installation running in active / passive cluster and requirement demands that Install SQL Server 2008 R2 in Cluster and move the databases from SQL Server 2000 to SQL Server 2008R2 and port all application to the new instance without doing any modification in the applications..
It is some thing like Migrationg Databases with less down in Cluster environment.
It is some thing like Migrationg Databases with less down in Cluster environment.
Keep in mind
After all the shared components are upgraded and prerequisites are installed, starts the failover cluster Installation process and you have to install on each failover cluster node, starting with the passive nodes (Passive node means it is passive to other instance running on Cluster).
Download the necessary tools Install before installation proccess
Download the all necessary tools and patches which are required for Upgrade process.
· Download SQL Server Upgrade Advisor 2008R2 tool
· Download .NET Framework 3.5 SP1
· Windows Installer 4.5
Download the necessary tools Install after installation proccess
· Download latest CU or SP available for SQL Server 2008R2
Part of Install SQL Server 2008R2, It is required to update all the failover cluster nodes with .NET Framework 3.5 SP1 and Windows Installer 4.5,which is required upgrade on the cluster nodes and make sure that relative shared components are in Place.
Note: If you installing 32 bit SQL Server download 32 bit components/Installers or If you installing 64 bit SQL Server download 32 bit components/Installers.
· Upgrade shared components starting with the passive nodes.
· Installs .NET Framework 3.5 SP1
· Install Windows Installer 4.5
· For Database Engine installations on Windows Server 2003 SP2, install the Windows Server hot fix needed for File Stream and Sharing Drives.(Optional)
· Restart the Node.
· Do the Fail over to an upgraded node.
· Upgrade the shared components on the last remaining node and Restart the Node.
WHY SQL Server Upgrade Advisor
SQL Server upgrade advisor 2008R2 helps us to prepare for upgrades to SQL Server 2008R2. Upgrade advisor analyzes installed components from existing SQL Server version and then generates a report that identifies issues to fix either before or after upgrade.
After clicking lunch report button it will show a window and it will show “Importance”,” when to fix “and “Descriptions”.
· Clicking on (+) sign will drill down as follows, Take any actions necessary to correct issues prior to our actual upgrade.
· Each report has got its own importance level. Red X symbol means that this error needs to be fixed before upgrading. A yellow triangle indicates that additional action needs to be taken once upgrade is done.
· The "When to Fix" column indicates when we should address the error. Any error having "Before" as the value in "When to fix" need to be addressed immediately as this error can be blocker for the upgrade.
· We can expand each report as shown below screen shot to check the objects causing the error and how to resolve it. This can be done by clicking on link, "Show Affected objects"
before new installation
Before starting new installation of SQL Server you should have below information in your hand.
- New service account for SQL Server services and SQL Server agent
- New cluster group is required to install SQL Server
- New Network name
- New IP Address
- Instance name
Creating Service account for sql server engine and sql server agent
SQL Server engine and SQL Server agent are required a service account to run as a services, so that create a service account in Domain controller and you can use this account later part of installation.
Creating Cluster Resource Group
Screen 1: Go to the cluster administer right click on group and select group
Screen 2: In New Group window enter the Name of the group and description and click on “Next”.
Screen 3: In Proffered Owners screen select nodes for available nodes box and then click ” add” button once nodes added to preferred owners box click on “Finish” button.
Moving the New clouster group to other node
Move the group from one nod to other node and vice-versa, by this step make you sure that newly created group working as properly.
Side by Side Installation Process has selected for upgrade SQL Server 2000 to SQL Server 2008 R2.
Open SQL Server 2008 R2 installation executable file from your CD\DVD\Shared location
Screen 1: Double click on setup.exe and you will find the below screen, now select “New SQL Server Failover Cluster installation” option highlighted as in below image.
Screen 2: Once you have selected” New SQL Server failover cluster Installation” option Installation will check for setup support rules to identify the problem. If it is failed due to any reason, you have to correct it before going further installation process.
Screen 3: Once after clicking on “show Details” button below dialog box will appears Check for status of all rules and Click on “OK” button
Screen 5: Once after click on Install button in Screen 4, below dialog boxes will appears and it will continue components installation.
Screen 6: Once setups support components installation is completed it will show below dialog box Click on “Next” button and it will drive to Installation type screen.
Screen 7: Enter Product Key or it will take automatically. See the below image for it.
Screen 8: In the License Terms dialog box, accept the license terms check box and click next.
Screen 9: In the Feature Selection dialog box, select only the components that you want installed. For the Shared feature directory, you can keep the default path if you have sufficient disk space on your C:\ drive or anywhere that is a local disk as this will be used by the SQL Server installation process later on. The directory for the clustered database engine will be different. Click Next.
Screen 10: In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that will be available on the network for the clients. This will vary depending on your selection of named instance.
Example: default instance is selected. A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server and is helpful when you want to run multiple instances in a cluster. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should select the Instance ID box and specify a value.
The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on your server.
Screen 11: SQL Server will review the disk space requirement for the features you have selected
Screen 12: In the Cluster Resource Group dialog box, check the resources available on your Windows Server 2003 SP2 cluster, best practice is create cluster group manually and select the group from drop down Click Next.(Remember you created cluster group in “Creating cluster resource group”)
Screen 13: In the Cluster Disk Selection dialog box, select the available disk that is on the cluster for SQL Server 2008 R3 to use. In this example, two clustered disk groups – H and L are available to select, select available drives and Click Next.
Screen 14: In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that SQL Server 2008 R2 cluster will use. Deselect the checkbox under the DHCP column as you will be using static IP addresses. If you have not disabled your IPv6 adapters and protocols, it would be better to uncheck the row for IPv6
Screen 15: In the Cluster Security Policy dialog box give the domain groups that can be used for SQL service accounts. Make sure SQL service account is a member of this group.
Screen 16: In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. In the Collation tab, select the appropriate collation to be used by SQL Server. Note that the startup type is set to manual for all cluster-aware services and cannot be changed during the installation process. Click Next.
Screen 18: On the Data Directories tab, enter the path where your system and user database files will be created. This will default to the first shared disk in the cluster so in case you want to change it to the other shared disks to be used by SQL Server 2008, modify accordingly. If you intend to use the new FILESTREAM feature, click the FILESTREAM tab and set the appropriate configurations. Click Next.
Screen 19: In the Error and Usage Reporting dialog box, click next.
Screen 20: In the Cluster Installation Rules dialog box, verify that all checks are successful and click next.
Screen 21: In the Ready to Install dialog box, verify that all configurations are correct. Click Next.
Screen 22: In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2008 Failover Cluster
Step 23 : Apply the required Service Pack/Patch and reboot the server
Once installation is done on this node, we have to proceed with the installation of SQL Server on the other cluster nodes. SQL Server 2008R2 we must manually install and patch each cluster node individually. This process allows for less downtime as we are patching the cluster or upgrading your cluster from SQL Server 2000 to SQL Server 2008R2. (SQL Server 2005 was the last edition of SQL Server to automatically install SQL Server on all nodes at once)
Installation on the other nodes is similar to installing on the first node except that we have selected the Add Node to a SQL Server failover cluster option from the initial menu. The same software requirements apply to the other nodes in the cluster, so the installer will handle the verification and installation of these components as well.
Because most of the settings are picked up from the other node(s) in the cluster that already have SQL Server installed on them, the process of adding a node to the cluster is bit shorter than installing on the first node
Adding a node on a SQL Server 2008 R2 Failover Cluster
Screen 1: Run setup.exe from the installation media to launch SQL Server Installation Center.
Screen 2: Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2008R2 Setup wizard.
Screen 3: In the Setup Support Rules dialog box, validate that the checks return successful results and click OK.
Screen 4: In the Product Key dialog box, enter the product key that came with your installation media and click Next.
Screen 5: In the License Terms dialog box, check and accept the license terms check box and click next.
Screen 6: In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. Again, make sure to fix any errors returned by this check before proceeding with the installation.
Screen 7: In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2008 cluster is correct.
Step8: In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node.
Step9: In the Error and Usage Reporting dialog box, click next
Step10: In the Add Node Rules dialog box, verify that all checks are successful and click next
Step11: In the Ready to Add Node dialog box, verify that all configurations are correct and click Install
Step12: In the Complete dialog box, click Close. This concludes adding a node to a SQL Server 2008 Failover Cluster
You can validate your cluster installation by expanding the Services and Applications node and check the cluster name of your SQL Server instance. You can now see an option to move the service to another node, in this case, the node you've just added in your failover cluster.
Testing new instance
After successfully completion of SQL Server 2008 R2 installation do the manual fail-over couple of times and create sample databases and make sure that new instance working as expected.
moving databases, logins,renaming and annoucing down time
Now we are in end part of the migration of the databases and this part will describes moving databases, logins, Announcing downtime and renaming network name as well as renaming the Instance name using alias names.
RESTORING DATABASES from full backup
Step1: run the sp_hexdecimal and sp_resolve_login to generate logins and copy the login script in text file and save in safe location. Ref: http://support.microsoft.com/kb/918992
Step2: Take a full backup of all user databases from SQL Server 2000 and restore in SQL Server 2008 R2 with NORECOVERY.
RESTORING DATABASES from Trasactional backup
Step2: Take a Transactional backup of all user databases from SQL Server 2000 and restore in SQL Server 2008 R2 with RECOVERY.
Step3: Perform the step 2 until restoration of the databases reaches minimum time.
Anicing down time and Trasaforing the logins
Step 4: Now announce the down time and perform final transactional log backup and restoration with recovery
Step 5: Run the login script in new instance (Logins script generated with STEP2)
Re naming naetwork name and aliasing instance name
Step 6: Take to SQL Server instance and Network name to Off-line.
Step 7: Re name the Name work name which is same as old network name.
Step 8: Create alias name for new instance (Alias name must be equal to old instance name)
Step 9: Re start the network name and Instance and
Step 10: Announce the available of new installed SQL Server in Cluster.
· To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases. Testing
Once upgrade was done there couple of activity required to check
· Check feature of SQL Server 2008R2 are working on Database.
· Implement Database maintenance activity Backups, restorations ,re-indexing and statistics
· Password is case sensitive in SQL Server 2008,Please check existing users and password
· Upgrade full text indexes while upgrading
· Use the SQL Server 2008 resource on migrated Database, like Database tuning wizard etc.
· Other Database object level (Procedure, Table etc ) compatibility test will carry with Application functional testing
· Required Discusses with Development and Plan the Testing
· Required Discusses with Development and Test team for Testing
Subscribe to:
Posts (Atom)