About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Monday, May 30, 2011

SQL Server Service Check Utility

I was ask to see if Full Text Search was installed on a development server one day. So after some manual work to collect this information I thought that maybe there could be a better way. So I wrote this script to help out.

Here is how this script could help you:

  • Determine the status of SQL server service(s) - running or not
  • Determine if the service is installed or not
I have executed it on the following servers
32 bit and 64 bit
SQL 2005 with SP3 - Stand alone and on multiple instances
SQL 2008 with SP1 and SP2 - Stand alone and on multiple instances
SQL 2008 R2 latest patches
SQL 2012 with latest patches

Hope you find it useful.

Thanks,
Rudy

Download script here:  https://docs.google.com/file/d/0B07PMQYOhF2DMEliT0pKSmozSDA/edit?usp=sharing

Thursday, May 26, 2011

T-SQL generator for sp_configure

The purpose of this script is to create a T-SQL output that can be execute on SQL Server (2005 and higher) to recofigure the SQL server as compared to the settings on which you have executed this code from. This script is great for rebuilding a SQL server from bare metal, UAT and/or development environments so that it matches your original environment.

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DbU40MXRaUGVmRGM/edit?usp=sharing

Monday, May 16, 2011

Show login information for users on Active Directory

Hello,

To add to my last blog; I have now completed and tested a script that will read active directory to display information on users within active directory. It does not write any information other than to a temp table so you can execute it on whatever environement you like. But test it first.

Note. Make sure you change "MYDOMAIN" to your own domain name or this script will not work.

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DTjRuSzJlMkJUNFE/edit?usp=sharing

Monday, May 2, 2011

Check List for 64 BIT SQL Server 2008 Cluster Installation

The purpose of this document is to help in the installation of SQL Server 2008 with tips and guidelines that I have found to work well in my environment. During installation I found missing information that was important for the successful installation of SQL Server 2008 (64 bit) on a Windows 2008 (64 bit) operating system in a cluster.

Pre Installation

Before installing SQL Server 2008 (64 bit) onto a Windows 2008 operating system there are several tasks to perform. Listed below are these tasks.
Failover Cluster Administrator Validation
In the Failover Cluster Administrator application you will find a "Validate Cluster" option. Click on it to execute a test that will verify that the cluster, on the operating system level, is working properly. If this test fails do not attempt to install SQL Server. This validation will produce a report that shows the status of the cluster. If the tests are successful, you may continue with the rest of the pre-installation checks.
Create a Resource Cluster Disk for MSDTC
The Microsoft Distributed Transaction Coordinator (MSDTC) installation requires that a dedicated disk be created on the SAN and/or iSCSI disk. The size does not have to be very large, and 1GB should be more than enough. When setting up the MSDTC you will point to this disk you created for its install. This is done within the "Failover Cluster Management" application. Note that MSDTC installation will need its own IP address. Do not give it the IP address of a NIC in use.
Create a Local Disk for Page File
Create a dedicated disk on the locally attached storage unit that will contain the operating systems paging files. See System Performance Settings for more information on how to assign the paging disk to this dedicated disk.
Disable unused NIC
Check to see the server has multiple network interface cards (NICs) and disable any extra NICs. For example if you are using an HP server and have multiple NICs you can "team" them together. Basically creating one virtual NIC. This virtual NIC is not liked by the SQL Server installation, which will complain and fail. You will need to "unteam" the NICs via the HP software and then you can disable or assign another IP address to them. If you assign them to a different IP (only use static IPs and not DHCP), you will have to disable the IP_V6 option (unless you are actually using IP_V6) and ensure that they are set to a different subnet. If not your install will fail. Once you have a successful installation of SQL Server you can re enable and/or re "team" the NICs.
Binding Order of Network Interface Cards
Ensure that the binding order for the NICs are correct as the SQL Server installation will report a warning if not configured properly. See Microsoft article 955963 for more details. When installing SQL Server you may get a warning about the binding order, this only applies to clusters with multiple public NICs. If you have only 1 NIC that is public and have a second NIC that is disabled, then you will get this warning. Do not worry about it and continue with the SQL Server installation. If you have several public facing NICs that you will have to ensure that the primary NIC is first in the binding order. Below are the step to take do view the binding order.
  • Click on "Start" the right click on "Network" and click on "Properties"
  • In new window that opened, locate the "Manage network connections" on the left side of the window and click on it
  • Second window will now open and from the menu (at top) click on "Advanced" and then on "Advanced Setting" and make your changes as needed
