Monthly Archives: April 2010

Attaching of databases from MS SQL 2000 & MS2005 to MS SQL 2008 R2

Attaching of databases from MS SQL 2000 & MS2005 to MS SQL 2008 R2

As architecture of 32-bit & 64-bit versions of SQL server is different we cannot directly attach the databases by copying master & msdb databases, so in order to avoid the manual work of attaching the databases from previous versions of MS SQL to MS SQL 2k8 R2.
Following steps need to be followed :-
1. Need to copy the databases to the respective SQL 2k8 server.

2. A vb script needs to be run at command prompt where the following parameters have to be modified :-
ServerName=”server\mssqlserver”
MappedMDFLocation=”h:\dbdata”
ServerMDFLocation=”h:\dbdata”

3. In the MappedMDFLocation as well as in ServerMDFLocation- location of databases has to be specified.

4. mSQLServer.Connect “(local)”,”sa”,”sa” .Here in place of ‘local’ the ServerName entry has to be provided followed by credentials for sa.

5. SQL DMO (SQL Distributed Management Objects) is required for the script to run successfully & it is present by default in SQl 2K5 & not in 2K8. Here we have to install it manually.

6. To use SQL-DMO on SQL Server 2008, you need to download and install Microsoft SQL Server 2005 Backward Compatibility Components to solve the problem. You can go to Microsoft Download Center and search for “Microsoft SQL Server 2008 Feature Pack” and sort by Release Date to find the latest version.

7. There under Microsoft SQL Server 2005 Backward Compatibility Components download the following file :-

X64 Package (SQLServer2005_BC_x64.msi)

8. Install the following Package and run the vbscript at command prompt.

9. Execute the script with changes made as above & databases will be attached.

download script

Thanks

Prashant Deshpande

Advertisements

1 Comment

Filed under Uncategorized

How to get better I/O performance with RAID for SQL SERVER

Dear All

How to get better I/O performance with RAID for SQL SERVER

—————————————————————————–
As SQL server ( any database server ) uses High Disk IOs we should align the Disks.
Would you like to know how to increase your servers I/O performance by up to 40%!? Well if so, then Disk alignment is one of Windows best kept secrets (or worse depending on how you see it.) If you’re running Windows Server 2000, 2003 or Windows XP with RAID, you are probably not getting the throughput you could if you had correctly aligned disks.

If you’re not familiar with the term disk alignment or sector alignment, in layman’s terms it goes like this. Windows like to write data to disk in 64k chucks. However Windows Server 2000, 2003 and Windows XP all incorrectly begin writing data at the 63rd sector. This means the first 1k of the chuck is written into one sector, and the remaining 63k in the next, and so on. The consequence of this behavior means that for every read and write, two sectors must be accessed from disk instead of one. This basically doubles your disk I/O.

Microsoft Windows Server 2000, Windows Server 2003 and Windows XP are all plagued with this problem. Any new volumes created on these operating systems (if not set correctly with diskpart – more on that in a minute) will be aligned incorrectly. Microsoft has addressed this issue in Windows Server 2008 and Window Vista, but upgrading an existing installation will not correct a disk that was originally formatted on one of the affected platforms.

To verify that an existing partition is aligned, perform the calculation that is described in the “More Information” section.

To align a disk partition on a RAID that has a 2,048-sector offset, follow these steps:
At a command prompt, type diskpart, and then press ENTER.
Type the following commands at the DISKPART prompt, and then press ENTER:
list disk

Note You receive output that resembles the following:
Disk ### Status Size Free Dyn Gpt
——– ———- ——- ——- — —
Disk 0 Online 37 GB 8033 KB
Disk 1 Online 17 GB 8033 KB
Disk 2 Online 17 GB 0 B
Disk 3 Online 17 GB 148 MB *
Disk 4 Online 17 GB 8 MB *
Disk 5 Online 17 GB 8 MB *
Disk 6 Online 17 GB 8 MB *
Disk 7 Online 17 GB 8 MB *
Disk 8 Online 17 GB 435 KB *
Disk 9 Online 17 GB 8 MB *
Disk 10 Online 17 GB 8033 KB

The list disk command provides summary information about each disk that is installed on the computer. The disk that has the asterisk (*) mark has the current focus. Only fixed disks and removable disks are listed. Fixed disks include integrated device electronics [IDE] and SCSI disks. Removable disks include 1394 and USB disks.

select disk

Use the select disk command to set the focus to the disk that has the specified Microsoft Windows NT disk number. If you do not specify a disk number, the command displays the current disk that is in focus.create partition primary align=1024

Notes
When you type this command, you may receive a message that resembles the following:

DiskPart succeeded in creating the specified partition.

The align= number parameter is typically used together with hardware RAID Logical Unit Numbers (LUNs) to improve performance when the logical units are not cylinder aligned. This parameter aligns a primary partition that is not cylinder aligned at the beginning of a disk and then rounds the offset to the closest alignment boundary.

number is the number of kilobytes (KB) from the beginning of the disk to the closest alignment boundary. The command fails if the primary partition is not at the beginning of the disk. If you use the command together with the offset =number option, the offset is within the first usable cylinder on the disk.

exit

Type exit, and then press ENTER.

Click Start, click Run, type diskmgmt.msc, and then click OK.

In the Disk Management Microsoft Management Console (MMC) snap-in, locate the newly created partition, and then assign it a drive letter.
Use the NTFS file system to format the new partition, and then assign a cluster size

Thanks

Prashant Deshpande

Leave a comment

Filed under Operating System