Automatically calculate the MAX memory setting for a dedicated SQL server installation

Hello everyone,

NOTE: Updated scripts below as of March 1, 2015

I have created the following script to calculate MAX memory setting on a dedicated SQL server.
The script will show total memory, MAX memory in use, MIN memory in use, New calculated MAX and MIN memory setting.

There are 2 versions of this script because of how to detect the total memory of the SQL Server.

MAX Memory script for SQL Server 2005 to 2008R2

MAX Memory script for SQL Server 2012 and higher

Enjoy!

Rudy

Windows Core and SQL Server

Installing SQL Server 2008R2 on a Windows 2008R2 Core Server

With Windows Core you are getting a more secure operating system as it only installs the basics necessary for a Window Server to get started. You will have to add functions/roles/application(s) in order to setup your database environment. Because of this basic setup you do not get a GUI, “Start” button, Internet Explorer or even Windows Explorer. All you get is a command line window.

So let’s start. First you need to install Windows Core. When you insert your DVD or run the ISO you execute the setup.exe program. When then menu window opens you are asked what edition of Windows you want to install. Here you select ”Windows Core” and follow the prompts. This article will not go through the Windows Core installation as there other documents from Microsoft and this site on how to perform this setup. Here is the Microsoft link for the Windows Core installation: http://technet.microsoft.com/en-us/library/cc753802(WS.10).aspx

Once you reboot and log in (by default the Administrator’s password is blank and you are asked to change it) you will see a command window like the image above.

Now what do you do? Well you can run commands like netsh, net, dir, etc. However there is an easier way to help configure your new server. You can execute an application called “sconfig” from the command line. This application comes with the installation of Windows Core. When executed, a new window will open.

I have removed my information from the image as this is not important for this document. The first thing to do is to add your new server to the domain. With the "sconfig" tool executed you add your server to the domain by press number “1” and follow the prompts which guide you through the process. If you are not sure how this is done have your Network Administrator work along with you to complete this step.

Once you reboot your Windows Core server you can get back to this menu again by typing “sconfig”. **Make sure you log back into your server with an ID that has domain admin writes.

The next step is to go through the rest of the setting and adjust as needed. You have two options here. You can use the build in tool called “sconfig” or a free tool called "Core Configurator".

If you feel more comfortable with a GUI interface to setup these functions/features, use the "Core Configurator" tool which can be found on the “CodePlex” website here http://coreconfig.codeplex.com/ so just go to the site and download it. Once downloaded you need to copy the file(s) from your PC hard drive on your Windows Core server. This can be done by mapping to it. That is on your PC map a drive letter or you can use \\MYSERVERNAME\C$ where MYSERVERNAME is the name of your Windows CORE server.

Remotely connect to your Windows Core server and at the prompt change directory to this new folder (cd\yourfoldername) where you have copied the files to and execute the application by typing “Start_CoreConfig.wsf”. If you don’t see this file you may have to extract the .ZIP or .ISO file you downloaded. Once you execute you will see the following GUI application. With either tool you should configure the following:

• Firewall settings
• Allow Server Manager Remote Management
• Enable powershell
• Download and install updates.

Select each section and once again follow the prompts. Again if you need more help talk to your Network Administrator or view more documents at http://technet.microsoft.com/en-us/library/cc753802(WS.10).aspx

Here you see a much nicer interface to use. To make changes, like the ones listed earlier in this article, you can just click on the “Control panel” button and select what options you want to configure. Read each section and follow the prompts.

The next step is to install the .NET 3.5 features as it is a requirement of SQL 2008R2 Server. To install .NET feature click on “Computer setting” you will see the following. See Image 4.

Finally click on “Add or Remove Roles”. There are many roles and features you can install but .NET is what you will need to get the SQL Server installation going. Scroll down the list until you see “NetFx3-ServerCore”, select it and click on apply. Once completed, you will be ready to install SQL server.

