ISCSI and SQL Server
Posted by: Manpreet Singh on: June 22, 2009
ISCSI is a great new technology in the world of storage. It is the standard which provide storage interface by carrying SCSI command over TCP/IP network. It helps organizations to consolidate storage and provide host with a illusion of locally – attached disk storage. Microsoft fully supports Microsoft SQL Server when it is deployed on ISCSI storage that has received the “Designed for Windows” Logo Program qualification. Microsoft windows server presents the ISCSI storage as ordinary drives attached to computer. To users and applications, including SQL Server, the remote destination is encapsulated. There are certain points which needs o be remember before using ISCSI technology with Sql server 2005 or later. Some of them are as follows
- Server clustering supports iSCSI clustered shared storage on Windows Server 2003 Service Pack (SP) 1 or higher only. Depending on the configuration, the following are supported
- Up to eight-node clusters, if using Storport with iSCSI Host Bus Adapter (HBA) Storport miniport
- Up to eight-node clusters, if using the Microsoft iSCSI Software Initiator.
- Although, customers with ISCSI Windows Server 2003 cluster environments will be supported as a cluster configuration without submission to the Windows Hardware Quality Labs (WHQL) and being listed on the Windows Server Catalog. But the support is contingent on the following provisions
- Customers must contact their vendor to ensure that the vendor has conducted internal testing to validate cluster compatibility and that they fully support the end-to-end solution with Server Clustering
- Customers must be running Windows Server 2003 Service Pack 1 or higher
- Solutions must leverage the Microsoft ISCSI Software Initiator used in conjunction with a logoed network interface card (NIC). Solutions that leverage an ISCSI HBA do not apply for the waived support policy; they must be Hardware Compatibility Test (HCT) qualified and listed on the Windows Server Catalog
- All iSCSI hardware used in the configuration must be qualified under the Designed for Windows Logo Program in their individual device categories (such as HBAs or iSCSI targets). For a complete list of qualified devices, please see the iSCSI Hardware Devices qualified in the Windows Server Catalog of tested products.
- Windows Server 2003 SP1 contains a substantial number of improvements for failover handling. Clusdisk enhancements take advantage of SCSI Unique IDs, allowing better disk identification and arbitration when using a Storport miniport or the Microsoft iSCSI Software Initiator. SCSIport does not support individual LUN resets. Individual Logical Unit Number (LUN) resets are critical in multinode cluster scenarios in which disk failovers must occur nondisruptively (leaving the disks not being failed over fully functioning)
- Please note that ,Service Pack 1 or higher is required for all Windows Server 2003 iSCSI cluster deployments to be supported
- Remotely booting a Windows Server from an iSCSI SAN is supported with either SCSI HBA or the Microsoft iSCSI Software Initiator Boot Version
- iSCSI is not supported with windows server 2000. The Microsoft Windows Hardware Compatibility Test (HCT) is no longer accepting any cluster submissions for Windows 2000 to be listed on the Windows Server Catalog. For more information see the Microsoft support life-cycle policy. New cluster deployments should be built on Windows Server 2003.
- Microsoft ISCSI software initiator is fully supported with no restrictions for full eight-node cluster
- These are the recommendations for the Gigabit Ethernet switch to be use with ISCSI
- Use an enterprise class switch
- Ensure the switch is of non-blocking design
- Recommend using full end-to-end Gigabit Ethernet. If using full GigE, use Ethernet Jumbo Frames for greater throughput and less CPU interruption
- Recommend using the highest possible network Maximum Transmission Unit (MTU)
- Recommend using a dedicated switch for the iSCSI SAN
- If the cluster is using Fibre Channel storage behind an iSCSI-to-Fibre Channel bridge device then still it is considered as an iSCSI cluster. Following are the guidelines for a ISCSI cluster
- Mixed storage topologies are not supported within the same cluster–all nodes must use the same protocol. If the cluster configuration is iSCSI, all nodes must use iSCSI only: do not add in Fibre Channel nodes
- You must ensure that the manufacturer of the iSCSI bridge has tested and qualified with the specific Fibre Channel or SCSI redundant array of independent disks (RAID) that is being used in the customer configuration and that the array contain sufficient cache if the iSCSI bridge does not contain cache or has insufficient cache
- The bridge is considered to be an iSCSI bridge device, and must therefore be approved as such under the Designed for Windows Logo Program for iSCSI devices.
- There are no differences between iSCSI and Fibre Channel testing requirements. Vendors may use the latest released Cluster DTM test kit.
- the Microsoft recommended requirements for ISCI specific clusters are as follows
- All iSCSI components (HBAs, storage arrays) must comply with the iSCSI device logo program requirements and pass required testing using the appropriate iSCSI HCT kit for the device
- The iSCSI SAN must be on an isolated network, both for security and performance. Any networking standard practice method for achieving this end is acceptable, including:
- A physically separate, dedicated storage network. In this recommended configuration, each node contains three different NICs: a public network for external client communication, a private network for inner-node communication and an iSCSI SAN network
- A physically shared network with the iSCSI SAN running on a private virtual local area network (VLAN). The switch hardware must provide Class or Service (CoS) or Qualify of Service (QoS) guarantees for the private VLAN.
- If multiple clusters and/or systems are used on the same SAN, proper segregation or device isolation must be provided. In other words, the storage used by cluster A must be visible only to cluster A, and not to any other cluster, nor to a node from a different cluster
- The use of session authentication (Challenge Handshake Authentication Protocol (CHAP) minimum) is mandatory. This provides a degree of security as well as segregation
- Mutual CHAP or Internet Protocol Security (IPSec) can also be used.
- The use of session authentication (Challenge Handshake Authentication Protocol (CHAP) minimum) is mandatory. This provides a degree of security as well as segregation
- Take the following configuration settings into consideration when using the MS Software Initiator
- Set all clustered volumes as “Persistent Bindings” to ensure they are remapped if the node is rebooted
- Set “Bind Volumes” for all clustered disks to ensure they are fully mounted by the iSCSI service before the Cluster Service attempts to bring them online.
- Ensure you are using Microsoft iSCSI Software Initator 2.0 or above
- A minimum of three physically different NICs are needed in each node in the cluster
- Public: A network card that is used for connectivity with external clients
- Private: A network card that is used for internal cluster communication. This NIC must be on a physically separate network
- SAN: A network card that is used for communication to the iSCSI target storage device. This NIC should be on a physically independent network that is not associated with any of the above. This network interface should not be enabled for cluster use
- NIC teaming is not supported on the iSCSI interface. Please see more info below
- Public: Network card that is used for connectivity with external clients. NIC Teaming is fully supported on this interface
- Private: Network card that is used for internal cluster communication. NIC Teaming is NOT supported on this interface
- SAN: Network card that is used for communication to the storage device. NIC Teaming is NOT supported on this interface. Instead use Microsoft Multipath I/O (MPIO) or multiple connections per session (MCS per iSCSI specification) to achieve fault tolerance
Understanding SQL Server Database Restore Process
Posted by: Manpreet Singh on: June 1, 2009
Restore is very important part of whole database administration process. Understanding the internal working of restore process helps administrators to create disaster recovery processes for organizations. Restoring a SQL database is the process of using full or differential backup files and their subsequent Transaction log files to roll the database forward to a certain point. Recovery is the overall set of Processes that SQL Server runs to ensure that the database is in a consistent state when it is brought online, which might include such processes as rolling back uncommitted transactions.
Restore is a multi-phase process and include three main phases: data copy, redo, and undo.
Data Copy Phase
During this phase of restore, the contents of the database, files, or pages being restored are initialized the data is then copied from one or more full backups and, optionally, differential backups, and then resetting the contents of the affected database, files, or pages to the time that they were captured by those backups. The oldest file used in the backup set of files (also known as the roll-forward set) will contain the LSN for the redo phase.
Redo Phase
The redo phase is also known as the roll-forward phase. This is when the transaction logs are processed to ensure the consistency of your databases. If you specified a specific point in time to recover to (date or marked transaction), this will be done in the redo phase. Even with all things rolled forward that have been committed, there might still be some uncommitted transactions. Also, Restore avoids unnecessary roll forward. Generally, if data was read-only when it was backed up and has remained read-only, roll forward is unnecessary and is skipped.
Undo Phase
Undo is the last step in ensuring the consistency of your database. In this phase, any changes made by uncommitted transactions are undone, or rolled back. At the end of undo, no more backup files can be processed, and the database is brought online. Undo will not be done if WITH NORECOVERY is specified. Also, If the data is already transactionally consistent at the start of the recovery process, the undo phase is skipped. After the database is transactionally consistent, recovery brings the database online.
System Databases in SQL server 2005
Posted by: Manpreet Singh on: May 26, 2009
Whenever we install new SQL server instance on server, it include four system databases by default named: Master, Model, Tempdb, and MSDB. It also contains a fifth, stealth database that you will never see using any of the normal SQL tools or commands. This database is referred to as the resource database, but its actual name is mssqlsystemresource.
Master
The master database is arguably most important database in SQL working. It composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations and usage, it also contain system wide configuration settings regarding SQL instance , endpoints information , login accounts, databases on the current instance, and the existence of other SQL servers (for distributed operations).
The master database is critical to your system, so always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such actions, you should back up master.
Model
Model database is act as a template database to every new database which is created on SQL server instance. Every time when we create a new database, SQL Server simply makes a copy of model database to form the basis of the new database. If someone like every new database to have certain objects or permissions, he or she can put them in model database, and all new databases will inherit them. We can also change most properties of the model database by using the ALTER DATABASE command, and those property values will then be used by any new database you create.
Tempdb
The tempdb database is used as a temporary storage for system objects, data and intermediate results. It is unique among SQL Server databases because it is re-created every time SQL Server is restarted. It’s used for temporary tables explicitly created by users, for worktables that will hold intermediate results created internally by SQL Server during query processing and sorting, for maintaining row versions used in snapshot isolation and certain other operations, and for materializing static cursors and the keys of keyset cursors. Because the tempdb database is re-created, any objects or permissions that you create in the database will be lost the next time we restart our SQL Server instance. An alternative is to create the object in the model database, from which tempdb is copied.
The tempdb database sizing and configuration is critical for optimal functioning and performance of SQL Server, so I’ll discuss tempdb in more detail in its own section later in this chapter.
Mssqlsystemresource
Mssqlsystemresource database is a hidden and read-only database that contains Executable system objects, such as system stored procedures and functions. It is stored as mssqlsystemresource.mdf / .ldf file combination and installed in the same location as the master database in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data as it is SQL Server requirement that both the master and resource databases will remain in same location. However, if we start our SQL Server instance in single-user mode, we can make a single connection, and that connection will be able to use the mssqlsystemresource database. Its content is specific to a version / patch level and because it cannot be included in regular a SQL Server backup we should always make a manual copy of both .mdf and .ldf files after SQL Server instance is installed and Before and after a service pack of hot fix is applied
MSDB
MSDB database is mainly used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks, and the Service Broker, which provides queuing and reliable messaging for SQL Server. All the information in msdb is accessible from Object Explorer in SQL Server Management Studio, so we usually don’t need to access the tables in this database directly. We can think of the msdb tables as another form of system tables: Just as you can never directly modify system tables, you shouldn’t directly add data to or delete data from tables in msdb.
Find percentage of work completed in SQL Server 2005
Posted by: Manpreet Singh on: May 25, 2009
Yesterday, I and my friend were taking manual backup of database on development server through management studio and suddenly management studio stop responding. I knew that this is happening due to the load added by backup process. We were wondering if there is any TSQL statement through which we can find the status of task running, e.g percentage completed etc. after some googling , I found a DMV(dynamic management view) through which we can found all the information (almost) which we want for any process .
SELECT Session_Id, Reads, Writes, Cpu_Time, Logical_Reads, Total_Elapsed_Time,
Blocking_Session_Id, Percent_Complete, Command,
(select text from sys.dm_exec_sql_text(sql_handle)) as Text FROM
sys.dm_exec_requests(nolock) WHERE session_id = 55
sys.dm_exec_requests is very informative DMV as it provide vast amount of information about any process which is running. the only thing which we need is a SPID of that process . For example if we are taking a backup of any database then, the query column Percent_Complete will give us percentage of work completed. I hope this statement will help you find the amount of work done without going through all the GUI tools and special parameter like STATS etc.
Yesterday, I and my friend were taking manual backup of database on development server through management studio and suddenly management studio stop responding. I knew that this is happening due to the load added by backup process. We were wondering if there is any TSQL statement through which we can find the status of task running, e.g percentage completed etc. after some googling , I found a DMV(dynamic management view) through which we can found all the information (almost) which we want for any process .
SELECT Session_Id, Reads, Writes, Cpu_Time, Logical_Reads, Total_Elapsed_Time,
Blocking_Session_Id, Percent_Complete, Command,
(select text from sys.dm_exec_sql_text(sql_handle)) as Text FROM
sys.dm_exec_requests(nolock) WHERE session_id = 55
sys.dm_exec_requests is very informative DMV as it provide vast amount of information about any process which is running. the only thing which we need is a SPID of that process . For example if we are taking a backup of any database then, the query column Percent_Complete will give us percentage of work completed. I hope this statement will help you find the amount of work done without going through all the GUI tools and special parameter like STATS etc.
Configuring Active Directory and Domain Controller
Posted by: Manpreet Singh on: May 19, 2009
Installing Active Directory needs static IP address. This can be assign by selecting network card properties and then typing in the IP address we wish to use. The subnet mask must also be provided. Both values must be correctly chosen given the networking configuration of the network that the cluster will be a part of. AS AD requires DNS, An address must be provided – in this case we can specify 192.168.100.1. DNS can be installed later with the other components.
Installing AD and DNS
- Click start menu, select run and enter dcpromo to open active directory installation wizard.
- The screen shows welcome message. Click next to start the installation of active directory.
- Next screen contains operating system compatibility issues. Have a look at them and press next to continue
- As we are building new domain controller for our active directory installation therefore select domain controller for a new domain and press next
- Select domain in a new forest in create new domain active directory installation wizard and click next
- In Full DNS name text box enter mydc.com. mydc.com will be our new domain for this example
- Keep the default NetBIOS name and press next.
- Customize the directory where AD database and log files data will be stored or leave the values as their defaults.
- Keep the default values for shared system volume folder location or customize them and press next
- In next step, Select install and configure the DNS server on this computer and press next
- In permissions pane, select permission compatible only with win 2000 or win 2003 as we are not going to use any pre – win 2000 machine.
- Enter restore mode password for active directory and press next.
- Check that all the parameters are correct. Press the back button if any change is required
- The progress of AD creation is shown.
- If successful the wizard should like the example image provided below.
- Restart the computer for changes to take effect.
- The active directory and DNS is now installed on your computer.
Changing SQL Server services account
Posted by: Manpreet Singh on: May 18, 2009
One of my pal is working on a project where they don’t have any dedicated SQL server DBA (they rather have developer DBA). One day he called me and told that he was having a problem with one of their SQL server instances. The instance was installed in my supervision so I was quite sure that someone has changed something. I asked him whether he has changed anything in day or two, he told me, and he has change the service account of SQL server. I know that both the things are correlated, but still I am quite confused as I haven’t found anything wrong with domain account which they were using. When I asked him how he has change that service account, he told me that he has changed it through services.msc. Bingo!! This was the part of puzzle for which I am looking for. I have done nothing after that but simply reconfigure SQL server service account through SQL server configuration manager again and it solves the problem. This problem comes due to the fact that when we first install Microsoft SQL Server to run under a Microsoft Windows NT account, SQL Server sets for that Windows NT account various Windows user rights and permissions on certain files, folders, and registry keys. If we later change the startup account for SQL Server (the MSSQLServer service) and the SQL Server Agent service by using SQL Server Enterprise Manager (SEM) or SQL Server Configuration Manager (SSCM), SEM automatically assigns all the required permissions and Windows user rights to the new startup account But, if we use the Services add-in that is in Control Panel or in Administrative Tools to change the startup account information for the MSSQLServer service or the SQL Server Agent service, there are additional permissions and user rights that we must set for proper working of SQL server service.
- Registry keys.
- NTFS file system permissions on the disk.
- Windows User rights.
Detailed information is given in this article