Disable the Power Management in the NIC advance setting, uncheck the "Turn off power..." so that the NIC cards do not shut down. If a power down occurs, it may fail your cluster over to the next node. This includes the NICs on the public and private networks. Not sure why you would need power management on a server's NICs as they use little power anyway. Generally I do not use power save management on any of our production servers.

Antivirus Software
Uninstall any and all antivirus software from all nodes or better yet do not install on any nodes. The antivirus software will cause many problems with your cluster not just for installation but also for the operation of SQL server. If you need to use an antivirus software, install it onto another server and have that server scan your cluster. Search BOL (SQL 2008 October 2009) for "Before Installing Failover Cluster" and Microsoft article 250355 for more details.
Firewall Usage
It is best to turn off the firewall that comes with the operating system and use your company's firewall. If you must use a firewall on the nodes, then review the Microsoft document in Books On Line (BOL): Configuring the Windows Firewall to Allow SQL Server Access. Once you have turned off the operating system firewall you will need to ensure that the firewall service is disabled or the operating system may turn it back on with warning.
Recycle Bin
In a cluster environment where you are using large disks, you should either disable the recycle bin or change the size of the disk that will be used for this function. If you use the second option and do not resize it you will find that a large portion of your disk (this could be in the gigabytes or terabytes) is now reserved for the recycle bin and not your data. By default the operating system will allocate 10 to 15 percent of the disk for this function. Personally I disable the recycle bin for all the disks assigned to the cluster. To disable this function perform the following:
  • Right click on the "Recycle Bin" icon. A window will open
  • Click on the "Do not move files to the Recycle Bin. Remove files immediately when deleted"
System Performance Settings
To help in the performance of your cluster you should disable the visual effect. This is done by the following:
  • Click on "Start" then right click on "Computer" and finally click on "Properties"
  • Once the new window opens, click on "Advanced system settings" located on the left side of this window. A warning will appear so you must click on the "Continue" button and the following window will now appear
  • Here you will click on the "Settings..." button located in the "Performance" section. A new window will appear and then click on "Adjust for best performance" as seen here
  • Next click on the "Advanced" table and select the "Background services" which will optimize the system for best performance of background service.
  • Now to click on "Change..." button in the "Virtual memory" section
  • Uncheck the "Automatically manage paging file size for all drives" and select the "Custom size". Now enter a value that that just about fills the dedicated drive you have created to be your page file drive. If you have not created one, do so (ensuring that drive is on a local disk and not on the SAN or iSCSI attached disk) and then come back to make this change. By assigning a dedicate drive for paging, you remove pressures from the C: drive where the operating system is located.
Once you click the "OK" button, you will be asked to restart the computer.

Private Network Card "Heartbeat"
Check the Microsoft article 258750 "Recommended private "Heartbeat" configuration on a cluster server" as this has the best practice for its configuration. If your network administrator has build the Windows 2008 cluster for you, I recommend reviewing its setting before starting. Ensure that the NETBIOS is not used in the private (Heartbeat) NIC.
Location of SQL Installation Files
Copy the SQL installation and service patch files to a temporary location on the C: drive, which is a local disk and not on the SAN and/or iSCSI disk. (or execute from the CD/DVD itself). From there you can now start the SQL server installation process. If you copy these installation files to a resource disk of the cluster you may have issues during the installation. Copy these file to both C: drives or local drives on all nodes. Once cluster is complete you can delete all the installation files to save on disk space. You will need to execute the setup program on each node. Install from the first node and once completed you will have a 1 node SQL cluster. Next execute the setup program from the second node where you will "add a node" to your cluster. Or you can use the advance installation that will do both nodes, but I have not used this option. More on this can be found in BOL 2008.
During the installation you will notice that SQL server performs its own checks. If there are any errors/failures on the report stop the installation process and correct the problem. SQL server setup will not let you continue if you have errors but will let you continue of you receive warnings.
For the SQL server installation I recommend the Microsoft technical article called " SQL Server 2008 Failover Clustering"

I hope you will find this document useful in your installation and welcome any additional items that I may have missed. With the above recommendations we have successfully installed several 64 BIT Windows 2008 clusters with SQL Server 2008 clustered.

Thanks you,
Rudy