Now you are ready to install SQL Server. Either copy the SQL Server installation files to a new folder on the C: drive of the Windows Core server or run it from you DVD drive. Make your way to the folder that contains the “setup.exe” an execute it. A familiar GUI will appear and you perform your installation just was you would on any other server. This document will not be covering the SQL Server installation If you have not performed a SQL Server install before, you can find many documents and help on this site and read the Microsoft document here: http://msdn.microsoft.com/en-us/library/ms143219.aspx

After your SQL Server installation you can use the SQL Server Management Studios (SSMS), right? No, there is no GUI on the Windows Core server. So from your desktop you would use the SSMS tool to finish off your installation.

In your SSMS tool, add your new SQL server by using the “New Server Registration” and enter your server name and save the addition. You will not be able to connect at this point. You will need to right click on the new SQL server name and select ”SQL Server Configuration Manager”.

Select the Client protocol you need and then start all your SQL Server service. Once all SQL related services are running you can now go back to SSMS and connect to your new SQL server installation and perform any other task you need.

Hope this helps in your installation of SQL Server on to a Windows Core Server. If you need more information you can search the Microsoft web site.

Using SQL Server to collect information from your Oracle server

How many DBA's out there have to manage Microsoft SQL Server instances and Oracle database servers? More than you think, and I am one of them. Just over a year ago one of our departments wanted to purchase a very expensive product that only runs on an Oracle database. When the software was purchased; 2 Oracle DBAs were also hired. Then it started, and we were all asked to cross train each other on the two database systems. On the SQL Server side I had created several reports to provide information on all our SQL Servers via a linked server connection. I decided to do the same and collect basically the same information from the Oracle servers. Here is a list of data I wanted to collect from Oracle. Job Status Data Current Error Information Database ONLINE Status Patch Data Patch Set Data To perform this task I first had to install the Oracle client (in this case it was for Oracle 10g) on the SQL server that will create the linked connection to the Oracle server. Without the client installed the link server parameter '@provider=' would not work as the Microsoft provided Oracle driver does not work properly. Even Microsoft recommends using the Oracle client. So make sure you install the Oracle client. If you are using a 64bit server just install the 32bit client as the 64 bit client may cause issues. I never did find out why this is the case. Once installed you can open up the SQL Server Management Studio (SSMS) and create a link connection from your SQL server to your Oracle server. This link can be created by the GUI (see Books On Line) or with the script below. Note: You must replace 'ORACLESRV1' with the name of your Oracle server. Also you need to change the user name 'REPORTS_USER' and provide its password 'PASSWORD'. Once complete you can execute this code. The user and password are the credentials you have created on the Oracle server. You can download the script here: https://docs.google.com/file/d/0B07PMQYOhF2DWmVIOVducTc1Yjg/edit?usp=sharing Ensure that the login has the rights to the VIEWs on SYSMAN.MGMT$*   PRINT 'Creating Oracle link connection...'PRINT ' 'EXEC master.dbo.sp_addlinkedserver @server = N'ORACLESRV1', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORACLESP'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLESRV1', @useself=N'False', @locallogin=NULL, @rmtuser=N'REPORTS_USER', @rmtpassword='PASSWORD' EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'collation compatible', @optvalue=N'true'EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'data access', @optvalue=N'true'EXEC master.dbo.sp_serveroption @server=N'ORACLESRV1', @optname=N'use remote collation', @optvalue=N'true' Now that you have created a linked connection to the Oracle server you can test this connection with the following script. It performs a SELECT against a system view on the Oracle server.   SELECT [JOB_NAME], [JOB_OWNER], [JOB_TYPE], [SCHEDULED_TIME], [START_TIME], [END_TIME], [STATUS], [TARGET_NAME], [TARGET_TYPE] FROM ORACLESRV1..[SYSMAN].[MGMT$JOB_EXECUTION_HISTORY] The information displayed is the execution history for jobs on your Oracle server. Neat! By querying other system views you can get a lot of information that is also displayed in the Oracle Enterprise Manager Web interface. Now that your tests are good you should delete the linked connection with the script below.   IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ORACLESRV1')EXEC master.dbo.sp_dropserver @server=N'ORACLESRV1',@droplogins='droplogins' This code will detect if the linked connection is there and delete it. There you have it. You can now see and collect information regarding your Oracle server without using any Oracle tools. You can now collect the related information and display it in SQL Server Report Server (SSRS). The script provided will perform the following: Creates a database called: ORACLEREPORTS Creates permanent tables: tblOracleJobStats - Job statistics, tblOracleCurrErrors - Shows errors, tblOracleOnlinStatus - Show if databases are online, tblOraclePatches and tblOraclePatchSets - Show level of patches installed Creates a stored procedure called: sp_dba_Oracle_Status - This stored procedure will create a new link connection to your Oracle server, perform a TRUNCATE on your tables and then repopulated with several SELECT statements. You can add this stored procedure to a job to collect your data whenever you like. I run this on an hourly basis. Once the script has executed you can just run the stored procedure to collect your information. Also you can create simple reports in SSRS to display the data. The completed SQL script is located in the Resources section below with additional notes within it. Thanks, Rudy

