Reasons for changing the SQL Server port number and how to change it

In my last post, I have shared the information on how to specify the port number to connect to the SQL Server Instance but, now I would like to share some information on 

Why should we change the SQL Server port number? Where and How to change the SQL Server port?

Why should we change the SQL Server port number?

The SQL Server instance can have a static or dynamic port allocation.

By default when SQL server is installed , it installs the default instance, unless you provide a different instance name. So, if it’s a default instance then its given Static port 1433.

But if the SQL Server is a named instance then it will give a Dynamic port the SQL Server which may change when the SQL Services are restarted.

As the default port 1433 of SQL is well known , it’s the security best practice to change the port number to minimize the potential attacks. The other reason is if other application is using that particular port or the administrator can’t open the port then we need to change the SQL Server port number.

Where and how to change the SQL Server port?

To change the SQL Server port and to change the static to dynamic port allocation follow the steps below:

1. Open SQL Server Configuration Manager 

2.  Then click SQL Server Network Configuration

3.  On the right side, select the instance you want to configure to listen on a specific port. In the below screenshot I have just one instance so selecting that.

4.   To change the port, double click the instance and select the TCP/IP protocol  right click TCP/IP protocol and select Properties.

5. Click on the properties and once clicking the properties TCP/IP Properties dialog box appears, in that box click the IP Addresses tab.

 If we see the above screenshot, the TCP Dynamic Ports is not set to any value which means the database engine is listening on static ports. We can change this to Dynamic port by setting it to “0” in the TCP Dynamic Ports field.

If you see the TCP Port field it is set to 1433, this is where we need to change the SQL Server Port value. For example, if you want to change the value to 4141 then in place of 1433 change it to 4141 and click on OK.

6.  Once you set the new value select SQL Server Services in the SQL Server Configuration Manager and in that right click SQL Server and restart it to see the change.

How to specify a port number to connect to SQL Server Instance

How to connect to a different port with SQL Server Management Studio

Microsoft SQL Server Management Studio will connect by default to port 1433 and there is nowhere in the connect dialog box below to specify a different port from the default. So, if you want to connect using different port then specify the Server name or IP , a comma and then the port number.

Connect using a different port using a comma:

The connection dialog in the SQL Server Management Studio is shown below.

Normally, you would just type the server name into the field but, to specify a different port number use a comma after the server name then enter the port number.

          

The above example shows the “Rain-sql” as the server name and “port” where the port would be. 

If you want to connect to a server named Shine-sql on port 4321 then set the Server name to

Example: Shine-sql,4321

If you know the IP of the server then in place of the server name you can use the IP.

Example: 192.168.1.22,6789

For named instances, you usually connect by specifying the server name and instance name (in place of port number).

Example: Shine-sql, instance name

If you specify the port the named instance is using then there is no need to specify the instance name because each instance on a server uses a different port.

Date Time functions

Date and Time Functions are the part of SQL server System functions, They can be found under the programmability folder -->Functions--> System Functions -->Date and Time Functions

Date time in general represented as YYYY-MM-DD hh:mm:ss.nn

List function of Date Time functions

• Current_Timestamp : This is an ANSI function , that will get you he current date and time.It doesn't  take any arguments

Example:

Print CURRENT_TIMESTAMP

Jul 10 2017 12:30PM

       

• Dateadd()  : Dateadd function helps you to build a date and time, by adding interval value to the specified date part , interval number is signed number

               

syntax  DATEADD (datepart , number , date ) 

Example

print dateadd(dd,1,'07/10/2017')

print dateadd(dd,-1,'07/10/2017')

Jul 11 2017 12:00AM

Jul 9 2017 12:00AM 

• Datediff() : Datediff  functions helps you to find the difference between two given date times in the requested datepart format  

  syntax DATEDIFF ( datepart , startdate , enddate ) 

Example

print datediff(dd,'07/10/2017','07/11/2017')

print datediff(hh,'07/10/2017','07/11/2017')

1

24

• Datename(): Datename function helps you to print the name of the date part for the given datetime

syntax DATENAME ( datepart , date )

Example
print Datename (mm,'07/11/2017 23:00:03')

print Datename (yyyy,'07/11/2017 23:00:03')

print Datename (hh,'07/11/2017 23:00:03')

July

2017

23 

• Datepart() : Datename function helps you to print the number of the date part for the given datetime

 syntax DATEPART ( datepart , date ) 

Example
 print Datepart (mm,'07/11/2017 23:00:03')

print Datepart (yyyy,'07/11/2017 23:00:03')

print datepart (hh,'07/11/2017 23:00:03')

7

2017

23

• Day() : Day function returns you the no of day from the give date time input

syntax Day(Date)

Example
print Day ('07/10/2017')
10 

• Getdate() : Getdate function returns date and time off today

syntax Getdate()

Example

print getdate()

Jul 10 2017 3:38PM

•  Getutcdate() : Getutcdate function returns date time off today

syntax Getutcdate()

Example

select getdate() as Todays_date,Getutcdate() as Today_UTC

Todays_date                       Today_UTC

2017-07-10 15:47:50.500   2017-07-10 22:47:50.500

• Isdate(): Isdate function helps us to identify wheather the input values is date or not, if it is a date it would return 1

syntax Isdate(string)

Example

print isdate('07/10/2017')

print isdate('cat')

1

0

