Monday, July 16, 2012

Configure SQLServer to listen on a Static port or Multiple Static Ports

Dynamic port is used by named instance only. By default the named instance listens on Dynamic port.  When you start the SQL server for the first time, it requests for port from O/S and it is allocated to SQL Server. This port information is written into the windows registry.  
Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program then SQL Server chooses another port at the time of restart.
You will receive “Error ID 10048” If the port is used by another program.
Static Port, If you configure an SQLServer instance to use a static port then you should restart the instance to take effect of changes and all clients must send their request to static port
By default, the default instance listens on fixed TCP Port 1433. The default instance of SQLServer does not support dynamic port allocation, but can also make the default instance to listen on multiple static ports.  The named instances of SQL Server support allocation of both static and dynamic ports
Follow these steps to configure Static TCP Port
1.      Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch  “SQLServer Configuration Manager”
2.      Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties
3.      Select “IP Addresses Tab” à Go to “IPAll” section
4.      Remove TCP Dynamic ports and update TCP Port: <Static port>
For ex: - 5000
Restart the SQLServer to take effect of new Static port.
You can configure the SQLServer to listen on multiple Static TCP Ports. Adding Additional ports to SQL Server will increase the performance of the SQL Server.
Follow these steps to configure additional TCP ports:
1.      Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch  “SQLServer Configuration Manager”
2.      Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties
3.      Select “IP Addresses Tab” à Go to “IPAll” section
4.      Enter the additional port separated by a comm for TCP ports
For ex:- TCP Port: 1234,5000
Verify the port configuration
1.      You should be able to see the new port information in error.log file

Server is listening on [ <IP Address> <Port Number>].
For ex: - Server is listening on [ <10.10.10.10> <ipv4>  <5000>].

If you have configured multiple static TCP ports then you should see below information in error.log file
  Server is listening on [ <IP Address> <Port Number>].
  Server is listening on [ <IP Address> <Port Number>].

  For ex: - Server is listening on [ 10.10.10.10 <ipv4> 1234].
   Server is listening on [ 10.10.10.10 <ipv4> 5000].
2.      You can also check the updated port information in “regedit
Start Registery (regedt32.exe) and locate the following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
In case of 2005,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
If the allocated port is Static then you should see below values
TCPDynamicPorts = Blank
TCPPort = New Static port

If the allocated port is Dynamic then you should see below values
TCPDynamicPorts = Current used port
TCPPort = Current used port

To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance.  For ex:-  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\ MSSQL.X\MSSQL Server\SuperSocketNetLib \AdminConnection\Tcp

Where X indicates the number

Please note that it is not recommended that named instance listen on port 1433

Regards,
Satishbabu Gunukula

Tuesday, June 5, 2012

Create New Error Log or Recycle Error log in SQLServer

SQLServer - Every time you restart the SQLServer a new error log will be created. The old error will be renamed as ERRORLOG.1. In most of the production environment the SQLServer will be restarted rarely and you will notice a large ERRORLOG.

In order to recycle or create a new error log you no need to restart the SQLServer.
You can recycle the error log using below command.

EXEC sp_cycle_errorlog
GO

You can limit the number of error log files before they are recycled.
1. In Object Explorer, Click the plus sign to expand Managment.
2. Right-click on the SQLServer Logs folder and select Configure.
3. In the Configure SQL Server Error Logs dialog box, you can enter maximum number of Error logs.

SQLServer Agent also has an error and it will be recreated every time you restart SQLServer Agent. The new error log will be generated as SQLAGENT.OUT and old error log will be renamed as SQLAGENT.1...etc. The SQLServer Agent can maintain up to 9 SQLServer Agent Error logs.

You can recycle the SQLServer Agent Error log using below
EXEC sp_cycle_agent_errorlog
GO

It is always good practice to setup a job to recycle both SQLServer and SQLServer Agent error logs.

Following below steps you can reanme SQLServer Agent error log
1. In Object Explorer, Click the plus sign to expand SQL Server Agent.
2. Right-click on the Error Logs folder and select Configure.
3. In the Configure SQL Server Agent Error Logs dialog box, enter the new file path and file name and click OK

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com/

Tuesday, May 29, 2012

How to generate Cluster.log for Windows or SQLServer Cluster troubleshooting

If you are doing troubleshooting with your Windows/ SQLServer Cluster you need to generated Cluster.log

Use below command to generate the cluster.log
C :\> cluster /cluster:clustername log /gen