Query your Active Directory from with SQL server

Here is a script I wrote to query active directory. Because I needed to get some information on users that had a login in SQL server using Windows Authentication.

Remember to change the script so that your domain name used.

Thanks,

Rudy

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

Show SQL authentication login information

The script provide will allow you to query the Active Directory (AD) for information related to users that are connecting to SQL server with Windows Authentication.

Copy and paste this code and execute

Here is a list of information at this displayed.

Bad Password Count
Bad Password Time
Days Until Expiration
Default Database
Default Language
History Length
Is Expired
Is Locked
Is Must Change
Lockout Time
Password Hash
Password Last Set Time
User name

Hope you find it useful.

Thanks,

Rudy

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

Microsoft SQL Server Error Log Scanning

SQL Server has an abundance of information in the ERROR.LOG file, from restart information to errors that are logged. Reviewing the ERROR.LOG can be a long and time consuming task especially if you are managing multiple SQL Server installations. However you usually need to see only the error information and not the informational entries. This article will help you with tracking the important information from the errorlog.
To accomplish this task here is what I've done to help.

1. Create a database and table to hold all the data from current ERROR.LOG
2. Truncate table and upload the current ERROR.LOG
3. Execute T-SQL script to analyze the data and produce an output with only error type information

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

This article will walk you through these steps.

First we need to create a database and a table to hold the error log data