• Month() : Month function returns integer value of the month from the input date time provided

Syntax Month(date)

Example
print Month('07/10/2017')
7

• Sysdatetime(): Sysdatetime function returns date time of the time it was executed, it difference from getdate only to the level of granularity  

Syntax sysdatetime()

Example
print getdate()

print Sysdatetime()

Jul 10 2017 4:14PM

2017-07-10 16:14:24.8253614

• Sysdatetimeoffset(): Sysdatetimeoffset function returns date time of the time it was executed along with offset value

For example server in which we executing the command is in PST/PDT it would get -7 value where as in ESDT/EDT it would be -4


syntax Sysdatetimeoffset()

Example
print Sysdatetimeoffset()
2017-07-10 16:29:26.6988611 -07:00

• sysutcdatetime(): sysutcdatetime function returns sysdatetime in utc formate

syntax sysutcdatetime()

Example

print Sysutcdatetime()

2017-07-10 23:36:27.1041777
  

• Switchoffset() : Switchoffset function returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. 

syntax Switchoffset ( dattimeoffset, time_zone )

Example

print Switchoffset('07/10/2017 7:45:50.71345 -5:00', -7)

2017-07-10 12:38:50.7134500 -00:07

• Todatetimeoffset() : Function requires 2 input parameters first one is the date time with offset value second parameter is the new offset value which you would like the original to be changed too

syntax TODATETIMEOFFSET ( expression , time_zone )

Example
DECLARE @todaysDateTime datetime2;

SET @todaysDateTime = '2017-07-10 16:51:42.8066667 -04:00'

SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00');

2017-07-10 16:51:42.8066667 -07:00

• Year() : year function returns the year of the input date to the function

syntax Year(string)
Example

print year(getdate())
2017

SSIS Video's

Please find the SSIS videos below.

SSIS-1

SSIS-2

Isolation in SQL SERVER

ISOLATION: Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.

 Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

•        Types of Isolation

Ø  Read Uncommitted

Ø  Read Committed

Ø  Repeatable read

Ø  Serializable

Ø  SNAPSHOT

READ UNCOMMITTED: In this type of isolation database engines does not issue shared locks, so uncommitted data could be read resulting into dirty read. Also data could be read effected by other transaction while the current transaction is running resulting into repeatable.

READ COMMITTED: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so non-repeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.

REPEATABLE READ: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating non-repeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.

SERIALIZABLE: A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, non-repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.

SNAPSHOT: A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, non-repeatable reads and phantom reads.

	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	û	û	û
Read Committed	ü	û	û
Repeatable Read	ü	ü	û
Serializable	ü	ü	ü
Snapshot	ü	ü	ü

Example:

IF OBJECT_ID('Employee') is not null

BEGIN

DROP TABLE dbo.Employee

END

--Creating sample table

CREATE TABLE dbo.Employee(ID int,Name Varchar(50),Salary Int)

--Inserting data

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES ( 1,'Daniel',1000)

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES( 2,'Mike',2000)

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES( 3,'Frank',3000)  

SELECT * FROM dbo.Employee

SELECT Salary FROM dbo.Employee WHERE ID=1

Links for videos to see the different isolations Link1, Link2

This article refers to in depth information of Robert Sheldon article 

SQL Server Common errors

As a DBA we need to investigate the SQL errors from the error log. if you need info on finding your errorlog file location you can refer this article.

The below are some of the common sql server errors i have seen, may be this can help you to get some insights into this errors.

Error: 18210, Severity: 16, State: 1.

Causes:  Backup device not accessible or SQL Backup process expecting a reply, but could not get reply.

Observation: This can be a message when your third party backup software is not sending the info needed by SQL server to say that backup was successful. This error is random, so needs investigation from third party backup tools and logs to check the status of backup.

Error: 1205

Causes: Deadlock and victim has been killed.

Observation: Source: Locking, Blocking and transactions open

Error: A time-out occurred while waiting for buffer latch -- type 4

Causes:

https://support.microsoft.com/en-ca/kb/2545989

·        You enable the row versioning feature on a database in SQL Server 2008 R2 or in SQL Server 2008.

·        You configure the database to use the snapshot isolation level.

·        You run many transactions that update the database concurrently.

Observation:

essage:A time-out occurred while waiting for buffer latch -- type 4, bp 00000000E5FD39C0, page 1:90809119, stat 0xc0000b, database id: 7, allocation unit Id: 72057594492944384, task 0x0000000004FB9708 : 0, waittime 300, flags 0x1038, owning task 0x0000000000000000. Not continuing to wait.

Error: 18456, Severity: 14, State: 38.

Causes: Login failed due to wrong access issues

Observation: Usually observed when login is trying to access Database or perform action on restricted database.

SQL Server ErrorLog Location

Sometimes as a DBA we need to Invetsigate the SQL errors. SQL Server stores errors in error logs, which can be fined in no of ways.

Finding the location of log file.

Error log file location can be found in startup parameters of instance. You need to go to SQL Server configuration manager, select the SQL Server Services on Left pane and select the instance you are interested. Right click properties and select parameters, the parameter which start with -e is the location of your error log file for the selected instance.

The other ways to find out the error log file location is using xp_readerrorlog and from registry keys. The below is the T SQL can be used.

EXEC master.dbo.xp_readerrorlog 0, 1, 'Logging SQL Server messages in file'