The logs will be generated on each cluster node under %WinDir%\Cluster\Reports folder.You need to login into each node in order to collect the stats.

You can use below command to generate the cluster.log for all nodes on the node where you are running the command.

C:\> cluster /cluster:clustername log /gen /copy:.

Using Powershell - PowerShell is a cluster scripting language.

For the Windows Server 2008 R2 you may access PowerShell management
Start à Administrator Tools à Windows PowerShell Modules.

You can load PowerShell with Failover Clustering in two ways:
1. Open “Failover Cluster PowerShell Management” from the shortcut in Administrative Tools
2. Run powershell.exe as administrator

You will find the powershell.exe under C:\Windows\System32\WindowsPowerShell\v1.0

You can also generate the cluster log using PowerShell.
Syntax:- Get-ClusterLog [-InputObject ] [[-Node] ] [-Cluster ] [-Destination ] [-TimeSpan ] []

Use below command to generate a log file for each node of the local cluster
Get-ClusterLog

Use below command to generate a log file for each node of the local cluster, and copies all logs to the local TEMP folder
Get-ClusterLog – denstination c:\Temp

Use below command to generate a log file for the local cluster in the cluster reports folder on each node of the cluster. The log covers the last 5 minutes
Get-ClusterLog -TimeSpan 5

You can refer below link for Failover Cluster Cmdlets in Windows PowerShell
http://technet.microsoft.com/en-us/library/ee461009.aspx

Windows PowerShell user guide
http://technet.microsoft.com/en-us/library/bb978526.aspx

Regards
Satishbabu Gunukula
http://sqlserver-expert.com/

Thursday, May 24, 2012

Index was outside the bounds of the array


I was trying to connect to SQLserver 2012 using SQLserver 2008 Management studio and received below error. I was able to connect and able to query.




I tried the same with SQL2005 Management studio and same error. I didn’t see useful information under Technical details.



Cause: Please note that old versions of SSMS (SQLServer Management Studio) are not forward compatible. Using 2012 SSMS you can connect to SQLServer 2005/2008 databases as it is backward compatible.


It looks like Microsoft will not fix the forward compatiable.


Solution: Upgrade all your client tools to highest version, and then you won't be missing out on any functionality or getting error messages.


Regards,

Satishbabu Gunukula

Tuesday, May 22, 2012

Manage Database files and file groups in SQLServer

SQLServer has three types of files

Primary – Primary data file contains the startup information of the database and also has information about other files in the database. The file extension is .mdf

Secondary – Secondary data file is used to store user data and these files are optional. The secondary files are used to spread data across multiple disks. The file extension is .ndf

Transaction log- Transaction log file holds the log information that is needed to recover the database. The file extension is .ldf

Every database has one primary file group and it is the best practice to create a user-defined file group for user data.

Create a user-defined file group:  Use below syntax to create user defined file group “USER_DATA”

USE master
GO
ALTER DATABASE TestDB
ADD FILEGROUP USER_DATA;
GO

ALTER DATABASE TestDB
ADD FILE
(
    NAME = USER_DATA1,
    FILENAME = ‘D: \DATA\user_data.ndf',
    SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB
),
TO FILEGROUP USER_DATA;
GO

You can also create a user-defined file group using Microsoft SQLServer Management Studio
1       . Right click on the database and choose Properties
2       . Go the FileGroups page, check on add and provide the file group name.
3       . Select the Default checkbox to make it as default file group

Modify User-defined/seconday file group as default:

USE master
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [USER_DATA] DEFAULT

Please note that all the user objects will create in default file group.

Resize the file or Modifying a file:  Use below syntax to increase the max size of the file

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
    (NAME = USER_DATA1, MAXSIZE = 2000MB);
GO

Add a data file to existing file group:  Many of the users will come across this If you primary data file drive is out of space. Create a new drive and add a new ndf file to the file group.

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'USER_DATA2', FILENAME = N'E:\SQLData\user_data2.ndf' , SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB) TO FILEGROUP [USER_DATA]
GO

Moving a file to a new location: You must physically move the file to the new disk/direcotry before running this syntax.

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
( NAME = USERd_ATA2,
    FILENAME = N’E:\SQLData\user_data2.ndf'
); GO

Make sure to stop and start the SQL Server or take the database OFFLINE and then ONLINE To implement the change.

Removing a file from database: Use following syntax to remove a file from database

USE master;
GO
ALTER DATABASE TestDB
REMOVE FILE USER_DATA2;
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com/