-- Create a database
USE [master]
GOCREATE DATABASE [ErrorLogStorage] ON PRIMARY ( NAME = N'SQLSystemsSupport', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON ( NAME = N'SQLSystemsSupport_log', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport_log.ldf' , SIZE = 64128KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- Create a table
USE [ErrorLogStorage]
GOCREATE TABLE [dbo].[ErrLogData](
 [LogID] [int] IDENTITY(1,1) NOT NULL,
 [LogDate] [datetime] NULL,
 [ProcessInfo] [nvarchar](50) NULL,
 [LogText] [nvarchar](4000) NULL,
 [SQLServerName] [nvarchar](150) NULL,
PRIMARY KEY CLUSTERED (
 [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Next we will clear the table and insert the ERROR.LOG data into the table. The script below will also add the SQL Server's name to the table. This is done so that you can have data from many SQL Servers inserted into the same table.

USE [ErrorLogStorage]
GO
-- Remove older data 
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
 BEGIN
 TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData] 
 END
 DECLARE @SQLCmd VARCHAR(1024)
 SELECT @SQLCmd = 'Insert Into [ErrorLogStorage].dbo.ErrLogData (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog '
 EXEC (@SQLCmd)
-- Cycle through the ErrLogData table and insert the server's name
DECLARE SrvName_Cursor CURSOR FOR
 SELECT [SQLServerName] FROM [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL
 OPEN SrvName_Cursor
 FETCH NEXT FROM SrvName_Cursor
 WHILE @@FETCH_STATUS = 0
 BEGIN
 UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername
 FETCH NEXT FROM SrvName_Cursor
 END
CLOSE SrvName_CursorDEALLOCATE SrvName_Cursor
GO

Now we can analyze the data collected with a query. Modify the WHERE clause to show more or less data.

SELECT [LogID]
 ,[LogDate]
 ,[ProcessInfo]
 ,[LogText]
 ,[SQLServerName]
FROM [ErrorLogStorage].[dbo].[ErrLogData]
WHERE ([logtext] NOT LIKE '% found 0 errors %'
 AND [logtext] NOT LIKE '%\ERRORLOG%'
 AND [logtext] NOT LIKE '%Attempting to cycle errorlog%'
 AND [logtext] NOT LIKE '%Errorlog has been reinitialized.%'
 AND [logtext] NOT LIKE '%found 0 errors and repaired 0 errors.%'
 AND [logtext] NOT LIKE '%without errors%'
 AND [logtext] NOT LIKE '%This is an informational message%'
 AND [logtext] NOT LIKE '%Setting database option ANSI_WARNINGS%'
 AND [logtext] NOT LIKE '%Error: 15457, Severity: 0, State: 1%'
 AND [logtext] NOT LIKE '%finished without errors%')
ORDER BY [SQLServerName] ,[LogID]
GO

The line '%Error: 15457, Severity: 0, State: 1%' does not actually indicate an error but is more informational, which is why I do not show this data.

Here is a sample output from the script above.

25021 2010-09-01 12:01:23' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25022 2010-09-01 12:01:25' spid12 REVOKE ALL ON [sp_dump_dtstasklog] FROM PUBLIC DEVSRV1
25023 2010-09-01 12:01:26' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25024 2010-09-01 12:01:28' Logon Error: 18401, Severity: 14, State: 1. DEVSRV1
25025 2010-09-01 12:01:29' Logon Login failed for user 'DBATester'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.10.6.215] DEVSRV1

There are many ways to look for specific information in the error log and you can customize the script to show specific information. Personally, I have created a scheduled job to automatically populate the table with error logs of all my SQL servers, which helps when I need to review an issue that effects other SQL Servers. With the error information in a table you can also create a report in SSRS to show this data in a nicer format.

I hope this helps in your ability to review the ERROR.LOG file for your SQL servers.

Thanks,
Rudy

Identify SQL Server's Port Number

Just copy and paste the T-SQL code and execute to get the port number used by SQL server.
This is how it works.

•Determine which version of SQL server you are running
•Look at the specific location in the registery
•Display the port number

I have tested this script on the following versions
•SQL 2000 - 32 bit
•SQL 2005 - 32 and 64 bit
•SQL 2008 - 32 and 64 bit
•SQL 2008R2 - 32 and 64 bit

It is quick, simple and could be executed on develoopment and production servers as there
are not changes being made to SQL server and/or the Window's registery.
You will need to ensure you have rights to read the register in order for the script to
execute properly and must be executed on each instance (this version) to get the port number)

Thanks,

Rudy

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

Rebuild SQL Server Master Databases in Less than 5 Minutes

Rebuild SQL Server Master Databases in Less than 5 Minutes

Have you ever has an issues with a SQL server (2000, 2005, 2008 and so on) that you needed to rebuilt the master database? I've had that problem not too long ago and had to spend some time researching how this can be done. Here is a good site with the information you will need to rebuild the master database (http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx). But with a little work you can rebuild the master and the other system databases in less than 5 minutes. Let's see how this can be done.

It's All Proactive

To quickly rebuild the master and system database you need to have done some proactive work before you need to do a rebuild. Here is a quick and easy list of task to do proactively.

1. Stop all SQL server services
2. Copy the master.mdf and master.ldf files from the SQL server's data folder to another folder
3. Restart all SQL server services

Now you have performed all you proactive tasks. A backup of these files exists in their native format. This is not a backup done via SQL server but a command line level or file level backup.

Time to Rebuild

So now what? Now when a master database has to be rebuilt due to corruption of some kind you can quick get back to a good state by performing the following.

1. Stop all SQL server services
2. Rename the master.mdf to master.mdf.old and master.ldf to master.ldf.old
3. Copy the master.mdf and master.ldf files from your folder to the SQL server's data folder
4. Restart all SQL server services
5. Log into SQL server to ensure you can connect
6. Perform a restore of the master database from your backups

At this point your SQL server will start up as normal and you can then continue to perform your additional tasks as needed.

Real World Example

Here is my real world example. At a disaster recovery test I had to build a SQL server 2008 environment and restore all of the SQL server's databases. This included all user and system databases. After the initial installation of SQL server 2008 I copied the master database files to a new folder I called 'D:\OrgFileBackup'. Then I performed the restore of the master database from our tape backups. During the restore an error occurred and the master database became corrupted. I could no longer log into SQL server nor restore any database. My only choice was to rebuild the master database. I performed the steps above and was able to connect to SQL server and perform my restores.
If I had to rebuild the master database or worse uninstall SQL server and reinstall SQL server completely the restore time for the whole environment would have taken a lot longer.

Rebuild Completed

This process is not intended to replace your backups but to enhance it. Now that you can log into SQL server you can perform a restore of the master database. You must restore from backups to get all the latest information back into the master database.
This process will not cure everything, but it is a faster way to rebuild your master database.

Hope you find this helpful,

Rudy

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

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

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

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

How to Connect to a SQL (2005, 2008 and R2) Server When You Are Completely Locked Out

Think of this scenario.

• You have forgotten (or do not have) the "sa" password
• The builtin\administrators account has been removed for security reasons
• You cannot connect to the SQL Server instance

Now what do you do? Let us see what you can do.
First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.
If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo.
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.
The service window should look like this once completed. See Image 2. Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.
Finally start up all your SQL Server services again and login. Hopefully you will find this article useful, actually I hope you'll never have to use it.

Thanks,
Rudy Panigas

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo.
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.

Collect SQL Server/Process Information - Quickly

If you need to get information out of your SQL server (2005 or 2008) quickly, execute the stored procedure and you will get a detailed report for the following areas

- Buffer statistics  - Memory statistics - General statistics - Locks statistics/Totals for Locks statistics
- TempDB statistics - Totals of Database statistics  - Plan Cache statistics - Transactions statistics
- SQL error statistics - SQL statistics (SQL compilations/recompilations) - Wait statistics - SQL  Execution statistics

This script displays data from the dynamic management  view called: sys.dm_os_performance and presents it in a report. I recommend that you look closer to this view for all your statistical needs.
If you find other information that you want to include, just modify this script to reflect the additional information.

Hope you find this script useful.

Rudy

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

DAC - What to Execute when Connected?

If your SQL server is in trouble and you cannot connect then the next option is to connect with the Dedicated Administrators Connection (DAC).

Once connected, you can collect some information on what is happening. Since you are under pressure, it would be nice to have a stored procedure to execute that displays information? Below is my script that does just that.

You want to execute the code below in the MASTER database to create the sp_dba_DAC stored procedure. We do this in MASTER because you generally have access to this database when the server is extremely busy.

Now open a CMD/DOS window and execute the following.

sqlcmd -A -S<server/instance name> -dmaster -E -Q"sp_dba_DAC" -oc:\dac-results.txt

Replace the <server/instance name> with your server or instance name.

Once you execute this command, a connection is made to the server/instance and the stored procedure "sp_dba_DAC" located in the MASTER database is executed. A file called "dac-results.txt" is created in the root of C: and the connection is terminated.

Open the "dac-results.txt" file with notepad (or any other editor) and you can quickly see the information.

Below is what is collected:

Shows SQL Servers information
Shows top 5 high cpu used statemants
Shows who so logged in
Shows long running cursors
Shows idle sessions that have open transactions
Shows free space in tempdb database
Shows total disk allocated to tempdb database
Show active jobs
Shows clients connected
Shows running batch
Shows currently blocked requests
Shows last backup dates
Shows jobs that are still executing
Shows failed MS SQL jobs report
Shows disabled jobs
Shows avail free DB space
Shows total DB size (.MDF+.LDF)
Show hard drive space available

Remember to create this stored procedure on your servers before you need to connect with DAC. Add additional stored procedure(s) or T-SQL code to provide even more information.

Thanks,

Rudy

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

Remove SQL Logins from All Databases and SQL Server

Have you ever had to delete a user from SQL server but are warned that the ID may still exist in a database? Or have you wanted to ensure that the account has been completed removed? Recently, a request came to me to remove many ids from several servers (SQL 2000 and SQL 2005) which via the EM/SSMS with would have taken some time. So I decided to make this job a little easier and more complete by writing a stored procedure that would help in this task.

The stored procedure will hunt for the user id specified in every database and then generate the T-SQL code used to actually delete the account. Below is an example.

Here I created a test id called "TestUser1"
To start, execute the code which will produce a stored procedure called: sp_dba_RemoveUser. Next supply the user account you want to delete and execute.
[sp_dba_RemoveUser] 'testuser1'

Finally the output is generated and you would copy and paste the results to the query window and execute.
Here is an example of the output generated.

-- *** Remove User/Login Tool ***
-- Version 1.2
--
-- This code will generate the t-sql code needed to remove a specific user from databases and
-- from SQL Server logins if needed.

-- ***********************************************************************************
-- *** Execute the following code to remove user from ALL DATABASES, if needed ***


USE master
GO
EXEC sp_dropuser [TestUser1]
GO

USE model
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServer
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServerTempDB
GO
EXEC sp_dropuser [TestUser1]
GO

-- ***********************************************************************************
-- *** Execute the following code to remove user from SQL Server login, if needed ***

IF EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'testuser1')
EXEC sp_droplogin testuser1

-- End of code

As you can see the T-SQL code goes to every database and drops the user's account. Once completed; it will drop the user from SQL server. Code is created if the user id exists in that database.

By using this method you can verify that the code generated is for the correct user and you can remove lines for database that you want to keep and/or you have to the option to delete the account for only the databases but keep the server login.

Hope this help you as it has helped me.

Thanks for reading

Upgrade SQL Server or not? The big questions for DBAs

Upgrading your SQL server has never been an easy question. Lately more and more DBAs have been asking why or why not to the upgrade question.

After attending the Dev Connection conference in Orlando FL, USA, the same question was asked over and over again. Here is a summary of what some experts are saying.

When thinking about upgrading SQL Server there are a few questions you need to ask yourself.

1. Is the current SQL server going out of support or required extra dollars to continue the support. For example within the next year or two, Microsoft will require extra money to support SQL 2005 as it is nearing end of life.
2. Does the newer version of SQL Server Denali (2011) or SQL 2008R2 have features that you need? If yes then you have your answer. If not, you still may want to upgrade to stay current.
3. Can you upgrade easily? If you are running SQL 7.0, 2000 or 2005 it will get harder to upgrade as newer versions become available. For example, if you are running SQL 7.0 and what to upgrade to SQL 2008R2, you can't directly. You need to upgrade to say version 2005 and then upgrade to 2008R2. If you are using SQL 7.0 or SQL 2000 you will need to revisit and most likely have to re write your DTS packages in SSIS (if you don't know what SSIS or DTS is then you should not upgrade!).
4. Does your company have time to do the testing once you have upgraded to ensure that your databases and applications are working correctly? There is a lot of testing required when you do an upgrade so make sure you and the development team have time to convert and test everything.

So upgrade or not? The answer is "It depends" Yes this is not the answer you wanted to read but it really does depend on all the points above. Once you start to look at each SQL server you want to upgrade you will see the questions above will help you decide.

Thanks for reading.