Limitations in 64-Bit Windows server 2003
Posted by: Manpreet Singh on: July 21, 2009
Windows Server 2003 is considered to be one of the most scalable operating system when running on 64 bit platform. But still there are some consideration which must be consider before migrating SQL Server to 64 bit enviournment
There are some limitations of windows server 2003 which must be addressed before migrating SQL server to 64 bit enviournment
- In 64 bit enviournment, 64-bit programs cannot load and call 32-bit MDAC.
- 64-bit Microsoft Internet Explorer cannot load 32-bit ActiveX controls.
- The 64-bit shell cannot load 32-bit Inproc shell extensions.
- 32-bit installer programs cannot load and register 64-bit DLLs.
- No 16-bit code can run, except for recognized Install Shield and Acme installers (these are hard-coded in Wow64 to allow them to work).
- 16-bit Setup bootstraps are not supported.
- 16-bit MS-DOS and Microsoft Windows 3.x utilities will not start. If you attempt to start such a program, you receive a “Program.exe is not a valid Win32 application” error message.
- There is no support for OS/2 or Posix programs.
- There is no support for Kernel-mode or 32-bit code such as:
- 32-bit virus-detection or 32-bit file system filters.
- 32-bit video adapter or 32-bit network adapter drivers.
- 32-bit Kernel-mode printer drivers.
32 Bit SQL Server 2005 Vs. 64 Bit SQL Server 2005
Posted by: Manpreet Singh on: July 21, 2009
Today, 64-bit processors have become the standard for systems ranging from the most scalable servers to desktop PCs. The way to take full advantage of these systems is with 64-bit editions of Microsoft Windows products and Microsoft SQL server.
The 64-bit systems offer direct access to more virtual and physical memory than 32-bit systems and process more data per clock cycle, enabling more scalable, higher performing computing solutions. There are two 64-bit Windows platforms: x64-based and Itanium-based. X64 solutions are the direct descendants of x86 32-bit products, and are the natural choice for most server application deployments—small or large. Itanium-based systems offer alternative system designs and a processor architecture best suited to extremely large database and custom application solutions. Microsoft SQL Server 2005 Standard and Enterprise Editions both offer dramatic improvements in memory availability and parallel processing performance when compared to SQL Server software running in a 32-bit environment. We need to understand that Applications and data are growing in complexity and size. Many applications running on 32-bit platforms today are approaching or have exceeded the limits of the platform, specifically regarding the number of processors and addressable memory. Advanced DBMS capabilities, particularly in the areas of scalable architecture and high availability, have become a business necessity and are increasingly common as more organizations gather and analyze data from numerous data sources, and serve that data to growing numbers of business users and organizations are consolidating servers to simplify critical data center operations.
The primary differences between the 64-bit and 32-bit versions of SQL Server 2005 are derived from the benefits of the underlying 64-bit architecture. Some of these are:
- The 64-bit architecture offers a larger directly-addressable memory space. SQL Server 2005 (64-bit) is not bound by the memory limits of 32-bit systems. Therefore, more memory is available for performing complex queries and supporting essential database operations. A 32-bit system can directly address only a 4-GB address space. Additional memory is indirectly accessible by using Address Windowing Extensions (AWE) on 32-bit platforms Windows Server 2003 SP1 running on the Intel Itanium 64-bit architecture supports up to 1,024 gigabytes of both physical and addressable memory
- The 64-bit processor provides enhanced parallelism, thereby providing more linear scalability and support for up to 64 processors, and yielding stronger returns per processor as compared to 32-bit systems. Improvements in parallel processing and bus architectures enable 64-bit platforms to support larger numbers of processors (up to 64) while providing close to linear scalability with each additional processor. With a larger number of processors, SQL Server can support more processes, applications, and users in a single system. Server platforms that offer more than 32 CPUs are available exclusively on 64-bit architecture. The highest TPC-C benchmark figures for SQL Server have been achieved on 64-bit systems because these systems leverage both large amounts of memory and the superior scaling of 64-bit processors on the 64-bit architecture.
- The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods. The bus architecture on current 64-bit chipsets is faster and wider than earlier generations. More data is passed to the cache and processor; this is somewhat analogous to the improvement that broadband connections offer over dial-up connections.
- A larger on-die cache allows for faster completion of user requests and more efficient use of processor time.
- On 32-bit systems, Analysis Services is limited to 3 GB of maximum physical memory. The 64-bit platform can help eliminate this limitation. It can also help to create or accelerate large online analytical processing (OLAP) systems with rapid response time requirements.
- The 64-bit platform facilitates the consolidation of multiple databases and applications to fewer servers. By hosting multiple databases on a single 64-bit system, you simplify management, improve storage utilization, and generally improve operational efficiency. Such consolidation efforts have limited capabilities on the 32-bit platform given the restrictions on memory availability and general absence of advanced hardware partitioning capabilities on 32-bit systems.
- The 64-bit platform allows you to “scale up” current applications that are experiencing significant growth; migrating existing database servers that are meeting platform limitations doesn’t affect the other tiers of multi-tiered applications.
- 64-bit servers can replace legacy UNIX or mainframe systems/applications. The 64-bit platform offers a powerful and reliable alternative to UNIX/mainframe systems for high-end database servers.
- Index creation operations benefit from the existence of larger addressable memory. This is because the entire sort can be done in memory. It is not necessary to write intermediate result sets to disk prior to performing the final write to disk of the actual index.
- Full-text indexing is restricted to either 2-GB or 3-GB workspaces for 32-bit computers. (In practice, since memory is also used for other processes, this restriction is notably lower than 2 or 3 GB.) This creates a potential bottleneck for creating or rebuilding very large Full-text indexes. Early deployments of very large full-text search systems can show orders of magnitude gain in speed when rebuilding indexes for these systems.
- Complex queries that use sorting or large hash joins or hash aggregates perform significantly better when sufficient directly addressable memory is available. Hash joins are very efficient, but can be memory intensive. When there is memory pressure on the system, hash joins can be removed from the cache in favor of memory-conserving, but slower, alternative query plans.
- Systems that have large numbers of active stored procedures will benefit through improved plan caching capacity. The 64-bit architecture can substantially reduce overall CPU utilization and latency by eliminating the need to evict procedures from cache and compile frequently.
- Server-side cursors, which are often heavy consumers of memory, can more readily be kept in memory, thereby resulting in better performance.
- Operations such as aggregation and sorting need to work with the entire datasets. These operations can benefit from the increased memory support provided by the 64-bit platform.
- SSIS components that perform row-by-row operations such as computing new columns, converting data, character conversions, and look-up joins to reference tables benefit from the enhanced performance provided by the 64-bit platform.
- SSIS tasks that perform aggregation, sorting, and fuzzy lookups, need to work with the entire dataset and benefit from the increased memory.
Migration
The migration of database from SQL Server 2000 (32-bit and 64-bit) and SQL Server 2005 (32-bit) to SQL Server 2005 (64-bit) is a straightforward task, as both share the same on-disk data and log file format. The mechanisms available include using detach and attach or backup and restore.
Detach/Attach
System-provided stored procedures are available to detach (sp_detach_db) the database from the 32-bit version, then attach (sp_attach_db) to the 64-bit instance. This can also be done in a GUI environment by using SQL Server Management Studio.
Backup/Restore
The same process for a standard backup and restore can be used to migrate data to the 64-bit platform, by simply restoring 32-bit backup files to the 64-bit platform. This can be performed by using Transact-SQL commands or via GUI tools. It is also possible to migrate data back to the 32-bit version for SQL Server 2005 databases. However, downgrading to SQL Server 7.0 from SQL Server 2005 (64-bit) is not supported. SQL Server 2005 can also perform the migration by upgrading an existing SQL Server 2000 64-bit database when performing an “in-place” install. Because SQL Server 2005 tools can be installed onto the 64-bit platform, it also possible to use the Copy Database Wizard (CDW) to bring a SQL Server 2000 database over from another server and attach it automatically. CDW can also script-out the SQL logins and passwords from the SQL Server 2000 installation and re-create them automatically on the destination 64-bit SQL Server. This also applies to any user-defined jobs that were related to the upgraded database.
Compatibility issues
SQL Server 2005 (64-bit) is fully compatible with SQL Server 2005 (32-bit), enabling easy integration in existing infrastructure while providing support for the extended 64-bit hardware. The product offers complete compatibility with the SQL Server 2000 (32-bit and 64-bit) database and transaction log files. Unlike SQL Server 2000 (64-bit), SQL Server 2005 management tools and client components are available on 64-bit either natively or in WOW. There are a number of enhancements and component changes with the 64-bit versions of SQL Server and Windows Server 2003. Components that used to be part of SQL Server set up, such as Microsoft Data Access Components (MDAC), Distributed Transaction Coordinator (DTC), and Microsoft Management Console (MMC), are now installed as core components as part of the Windows Server 2003 set up. This enables SQL Server 2005 (64-bit) to take advantage of these operating system resources.
Reporting Services considerations
SQL Server 2005 Reporting Services supports 64-bit processors, including the Intel Itanium2 processor and also the x64 architecture from AMD and Intel. On x64 systems, Reporting Services can run in both native 64-bit mode and also the 32-bit Windows on Windows (WOW) subsystem. In general, 64-bit systems running at the same processor speed will not increase the throughput of reports. Instead, the primary benefit is that users can view and export the output of larger reports. You might get better throughput on 64-bit machines at higher workloads, because contention
Resolving SQL Server 2005 SP3 Installation Problem
Posted by: Manpreet Singh on: July 14, 2009
Have you encountered a problem due to deletion of the contents of C:\Windows\Installer folder? Several times the administrators delete the contents of this folder to make the space free without knowing the consequences. All the previous installation files get stored to this folder specifically the update\patches files which comes in to use when we upgrade the patch or any specific application. So please do use the “Installer cleanup utility” provided by Microsoft for cleaning C:\Windows\Installer folder.
In this article I will discuss as how to resolve the problem if you are unable to install the SQL Server 2005 SP3 due to previous installer files deleted. We have recently faced this when we were trying to update our Database Service pack. One of our administrators deleted the contents of C:\Windows\Installer folder to mitigate the space crunch faced on the system volume(C: Drive) and resulting which we were unable to install the SQL Server 2005 SP3.
If you are facing the same issue then the solution goes as follows-
1.) Start the SQL Server 2005 SP3 installation. When it fails refer to the log file.
2.) You can refer the installation log stored at “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log” the path may differ according to your installation.
3.) When you will refer this log file you will find it is unable to find one .msp file which ideally should be in .\windows\installer folder. This file name is different for each n every server. Now the work around will be as below-
“Download the SQL Server 2005 SP2 if you have it already then it’s cool. Extract the *.exe by using /extract or /x switch to a folder. Now search for “sqlrun_sql.msp” file. Rename this file by copying it somewhere else to the same name which the log file is showing missing in c:\windows\installer folder and paste the file to C:\windows\installer folder.
4.) Now run the SP3 setup again. It should be completed this time. But if still fails, refer the log file again. Now what you will found that that it has found the previously missed .msp file but got struck at the second .msp file (In my case it was like this). Now the workaround is-
“Go Ahead and download SQL Server 2005 SP1 and extract it the same way as we did about SP2 now again search for “sqlrun_sql.msp” file and paste it to the c:\windows\installer and rename it to the the name which log file is showing missing.”
Now run the SP3 setup again and this time you should be successful.
The Sample Installation Log File
*************************************************************
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: UIText 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: Media 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: Shortcut 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: SxsMsmGenComponents 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: MsiSFCBypass 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: RemoveFile 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: RemoveRegistry 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: Environment 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: _sqlUpgradeFile 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: _sqlUpgradeRegistry 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: _sqlUpgradeFolders 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Note: 1: 2262 2: _MsiPatchTransformView 3: -2147287038
MSI (s) (44:80) [14:29:07:518]: Opening existing patch ‘C:\WINDOWS\Installer\2691b4.msp’.———-“In first failure this msp file was causing failure.”
MSI (s) (44:80) [14:29:07:518]: Opening existing patch ‘C:\WINDOWS\Installer\389d4c59.msp’.———“In second failure this msp file was causing failure.”
MSI (s) (44:80) [14:29:07:518]: File will have security applied from OpCode.
MSI (s) (44:80) [14:29:14:394]: Original patch ==> d:\604b2713b936ee0404\HotFixSQL\Files\sqlrun_sql.msp
MSI (s) (44:80) [14:29:14:394]: Patch we’re running from ==> C:\WINDOWS\Installer\8ecaac.msp
MSI (s) (44:80) [14:29:14:394]: Opening existing patch ‘C:\WINDOWS\Installer\4208e404.msp’.
All About SQL server 2005 Clustering and Windows Clustering
Posted by: Manpreet Singh on: July 9, 2009
Basic hardware requirement
- Minimum two servers with windows server 2003 datacenter, enterprise or standard edition (Two node clustering only).
- Shared storage (SQN, ISCSI) etc. with Minimum 3 partitions (for Quorum (500 MB), MSDTC(2 GB) and Data files (Depends))
- Minimum 2 Network card per server for public and private network with 500-millisecond or less round-trip response time
- Nodes must be part of windows domain.
- Minimum 5 Static IP address on public network and 2 IP address with private network
- Static IP address on each node (Two IP address)
- One Cluster virtual IP address (One Address)
- One IP address for MSDTC on clustering (One IP)
- One IP address as SQL virtual IP address (One IP)
- Minimum 2 Active Directory Account as part of local administrator group on each node
- Windows cluster service account (1 account)
- One for SQL server service (1 account)
- Minimum Two Unique names
- MSDTC virtual name (1 Name)
- SQL Server Virtual Name (1 Name)
Prerequisites
- All disks must be basic disks (not Dynamic) with NTFS partitions
- Under the 64-bit editions of Windows Server 2003 that support clustering, all shared disks must be configured as master boot record (MBR) partition-style disks
- Each SQL Server or Analysis Services will require its own dedicated drives.
- Each single LUN must have only one logical drive
- As per MFT (master File table) each disk has a limitation of 2TB, use 64KB as the Block size if you are doing a mix of read and write or 8KB if you are doing mostly writes. Never use default 4 KB Size.
- Please do sector aligning of shared disk (for SANs) which means for every I/O issued by Windows and SQL Server, there is one corresponding I/O on the hardware side of things.
- Always use hardware-based RAID.
Rights needed for cluster administrator service account
- Act as Part of the Operating System
- Adjust Memory Quotas for a Process
- Back Up Files and Directories
- Increase Scheduling Priority
- Log on a Service
- Restore Files and Directories
Firewall considerations for SQL server clustering
- 135: This is the port used by the remote procedure call (RPC) endpoint mapper.
- 139: This is the port used by the NetBIOS session service.
- 445: This is the port used by Windows file sharing (SMB).
- 3343: This is the port used by the cluster network driver and private heartbeat network.
- 5000 to 5099: Although you might not need to open up all these ports, some of these might need to be opened to allow remote RPC connectivity to Cluster Administrator. If they are not open, you might see a 1721 error in the Event Log. The actual Cluster Service needs at least 100 ports for communicating through RPCs. When other services such as SQL Server 2005 are using some of the ports, there might not be enough ports open for the cluster to function properly.
- 8011 to 8031: If the cluster nodes are separated via a firewall, these ports must be opened to allow RPC connectivity between the nodes. If these are not opened, errors that refer to a “Sponsor” will be found in the cluster log. As with the previous point, the reason this might occur is that there are not enough ports required for clustering to allow RPC communication between a node that is trying to join the cluster and its sponsoring node.
Installing cluster service
- Make sure, all the hardware is identical (such as HBAs) on each node, and make sure all firmware and BIOS levels for the components match the versions on each node.
- Create and configure the Cluster Service Account through AD
- Add Cluster Service Account to Each Node and provide local administrative rights by adding them to local administrator group.
- Provide user right assignment through local security policies to Cluster Service Account on each node (gpedit)
- Configure Public Network.
- Configure Private Cluster Network by Disabling NetBIOS Over TCP/IP.
- Set Network Priorities (Publicàprivate…) on each node.
- Configure Shared Disks by sector aligning using diskpart.( create partition primary align=o)
- Format the Disks with NTFS file system
- Verify Disk Configuration
- Running the Microsoft Cluster Configuration Validation Wizard (optional)
- Log off all other nodes except the first node.
- Add First Node to a New Server Cluster by using cluster administrator and by entering cluster IP address cluster name and cluster service account and by selecting quorum disk.
- Power up a node to be joined to the cluster. Start Cluster Administrator, which is located under Administrative Tools. You do not need to run Cluster Administrator from the node that you will be joining. It can be run on the first node
- From cluster administrator, right click cluster name and select properties and set the network priorities (private à public).
- Set the public network to all communication (private and public) and private network to Internal Cluster Communications only.
- Resize the Quorum Log from cluster properties. Default is 4 MB.
- Test the environment by changing the owner of groups , resources
Creating a Clustered Microsoft Distributed Transaction Coordinator
- Start Cluster Administrator.
- In the left pane, expand Groups. Right-click the group that contains the dedicated disk for use with MS DTC, and Rename, it to somewhat more appropriate
- Right-click the newly renamed resource group, select New, and then select Resource, enter an appropriate name for the IP resource that will be associated with MS DTC such as MS DTC IP Address. In the Resource Type drop-down list, select IP Address. In the Group drop-down list, make sure the right resource group is selected.
- In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click next in the Dependencies dialog box, select the drive from Available Resources, and click Add to add it to the Resource Dependencies list. Click Next.
- In the TCP/IP Address Parameters dialog box, enter a static IP in the Address input box, and select the proper public network to use. Click Finish. Click OK. And the resource now appears in the resource group and has a state of Offline.
- Right-click MS DTC’s resource group, selects New, and then selects Resource. In the New Resource dialog box, enter an appropriate name for the network name resource that will be associated with MS DTC such as MS DTC Network Name. In the Resource Type drop-down list, select Network Name. In the Group drop-down list, make sure the right resource group is selected.
- In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible Owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next.
- In the Dependencies dialog box, select the MS DTC IP address from Available Resources, and click Add to add it to the Resource Dependencies list.
- In the Network Name Parameters dialog box, enter a name for MS DTC that will be used by other applications. Click OK. The resource now appears in the resource group and has a state of Offline.
- Right-click MS DTC’s resource group, selects New, and then selects Resource. In the New Resource dialog box, enter an appropriate name for the MS DTC resource itself such as MS DTC. In the Resource Type drop-down list, select Distributed Transaction Coordinator. In the Group drop-down list, make sure the right resource group is selected.
- In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the available Nodes list, and click Add. Click Next.
- In the Dependencies dialog box, select the physical disk and the MS DTC network name from Available Resources, and click Add to add them to the Resource Dependencies list. Click Finish. And Click OK. The resource now appears in the resource group and has a state of Offline.
- Start the newly created resources by right-clicking the MS DTC group and selecting Bring Online, All resources should now have a status of Online
Enabling Network MS DTC Access
- To ensure that external processes can utilize the clustered MS DTC, you must enable network MS DTC access. Follow these steps:
- Log on to the node that currently owns MS DTC.
- Open Add or Remove Programs in Control Panel.
- Click Add/Remove Windows Components.
- Select Application Server, as shown in Figure 5-120. Click Details.
- In the Application Server dialog box, make sure that the Enable Network DTC access option is checked. Click OK. Click Next. You might be prompted for your Windows installation media, so you should have it nearby or on an accessible network share.
- Click Finish on the Completing the Windows Components Wizard dialog box, Stop MS DTC by right-clicking the cluster group and selecting the option Bring Offline.
- Restart MS DTC by right-clicking the cluster group and selecting the option Bring Online.
Testing the Server Cluster
- Review the Event Log
- Verifying Network Connectivity and Cluster Name Resolution using ping utility
- Validates Resource Failover
- Check Disk Semantics
Cluster validation wizard
The Cluster validation wizard checks following thing to make sure windows server cluster works properly.
- At least two separate network cards exist per server.
- Each network card has a unique IP address on a separate subnet.
- Each server can communicate with all other intended nodes of the server cluster.
- The shared disks that will be used for your cluster are accessible from all machines, are visibl only once, and are uniquely identifiable.
- The network and disk I/O latencies are within spec.
- The disk bus reset or LUN reset.
- SCSI reserve/release, reservation, breaking, and reservation defense mechanisms.
- Online/offline to simulate failover.
Changing the Cluster Service Account Password
Changing the password is a completely online operation using the command-line CLUSTER.EXE with the /changepassword option
cluster /cluster:clustername /changepassword:newpassword,currentpassword /test /verbose
This tests that the password can be changed successfully.
You can use five options when changing the password and you can combine them:
- /skipdc: If you have already changed the password at the domain level and just need to change it on the cluster nodes so that the cluster service works properly, use this option. If you do not use this option, the password will also be updated at the domain.
- /force: This switch will force CLUSTER.EXE to proceed with the password change even if nodes of the cluster are unavailable.
- /test: Before doing the actual password change, this switch will check to see whether the password is able to be changed on the nodes as well as at the domain (if you are not using/skipdc).
- /quiet: This switch will suppress all output except errors.
- /verbose: This switch will show all output. I recommend using /verbose because it will give you the most information.
Adding a New Disk to the Cluster
- Create the disk space on your shared disk array with the appropriate zoning and masking. Most will support creating the LUN while servers are up and connected. Please check with your array’s vendor to ensure that you can do this because you do not want to potentially ruin your disk configuration. If your array does not support live LUN creation, you will have to shut down the servers connected to it.
- Power down all the nodes except one in your cluster.
- Sector align the disk using DISKPART
- Format the disks using NTFS file System.
- Open Cluster Administrator, right-click the resource group where you want to place the new disk resource, select New, and then select Resource.
- In the New Resource dialog box, enter an appropriate name for the mount point. In the Resource Type drop-down list, select Physical Disk. In the Group drop-down list, make sure the right resource group is selected. Click Next.
- On the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next.
- In the Dependencies dialog box, do not make this resource dependent on anything. Click Next.
- In the Disk Parameters dialog box, verify the proper disk is selected. Click Finish. Click OK. The Resource now appears in the resource group and has a state of Offline.
- Start the newly created resource by right-clicking the group and selecting Bring Online. The disk will now have a state of Online.
- If this drive is added after SQL Server is in production, add the additional disk as dependencies to SQL Server or Analysis Services resource.
- In Windows Explorer, check that the new disk is ready for use. Test that you can write to and read from the new drive by creating a text file and opening it.
- Power up one of the other nodes, failover the resource group with the new mount point to the node you just turned on and verify that, you can read and write files from it.Repeat step 14 for all nodes in the server cluster.
Changing the Quorum Disk
To use Cluster Administrator to change which disk the quorum uses, follow these steps:
- Open Cluster Administrator. Select the cluster group that contains the disk that will become the new quorum. In the right pane, right-click the disk resource, select Change Group, and then select Cluster Group. At the confirmation dialog box, click yes.
- In the Move Resources dialog box, click yes. Verify that the disk is now in the Cluster Group, Select the cluster name in the left pane, right-click the name of the server cluster, and select Properties.
- Select the Quorum tab. In the Quorum Resource drop-down list, select the drive you moved to the Cluster Group, and resize the quorum log to an appropriate size. Click Apply, and then click OK.
- If the disk has no issues, you can move it to another resource group to repurpose it. If the Disk is no longer needed or damaged, delete the resource from the cluster. To delete it, right click the disk resource, and select Delete.
Installing SQL server cluster on windows clustering
- Ensure the Windows Failover Cluster Is Configured Properly
- Create the SQL Server 2005 Failover Clustering Service Accounts and Groups
- Add the newly created Cluster Groups to Each Node in local administrator group.
- Rename the Cluster Resource Group
- Install .NET Framework 2.0
- Install the .NET Framework 2.0 on each node of your cluster,
- Log on to one of the nodes of the cluster as the cluster administrator(log off all other nodes) account and Start Cluster Administrator, and check to see whether the group you renamed earlier is owned by the node you are logged on to. If it is not, move the group to the node.
- Start setup.exe.
- Setup will now do a more exhaustive check of the node that started the installation process,. Any warnings or errors will be detected, and you can see them by clicking Messages next to the appropriate item or by viewing a report via the Report dropdown list. Click Next.
- On the Components to install page, select the options you want to install as part of your new SQL Server 2005 failover clustering instance. Only SQL server database service and analysis service is cluster able. Click on Create SQL server failover cluster below SQL server database services.
- Select default instance or named instance for SQL server database service. If you are installing a named instance, select Named Instance, and enter the second part of the name (that is, if your named instance will be SRV\SQL1, enter SQL1 here)
- On the Virtual Server Name page, enter the name of the SQL Server that will be registered in DNS. This will be the name used to access SQL Server if it is a default instance; it will be the first part before the backward slash (so enter SRV in SRV\SQL1) if it is a named instance.
- On the Virtual Server Configuration page, select the network in the Network to Use list that will be used by SQL Server to allow connectivity from users and applications. This should be an externally (public) facing network in the Windows server cluster. Enter the static IP address that will be used by SQL Server.
- On the Cluster Group Selection page, select the cluster group from the Available Cluster Groups list, which contains the disk that this clustered SQL Server installation, will use for its system databases. Never select the default cluster group, which contains the quorum disk, or the group that contains the Microsoft Distributed Transaction Coordinator.
- On the Cluster Node Configuration page, all available nodes by default will be added to the definition of the failover clustering instance. If nodes are unavailable for some reason, there will be an appropriate entry in the Unavailable nodes section, If you are using the Standard Edition installer on a cluster that has more than two nodes, you can allow only two nodes in total to be part of the definition of the failover clustering instance, click Next.
- On the Remote Account Information page, enter the password of the cluster administrator whom you are logged in as. If the user you are currently logged in as is not an administrator of the cluster nodes, cancel Setup, log off, and log on as the proper user. Click Next.
- The Service Account page is where you enter the service accounts and passwords for the various services depending on the configuration you selected, you should use different accounts for each service or use same account for all services. Click Next to continue.
- On the Domain Groups for Clustered Services page, select the domain groups that will be used for the failover clustering instance. If you know the names of the groups, enter them in the appropriate text box in the format DomainName\GroupName. Click OK when finished.
- On the Authentication Mode page, click either Windows Authentication Mode, or Mixed Mode,. Most installations will use Mixed Mode. If you select Mixed Mode, you will be prompted to enter a password for the sa user. You cannot use a blank password for the sa user in SQL Server. Click Next.
- Select the proper collation you need—or leave the defaults—and then click Next.
- Setup will then start to prepare your install, during the installation process, you can see the progress of the install on each node by selecting the node in the drop-down list, This new feature of the installer is a welcome addition because you can now see what is going on during Setup on each individual node and the progress for each node.
Testing the Failover Cluster
- Reviewing the Event Log
- Verifying Network Connectivity and Cluster Name Resolution
- Performing Failover Validation
- Verifying the Disk Configuration using sys.dm_io_cluster_shared_drives or fn_servershareddrives()
- Verifying the Node Configuration by using sys.dm_os_cluster_nodes or fn_virtualservernodes()
Administrating SQL server Cluster
- To find out which node currently owns the SQL Server resources, issue the query SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’).
- To find out whether the instance is cluster or not use SELECT SERVERPROPERTY(‘IsClustered’).
- SQL Server Surface Area Configuration is cluster-aware application and before using it with cluster server we need to configure it to communicate with a failover clustering instance. To connect to a clustered instance of SQL Server, click the Change Computer link. Enter the name of the instance that is registered in DNS.
- The SQL service can be controlled from SQL Server Configuration Manager, SQL Server Surface Area Configuration, and Cluster Administrator.
- SQL Server Configuration Manager is the preferred method of stopping and starting all SQL-related services. SQL Server 2005 SQL Server Configuration Manager is fully cluster-aware.
Changing Collation of SQL server Database
Posted by: Manpreet Singh on: June 30, 2009
As we all know that collation is a important part of Sql server storage engine. The collation defines who data is stored in Sql Server. The physical storage of character strings in Microsoft® SQL Server™ is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. It can have following characteristics
- Language
- Case sensitivity
- Accent sensitivity
- Kana sensitivity
We can use the sp_helpsort stored procedure to find out the collation of our server
Old versions like SQL Server 7.0 and SQL server 6.5 doesn’t support databases that have multiple collations. Therefore, all the databases that we create in SQL Server 7.0 use the default collation. SQL Server 2000 and later supports multiple collations. SQL Server 2000 databases can have collations other than the default collation. Additionally, SQL Server 2000 also supports columns that have collations other than the collations of the databases where they were created. in SQL server 7.0 if we need to change the collation of any database then the only way is to create database and all the objects on the destination server and then transfer the data by using DTS or the BCP utility.
Changing collation of database running SQL Server 7.0
To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 7.0 and that has a different collation, follow these steps:
- Back up the source database.
- Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
- To export the data from all the tables in the database, use DTS or the BCP utility.
- Create a new database on the destination server by using SQL Server Enterprise Manager or the CREATE DATABASE statement.
- Use SQL Query Analyzer to run the scripts that you created in step 2 to re-create all the objects in the destination database.
- Import the data in the destination tables by using DTS or the bcp utility.
Changing collation of database running on SQL Server 7.0 to SQL server 2000
To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 2000 and that has a different collation, follow these steps:
- Back up the source database.
- Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
- If the columns must have a different collation than the default collation on the destination database, make the required collation changes to the appropriate columns in the scripts.
- Create a new database on the destination server with the appropriate collation.
- Use SQL Query Analyzer to run the scripts that you created in step 2 on the destination server to re-create all objects in the database.
- Use DTS or the bcp utility to transfer the data.
Points to remember:
- The new tables and columns have the same collation as the database unless you specify a different collation for the columns.
- If you use DTS, Make sure that the Use Collation option is enabled when you transfer data from SQL Server 7.0 to SQL Server 2000.
- If you use DTS, remember the objects are already created on the destination server with the appropriate collation, disable the Create Destination Objects First option.
Changing collation of database running on SQL Server 2000 to SQL server 2000 or later
To transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000, follow these steps:
- Back up the source database.
- Note if any columns use the COLLATE clause.
- Create a new database on the destination server with the appropriate collation.
- If no columns use the COLLATE clause, use DTS to transfer the data to the destination server. To do so, enable the Use Collation option for code page translation and to transfer the data to the new collation on the destination database. If any columns use the COLLATE clause, follow these steps:
- Generate scripts for all the objects (not including the indexes, the triggers, the primary keys, the foreign keys, the default settings, and the constraints). Additionally, make sure that you enable the Only script 7.0 compatible features option to remove the COLLATE clause from the script.Note When you use the Only script 7.0 compatible features option, you can change the collation. However, any new SQL Server 2000 options (including user-defined functions, extended properties, the INSTEAD OF trigger, and indexes on views) will not be considered when the scripts are generated.
- Run the scripts from step a on the destination database to create the objects with the destination database collation.
- Use DTS to transfer only the data from the source database.
- After the data is successfully transferred, generate scripts for all the constraints, foreign keys, primary keys, and indexes from the source database.Note On the Formatting tab of the Generate SQL Scripts dialog box, click to clear the following check boxes:
- Generate the CREATE <object> command for each object
- Generate the DROP <object> command for each object
- Run the scripts from step d on the destination database.
Please note that the following methods do not change a database’s collation:
- Backup and restore: Restoring a database on a server that has a different collation than the server that is used for the backup does not convert the restored database to the new collation. The database collation remains as is.
- Detach and reattach: If you detach a database that was created with one collation and you reattach the database to another server that has a different collation, the collation of the database does not change. The collation of the database remains as is.
- Copy Database Wizard: The Copy Database Wizard essentially automates the process of detaching and reattaching. The collation of the database remains as is.
SQL Installation Server Setup Checklist
Posted by: Manpreet Singh on: June 26, 2009
Before Install
Create at least one new Active Directory account for SQL Server.
This is best practices, and a popular shortcut is to run all of the database servers under the same service account. I’ve run into two problems with this approach. First, I’ve had a sysadmin repeatedly fat-finger the service account password, thereby locking out the service account. When all of the SQL Servers suddenly run into service account problems, that are a really ugly outage.
Second, there have been times when I’ve needed to give permissions to a specific SQL Server that other servers shouldn’t get. If xp_cmdshell is enabled, then DOS commands will be run with the SQL Server’s service account permissions. I hate giving any more permissions than necessary, so if a user needs to write to remote locations using xp_cmdshell, it’s nice to lock down the permissions on that one database server’s account instead of enabling every SQL Server to write to that remote location.
I say “at least one new account” because best practices dictates using separate accounts for the database service, fulltext service, agent, etc. I’ll slack a little there and share the same account sometimes, especially on development boxes.
Configure local security settings for the SQL Server 2005/2008 service account.
Go into Local Security Settings (click Start, Run, type SECPOL.MSC and hit enter) and click Local Policies, User Rights Assignment.
In the permission “Perform Volume Maintenance Tasks”, add your SQL Server service account (or a domain group if you use that). This enables SQL 2005’s Instant File Initialization, which lets SQL Server instantly grow data files without erasing the physical disk first. This is a huge performance gain for large databases like data warehouses.
In the permission “Lock pages in memory”, add your SQL Server service account or domain group. This lets SQL Server keep all of its data in physical memory instead of swapping it out to disk.
If the SQL Server uses SAN storage….
Update the HBA firmware. Downlevel HBA firmware has caused me all kinds of nasty problems, especially in clustered servers. Generally, these updates can’t be done online while the server accesses data, so it’s better to get the code up to date before the box goes into production.
Set up multipathing drivers. Sometimes this is done by the storage team, but the DBA should get involved enough to understand whether the multipathing is active/active or just failover.
Test the multipathing & failover. Start a huge file copy to each array, and do them all simultaneously. Go into the datacenter and pull one fiber cable out. Watch to make sure the file copy continues. Some SAN drivers will take 10-15 seconds to fail over, but the file copies should not be disrupted, period. If they’re disrupted, the multipathing didn’t work. Then plug the cable back in, and pull another cable. Again, the file copy should continue. Finally, while a file copy is running, ask the SAN admin to disable one of the SAN zones for the server – that way, the fiber cable will still be lit up, but the path to the storage will be gone. (This is a tougher failover method than just pulling the fiber.)
If the SQL Server uses iSCSI storage….
Set up teaming software. Database servers can’t rely on one single network connection for iSCSI any more than a fiber-connected SAN can rely on one single host bus adapter. Ideally, the two network cards will be connected to two different switches for redundancy, but at the very least, we need two network cards. The teaming method can be active/active (meaning 2 gigs of throughput for two teamed 1 gig nics) or active/passive.
Test the multipathing. In my experience, I’ve usually seen active/passive on a per-array basis – meaning, if you have two different iSCSI drive letters, then the teaming or multipathing drivers will put each drive on its own network card. The EMC and LeftHand multipathing appears to do this by default. Start multiple simultaneous drive copies and go into Task Manager, in the Network tab. Look at the bandwidth used by each network card. If a network card is sitting idle, then you’re leaving performance on the table. Now is the time to tweak the multipathing software and ask questions of the vendor – it’s easier to troubleshoot file copy performance than it is to troubleshoot SQL Server performance.
Test the failover. As with the fiber cable testing, start multiple simultaneous file copies to/from the network drives and pull one network cable out. If the file copy fails (if Windows throws an error) then SQL would have crashed. Tweak the teaming software until it can fail over seamlessly, and ideally it should fail over back and forth and go back to higher bandwidth levels as the networks come back online.
Use DISKPART to create aligned partitions.
Microsoft KB article 929491 covers some of this in technical detail, but the Microsoft Exchange Team blog does a much better job of explaining why disk partition alignment is important for performance. Use this command in Diskpart.exe:
CREATE PARTITION PRIMARY ALIGN=1024
The 1024 number will work with every major SAN out there. Gurus can use smaller numbers when they know a specific SAN very well, but small numbers only save you a few hundred kilobytes in the entire disk. Be safe, be sure, and use 1024, and you won’t get burned if the underlying SAN structure changes, like with today’s virtual storage.
Install the Windows components for SNMP and WMI.
These management tools can be found in Add/Remove Programs, Add/Remove Windows Components, Management. They need to be installed before the SQL Server install starts because SQL will only create the monitoring mechanisms if SNMP & WMI are already installed.
For 32-bit servers, configure AWE and PAE.
One of my favorite sites, SQL-Server-Performance.com, has the most straightforward explanation of how to configure these obscure settings in boot.ini:
- 4GB RAM: /3GB (AWE support is not used)
- 8GB RAM: /3GB /PAE
- 16GB RAM: /3GB /PAE
- 16GB + RAM: /PAE
You can read more about AWE and PAE at SQL-Server-Performance, who does a better job of explaining this than I ever could.
Set the page file on the C drive to 1.5x physical memory.
I’ve been through two separate Microsoft SQL Server health checks done by very, very intelligent MS guys, and I got two different opinions about page file size. We did a lot of escalation at Microsoft, and I got the best answer from J.C. Armand, a great SQL resource.
For advanced troubleshooting, there has to be a page file on the C drive big enough to do a memory dump. This isn’t a strict requirement, but if SQL Server ever crashes hard, that memory dump will be invaluable. I have actually had this happen, and I’ve even been able to reproduce the crash on several different servers – we even did it at the Microsoft Technology Center in Chicago. It was some stunningly bad code coupled with an ugly database, and I got a lot of laughs out of that. Anyway, the point was we needed a memory dump to troubleshoot it, and the only way we could get that was a giant C drive page file.
The page file doesn’t have to be 1.5x memory in order to do that, but the consensus among the experienced DBAs was to start the server at a high page file size, then watch the utilization carefully over time and scale the page file size down as necessary.
After The Install
Install not just the service packs, but also the cumulative updates.
Starting with SQL Server 2005’s Service Pack 2, Microsoft releases hotfixes in cumulative packs. These updates do more than just fix bugs: they improve how SQL Server performs. These updates are free performance benefits – and who doesn’t like that?
Double-check that Instant File Initialization is enabled.
Paul Randal wrote an excellent blog post on how to tell if instant initialization is enabled. Follow the instructions in his post, and you’ll know for sure. (While you’re there, subscribe to his blog – it’s chock full of SQL-y goodness.)
Best Practice: Move TempDB to its own drive.
By default, the TempDB files are put on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that’s not a good idea either. Instead, the TempDB data files should be on their own dedicated drive.
Fix this by first moving TempDB to its own drive. In this example, I put the data file on the T drive and the log file on the L drive. (Be aware that the directory paths must already exist.)
use master
go
alter database tempdb modify file (name=’tempdev’, filename=’T:\MSSQL\DATA\tempDB.MDF’, size = 1mb)
go
alter database tempdb modify file (name=’templog’, filename=’L:\MSSQL\LOGS\templog.LDF’, size = 1mb)
go
I only set a 1mb file size because SQL Server does something tricky: even though we’re telling it to use a different drive letter, it will look for this amount of free space on the drive TempDB currently uses! If SQL Server was installed on the server’s C drive, for example, and we try to create a 10gb TempDB file on a T: drive, that SQL command will fail if there isn’t 10gb of free space on the C drive. Yep, it’s a bug – get over it.
After this code runs, restart the SQL Server. That will create the new TempDB file on the new drive. Manually delete the old TempDB file on the original drive, because SQL Server doesn’t delete that itself.
Now that TempDB is on the right drive, expand it to the full size you want, and then create one additional TempDB data file per core. Here’s the code to create one additional TempDB data file – you can modify this for more files:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdev2.ndf’ , SIZE = 10GB , FILEGROWTH = 0)
GO
The data file creation should only take a couple of seconds – if it takes more than ten seconds, then instant file initialization isn’t configured correctly. We talked about this back in the pre-installation checklist, so go back and revisit that before you create the next TempDB file. Fix the security to allow for instant file initialization now – it has a huge performance impact on database growth.
Assuming that one file growth only took a couple of seconds, then go ahead and create the rest of the TempDB data files.
Notice that I don’t have filegrowth enabled. You want to proactively create the TempDB files at their full sizes to avoid drive fragmentation. If you have a dual-cpu quad-core server (8 cores total) and an 80-gb array for TempDB data, you would create eight 10gb files for TempDB. That way, each file is contiguous, all laid out in one big chunk. If you create them as smaller files and let them autogrow, then the disk will be fragmented all over the place because the files will be growing at random times. Plus, you could end up with differently sized TempDB files if one of them happened to grow faster than the rest. That’s why I pre-grow all of the TempDB files ahead of time and get them at exactly the right size.
Configure SQL Server memory for best practices.
Sounds easy, right? Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just configure it. There’s only a couple of fields – how hard could it be?
Oh, this screen is full of danger and pitfalls.
First, that tricky checkbox that says “Enable AWE”. You might think you only have to check that box if you’re using a 32-bit server with more than 4 gigs of memory, but nooooo. According to a couple of Microsoft folks I’ve spoken with (and I don’t mean first line support), that checkbox causes SQL Server to handle memory differently – not just enable AWE. In fact, that box should be checked on 64-bit servers, too – any box with more than 4 gigs of memory!
Second, the minimum and maximum memory amounts are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory we want SQL Server to take.
Ideally, no one would ever remote desktop into a SQL Server and run programs. Unfortunately, this happens, and we have to plan for it by leaving enough free memory for people to run things like SQL Server Management Studio. When I’m first building a server that isn’t running any other applications at all, I like to leave 10% of the memory free, or 4gb, whichever is larger. Then I monitor the free memory over the course of a month or two, and adjust it up or down during the next outage window.
If the server does multiple duties like act as a web server or application server, we have to be much more conservative with memory. Application owners never seem to know how much memory they’ll really use in production: SAP BW’s Netweaver, for example, tends to use anywhere from 10% to 50% of the memory on our production server, and it’s tough to predict. As a result, we have to leave the SQL Server’s memory allocation at just 50% of the available memory on the server.
The only way to know the right answer long term is to use Perfmon or a performance monitoring utility to watch the server’s free memory.
Set the Default Database Path
Even if you chose this during setup, we need to revisit it because SQL Server puts both the data files and the log files in the same directory. In SSMS, right-click on the server name and click Database Settings. The paths for the data files and log files can be configured from there.
Of course, this assumes that we have separate drives for the data and log files, which is the right way to go for performance purposes.
Configure Database Mail with public & private profiles.
Database Mail is a pure SMTP solution that beats the daylights out of SQL 2000’s SQLmail. It doesn’t require Outlook to be installed on the database server, doesn’t need any MAPI code, and works with any company email server that can be accessed via SMTP.
There’s plenty of sites on the web that explain how to configure Database Mail, but I want to address something: be aware that developers can use Database Mail for things that SQL Server shouldn’t be doing. For example, they may decide to use Database Mail to send out mass emails to your end users or customers. There’s nothing technically wrong with that, but it increases the load on the database server and it sends all outgoing email with the SQL Server’s Database Mail account.
At our shops, we use internal emails like (servername)@ourcompany.com to identify which server is sending the database mail. Those email addresses make sense to us because we just need to know where the alerts are coming from – we would never hit Reply to a server-generated email.
However, if developers use SQL Server to send out emails directly to customers, those customers will indeed reply. I had a nasty problem where a couple of developers decided to purge old customer accounts, and they used SQL Server’s Database Mail to broadcast an announcement to those users. The email read something like, “You haven’t used your account in 30 days, so we’re deleting it. Please contact us for questions.” Of course a lot of customers got aggravated and sent some nastygram replies, which arrived in the IT team’s inboxes, who had no idea what was going on. After some confusion, we were able to track down the guilty party, but those emails never should have gone out from the IT staff.
Bottom line: if you decide to use Database Mail (and you should), consider setting up separate private and public email profiles. The public email profile used by the developers should be sent from the developer management team’s group email address – that way, they can address any replies themselves.
Configure SQL Server Agent’s failsafe operator.
After configuring Database Mail, create at least one SQL Server Agent operator. This operator’s email address should be a distribution list for the database administrator group. Even if the company only has one DBA, never use an individual person’s email address – use a distribution list instead. When the DBA goes on vacation or gets a job at another company (or heaven forbid, gets fired), it’s easier to add someone to a single distribution list instead of modifying operators on dozens or hundreds of servers.
Then right-click on the SQL Server Agent, configure the alerting system to use Database Mail, and set up that DBA group as the failsafe operator. That way if anything happens and SQL Server doesn’t know who to alert, it can alert the group.
Create default alerts for severities 16 through 25.
SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.
The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘Database Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7
GO
Install the SQL Server 2005 Performance Dashboard Reports.
These are an insanely cool and free extension for SQL Server Management Studio.
- Download the SQL Server 2005 Performance Dashboard Reports
- Read about them on SQL-Server-Performance
You run the setup.exe on your personal workstation, and then you have to execute the setup.sql script on each server that you want to monitor. It only takes a few minutes, but the information that it gathers will help you manage your server better throughout its lifetime.
Set Up Maintenance Plans
This is where things start to get different on a shop-by-shop basis. Some places use native backups, some places use backup compression software, some places use index defrag software, and so on
Benchmark It, and Find the Slowest Link
Before it goes into production, load test and stress test it to find where you’ll need to improve performance down the road. Before it goes live, this is your one chance to really bang the daylights out of it without anybody complaining.
