:::: MENU ::::

Posts Categorized / System Administration

  • Oct 23 / 2012
  • 0
dbDigger, Ports, System Administration

SQL Server ports configuration

Ports are a logical point of connection provided and managed at OS level. OS directs the data arrived at NIC for a specific port to its specified application. SQL Server ports are used at instance level and a port can not be shared among multiple instances on same machine. Before going in to detail of ports configuration for SQL Server level, we should have a look at categories of ports

IANA Reserved Ports

These ports range from 0 to 1023. The Internet Assigned Numbers Authority (IANA) allots these ports only to standard protocols. Vendors or users may not use any port within this range.

Vendor Reserved Ports

Ranges from 1024 to 49151. These ports are registered by IANA for applications of specific vendors. Other users are not able to use any port within this range. For SQL Server the default reserved port in 1433.

Open for Users Ports

Ranges from 49152 to 65535. Ports within this range may be used for end user purposes. If we want to use dynamic or static customized port number then it should fall in this range.

SQL Server Default Port

Default instances of SQL Server use port no 1433 by default. However named instances and express edition use dynamically allotted port by default. It is a good practice to change the default 1433 port no to user allotted static port.

Port Types in SQL Server

For SQL Server we may classify the ports in following heirarchy
  • Dynamic Port (fall in the range between 49152 to 65535)
  • Static Port
    • Default Port (1433)
    • User Allotted (Any port between 49152 to 65535)

Choosing Between Static and Dynamic Port

Dynamic ports serve the purpose of simplicity and to use the ports other than default 1433 but there are scenarios when you have to allot a specified static port to the SQL Server instance. Especially while working with firewalls we have to exclude the ports explicitly from firewall rules. In such cases it is required to use a static port rather than a dynamic new port each time with SQL Server service start.

Can we use more than one port for single instance?

The answer to this major question is “YES”. We may configure a SQL Server instance to listen on multiple ports at a time. To learn and implement¬† this functionality read this article by Jugal Shah.

Here is a very good post about the ports that a DBA should have essential knowledge of. It contains the ports for all SQL Server endpoints and services.
And also a very good point added by Yusuf Anis
“And for those who still have pre win 2k8 boxes with them the dynamic port range is limited to 1024-5000 on both TCP & UDP.”

  • Feb 24 / 2012
  • 0
Backup and Restore, dbDigger, SQL Server Error messages, SQL Server IO, System Administration, Windows Server

Database backup and restore failed on mapped network drive

During my job at National Database and Registration Authority of Pakistan (NADRA), i was accustomed to create database backups directly on mapped network drives. We have a domain configured at NSRCs with SQL Server 2000. Fore servers with single disk, it becomes very efficient by preventing the local server disk IO. Now while working with SQL Server 2005 installed on windows server 2003 work group. i was required to create an adhoc backup during peak hours. For optimized IO i tried to use mapped network drive for creating backup on it directly. But surprisingly mapped network drive was neither available in SSMS backup location explorer nor through T-SQL backup statement. Error message was being generated.

Database backup and restore on mapped network drive

Same statement was working successfully for any location other than mapped network drive. Keeping in view my previous experience with SQL Server 2000 on domain i was not expecting any problem in this task. However after some time i realized that network drive is not available as backup device.

Next option was BOL and Google. There i came to know that mapped network drives are available for backups only when your SQL Server service is running under domain account that has access to mapped network drive.


Without a privileged domain account as start up account of SQL Server service, mapped network drives cannot be accessed from within SQL Server context for backup or restore process.

  • Feb 22 / 2012
  • 0
dbDigger, Deployment, SQL Server Error messages, System Administration, Windows Server

Role management tool to install or configure .Net framework on Windows Server 2008

While installing SQL Server 2008 on Windows Server 2008 R2, i got following error message at initial stage in process.

Error message in SQL Server 2008 setup related to Role management tool

Windows was up to date and required version of .Net framework was installed on it. Opened Server Manager for Windows Server 2008 and clicked on features in left panel. Then by clicking Add Features link in right panel another panel was opened with several features available there for installation. I checked the box for my required .Net framework feature and Clicked on Next button to proceed with the installation.

Service Manager in Windows Server 2008 for .Net Framework features

The .Net feature was installed and SQL Server 2008 installation ended successfully.

  • Feb 21 / 2012
  • 0
dbDigger, SQL Server Services, System Administration, Windows Server

Not able to connect SQL Server 2008 on network

Recently i deployed SQL Server 2008 along with SP3 on Windows Server 2008 R2. All installation and later configuration were managed through Remote Desktop. Later while trying to connect the instance on LAN from my desktop PC i got following error message

Not able to connect SQL Server 2008 on network

The generated error message is related to network access of instance. Back through remote desktop, first of all i checked for firewall issues but every thing was fine for that. Then after little exploration of SQL Server configuration manager i observed that TCP is disabled. I enabled the TCP in configuration manager as shown in snap

Enable TCP IP in SQL Server configuration Manager

A warning box appeared suggesting the SQL Server service restart for changes to take effect.

SQL Server Service restart required after enabing TCP IP

I restarted the service from configuration manager and again tried login from desktop PC on LAN. This time login was successful without any error message.
So make sure to enable TCP-IP for accessing the SQL Server 2008 instance on LAN. TCP-IP is by default disabled in SQL Server 2008 installation.

  • Dec 13 / 2011
  • 0
dbDigger, Memory management, SQL Server Training, System Administration, Windows Server

A broad comparison of PAE and AWE memory extensions

Physical address extension (PAE) and Address windowing extensions (AWE) both are used to address more than 4GB memory on 32 bit systems. I define both in following lines to remove the confusion regarding this concept

Physical address extension (PAE)

PAE is used by any OS to extend more than 4 GB ram for 32 bit systems. 32 bit systems natively cannot utilize more than 4GB of physical memory. As most of the server hardware is with much more memory than 4GB, it is required to make it accessible for 32 bit operating systems. For windows platform PAE is enabled by adding some parameter in boot.ini file.

Address windowing extensions (AWE)

AWE is a windows API used for supporting 32 bit applications to access more than 4GB of memory. It may be required for memory extensive software and applications. Read about enabling PAE for SQL Server.

  • Dec 06 / 2011
  • 0
dbDigger, Hardware and Platform, System Administration, Windows 7

How to uninstall an SQL Server instance out of multiple instances

While writing an article for MSSQLTips.com, i installed SQL Server 2005 Dev Edition and SQL Server 2005 Express Edition on Windows 7. Dev Edition was with default instance name while Express Edition was named as SQLExpress on machine. After completion of article as a clean up process, it was required to uninstall the express edition.

During this process i noticed that for new DBAs uninstalling an instance out of multiple instances may be confusing if not aware of process. In add/remove programs utility, SQL Server instances are embedded in their versions rather than separate entry for each instance. SQL Server 2005 instances are included under Microsoft SQL Server 2005 entry while SQL Server 2008 instances are included under Microsoft SQL Server 2008 entry.

Uninstall SQL Server 2005 Express edition

So here is how i uninstalled express edition and leaving the Dev edition only in SQL Server 2005 family.

  • Click on Microsoft SQL Server 2005 entry
  • On generated frame select SQL Server 2005 express instance to uninstall
  • Make sure to leave the common components unchecked

Uninstall SQL Server 2005 Express edition from Windows

Now click the Next button to continue with uninstall of selected instance of SQL Server 2005.

Consult us to explore the Databases. Contact us