Find TCPIP Port

Use below script to find TCPIP Port on which SQL Server is listening:



DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('ArunL')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME')) + '\MSSQLServer\SuperSocketNetLib\Tcp'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'

Advantage and disadvantage of Database Mirroring

Advantages Database Mirroring:

1. Database mirroring supports full-text catalogs.
2. Database Mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
3. Does not require special hardware (such as shared storage, heart-beat connection) and clusterware, thus potentially has lower infrastructure cost
4. Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
5. It has automatic server failover and client failover mechanism.
6. Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.

Disadvantages of Log Shipping:

1. Mirror server/database is not available for user operation.
2. Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
3. Automatic server failover may not be suitable for application using multiple databases.
4. It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.

Enable, Disable Trace Flag

Trace flags are required for temporary purpose to analyze server level characteristics or behaviors during a course of action. It helps the developers to troubleshoot the server. 


We can enable/ disable trace flag in many ways. We can Enable/disable through
1. Adding in startup parameter
2. Adding in Registery
3 DBCC TraceOn/TraceOff


Here I am describing TraceOn only


Enable Trace Flag
You can use DBCC TraceOn to enable trace flag for a single session so that it wont work in another session.


DBCC TraceOn(abcd)


You can also use DBCC TraceOn to enable trace flag globally so that it will be effective in all sessions.


DBCC TraceOn(abcd,-1)


Disable Trace Flag
You can use DBCC TraceOff to disable trace flag for a single session


DBCC TraceOff(abcd)


You can also use DBCC TraceOff to disable trace flag globally so that it will be effective in all sessions.


DBCC TraceOff(abcd,-1)


Check Trace Flag Status
To know the status of any trace flag whether its turned on you can use the below command

To check individual trace flag status use the below command

DBCC TraceStatus(abcd)

To check status of all trace flag use the below command

DBCC TraceStatus(-1)


NOTE: Here abcd is Trance Flag Number

Generate random Password

SQL Server is a robust back end for many application. Sometimes it is necessary to generate random password for Users in some application. Here is a stored procedure that generate random string based on some parameters:


CREATE PROC GenerateRandomPass
@Start TINYINT = 48,
@Range TINYINT = 74,
@ExChar VARCHAR(35) = '\/<=>0[]O:@;?^`',
@LenOfPass INT,
@Output VARCHAR(35) OUTPUT
AS
DECLARE @String CHAR
SET @Output = ''
    WHILE @LenOfPass > 0 
BEGIN
  SELECT @String = CHAR(ROUND(RAND()* @Range + @Start, 0))
  if CHARINDEX(@String, @ExChar) = 0 
  BEGIN
  SET @Output = @Output + @String
  SET @LenOfPassOfPass = @LenOfPassOfPass - 1
END
END
GO;


Here
@Start means starting ASCII char, Default is 48 which is 0.
@Range means how many ASCII chars to include. Default is 74 (48+74 = 122) which is z.
@ExChar means characters to exclude from output.
@LenOfPass means length of generated string.


Example
DECLARE @Password varchar(10)
-- All Upper Case Letters Excluding @ and ^
EXEC [dbo].GenerateRandomPass @Start=65, @Range=25, @ExChar='@^', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All Lower Case Letters Excluding 0 and l
EXEC [dbo].GenerateRandomPass @Start=97, @Range=25, @ExChar='0l', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All numbers between 0 and 9
EXEC [dbo].GenerateRandomPass @Start=48, @Range=9, @ExChar='', @LenOfPass=10, @Output=@Password OUT
SELECT @Password
-- All Values Between ASCII code 48 - 122 excluding defaults
EXEC [dbo].GenerateRandomPass @LenOfPass=10, @Output=@Password OUT
SELECT @Password

Incremental Backup

An Incremental Backup is nothing but a Differential Backup.  In sql server if anything modified after taking the full backup it will be recorded in page as 1(indication symbol) in differential change map(DCM). Those ones are never changed until u take the full backup again. So last differential backup enough for restore because it's having the all the increments since the last full backup.


An incremental backup contains all the changes since the last incremental backup. Incremental backups are additive - you have to restore all of them to get back to where you started. SQL Server does not support true incremental backups (I mean just pages which have changed since the last incremental). Incremental (transaction) backup is good up to the last incremental or full backup. 


Process to do a Differential Backup..


1. Right click on you DB in SSMS
2. click on "Tasks" then -> "Backup"
3. In the Backup Type select 1) Full or 2)Differential.
4. Configure all other settings like file location etc....


You can also do this in SSIS using a Backup Database Task Step.