What is End Point in Mirroring? How to create end point in Mirroring? How to Find Port number in Mirroring? How to change port in Mirroring?

 What is End Point in Mirroring?

An endpoint is a network protocol which is used to communicate over the network from one server to another server or we can say one SQL Server instance to another SQL Server Instance . End points will come with in-built security. 

End point runs on a port number, SQL Server supports different types of end points

(i)                  Db mirroring end points

(ii)                service broker end points

(iii)               SOAP end points

(iv)               TCP end points

By default the Database mirroring end points will run on the default port number 5022. 

The default End point Port number Are-

Publisher - 2022

Distributor - 2023

Subscriber - 2024

How to create end point in Mirroring? 

Create endpoint <endpoint name> State=started/stopped/disabled as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

How to Find Port number in Mirroring? 

SELECT name, port FROM sys.tcp_endpoints

How to change port in Mirroring?

ALTER ENDPOINT [Mirroring] AS TCP (listener_port = 5023)

Please comment below if missing anything -Jainendra Verma

How to create/configure replication in SQL Server and how to monitor Replication in SQL Server?

Replication configuration is very easy. Any one can do it in 10 Minutes

Please follow below Step by Step complete Replication configuration 

We will configure replication between following servers-

1.       INHYIZLP10744 < Publisher > Server 

2.       INHYIZLP10744\INSTANCE1 <Subscriber> Server

3.       Database name is – Replication_Test_DB (Already existing on both server)

4.       Table <Article> name – Table_1 (Table must have Primary Key)

Open SSMS and connect to your Publisher Server <INHYIZLP10744> and open Replication 

Right click on the Local Replication and select New Publication, Here we can create publication- Click Next à Select Distributor Server ( We are selecting same Publisher server for distributor server) à Next 

Next

Next

Next

Next

Select Database

Select Replication Type / publication type-

Select Articles <table, View, SP etc > - we can change Article properties also-

We can put filter or condition on Article. It is like where condition in query.

Next

After next click on security table and provide credential-

Next

Provide Publication Name

Next

You publication has been created in SSMS. Non Right Click on Created publication and click on New Subscription link -

Select you Publisher

Select the Agent location <Push or Pull>

Click on ADD Subscriber and select Select subscriber server

On Our demo INSATANCE1 is our subscriber --> Connect

Select database - <Replcation_test_DB> - We have created same database in subscriber OR you have to take full backup and restore it on Subscriber server<Instance1>

Click on below button for connection for Distributor and subscriber

Credential Window --> OK

Click Next (here you can schedule synchronisation agent )

Click Next (here you can schedule initial/first subscription )

Next

Next

Next (Your subscription created)

You can verify in you SSMS on Publisher Server

Now  All DML(Inster/update/delete) operation will be sync between  publisher server<INHYIZLP10744> to Subscriber Server<INHYIZLP10744\INSTANCE1> for Article (Table_1)

Now you can also monitor Replication, Just Right click on Publication and click on Lunch Replication Monider the below window will open and can see all live agent and history of each article and etc. 

I hope you will like my post. Please let me know and comment below- Jainendra Verma

SQL Server Clustering scenario based problems, Issues, Troubleshooting and Fixes.

Clustering scenario based problems, Issues, Troubleshooting and Fixes-

1.       Re-Validate a cluster Configuration: - Open Failover Cluster Management is selected and then, under Management, click Validate a Configuration.

           View report - %SystemRoot%\Cluster\Reports\Validation Report date and time.html

2.       Network is down.

Recovering from Failover Cluster Failure

Usually, failover cluster failure is to the result of one of two causes:

·         Hardware failure in one node of the two-node cluster. This hardware failure could be caused by a failure in the SCSI card or in the operating system.

To recover from this failure, remove the failed node from the failover cluster using the SQL Server Setup program, address the hardware failure with the computer offline, bring the machine back up, and then add the repaired node back to the failover cluster instance.

·         Operating system failure. In this case, the node is offline, but is not irretrievably broken.

To recover from an operating system failure, recover the node and test failover. If the SQL Server instance does not fail over properly, you must use the SQL Server Setup program to remove SQL Server from the failover cluster, make necessary repairs, bring the computer back up, and then add the repaired node back to the failover cluster instance.

Recovering from operating system failure this way can take time. If the operating system failure can be recovered easily, avoid using this technique.

Resolving Common Problems

The following list describes common usage issues and explains how to resolve them.

(a)       Problem: Incorrect use of command-prompt syntax to install SQL Server

Issue 1: It is difficult to diagnose Setup issues when using the /qn switch from the command prompt, as the /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files. For more information about log files, 
Resolution 1: Use the /qb switch instead of the /qn switch. If you use the /qb switch, the basic UI in each step will be displayed, including error messages.

(b)      Problem: SQL Server cannot log on to the network after it migrates to another node

Issue 1: SQL Server service accounts are unable to contact a domain controller
Resolution 1: Check your event logs for signs of networking issues such as adapter failures or DNS problems. Verify that you can ping your domain controller. (Open and check DNS manager also)

Issue 2: SQL Server service account passwords are not identical on all cluster nodes, or the node does not restart a SQL Server service that has migrated from a failed node.
Resolution 2: Change the SQL Server service account passwords using SQL Server Configuration Manager. If you do not, and you change the SQL Server service account passwords on one node, you must also change the passwords on all other nodes. SQL Server Configuration Manager does this automatically.

Issue 3: SQL Service account password expired.
Resolution 3: Change the password and update on each node for MSQLSERVER service.

(c)       Problem: SQL Server cannot access the cluster disks

Issue 1: Firmware or drivers are not updated on all nodes.
Resolution 1: Verify that all nodes are using correct firmware versions and same driver versions.

Issue 2: A node cannot recover cluster disks that have migrated from a failed node on a shared cluster disk with a different drive letter.
Resolution 2: Disk drive letters for the cluster disks must be the same on both servers. If they are not, review your original installation of the operating system and Microsoft Cluster Service (MSCS).

(d)      Problem: Failure of a SQL Server service causes failover

Resolution: To prevent the failure of specific services from causing the SQL Server group to fail over, configure those services using Cluster Administrator in Windows, as follows:

he Microsoft® Exchange Server Analyzer Tool reads the following registry entry to determine whether you have configured a failure of the Microsoft Distributed Transaction Coordinator (MSDTC) resource to affect the group:

HKEY_LOCAL_MACHINE\Cluster\Resources\

If the value of ClusterMSDTCInstance is 2, the Exchange Server Analyzer displays a warning.

The MSDTC resource must be present in an Exchange cluster to support initial installation and service pack upgrades. However, it is not required while Exchange is running.

By default, a failure to the MSDTC resource will affect the group. Two examples of resource failure are as follows:

• The log file size exceeds the capacity of the disk.
• The physical disk for the MSDTC resource fails.

Specifically, a failure to the MSDTC resource will cause a failover of all Exchange services that are running on that cluster node to a different node in the cluster. However, because the MSDTC resource is not a required resource, it does not have to be configured to affect the group.

To resolve this warning, configure the Exchange Cluster so that a failure to the MSDTC resource does not affect the group.

To configure Exchange so that a failure to the MSDTC resource does not affect the group

1.       Log on to any node of the cluster.

2.       Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3.       Under Groups, right-click the cluster group that includes the MSDTC resource.

4.       Right-click the MSDTC resource, and then click Properties.

5.       On the Advanced tab, clear the Affect the group check box, and then click OK.

·         Clear the Affect the Group check box on the Advanced tab of the Full Text Properties dialog box. However, if SQL Server causes a failover, the full-text search service restarts.

(e)       Problem: SQL Server does not start automatically

Resolution: Use Cluster Administrator in MSCS to automatically start a failover cluster. The SQL Server service should be set to start manually; the Cluster Administrator should be configured in MSCS to start the SQL Server service. 

(f)        Problem: The Network Name is offline and you cannot connect to SQL Server using TCP/IP

Issue 1: DNS is failing with cluster resource set to require DNS.
Resolution 1: Correct the DNS problems.

Issue 2: A duplicate name is on the network.
Resolution 2: Use NBTSTAT to find the duplicate name and then correct the issue.

Issue 3: SQL Server is not connecting using Named Pipes.
Resolution 3: To connect using Named Pipes, create an alias using the SQL Server Configuration Manager to connect to the appropriate computer. For example, if you have a cluster with two nodes (Node A and Node B), and a failover cluster instance (Virtsql) with a default instance, you can connect to the server that has the Network Name resource offline using the following steps:

1.       Determine on which node the group containing the instance of SQL Server is running by using the Cluster Administrator. For this example, it is Node A.

2.       Start the SQL Server service on that computer using net start. For more information about using net start.

3.       Start the SQL Server SQL Server Configuration Manager on Node A. View the pipe name on which the server is listening. It should be similar to \\.\$$\VIRTSQL\pipe\sql\query.

4.       On the client computer, start the SQL Server Configuration Manager.

5.       Create an alias SQLTEST1 to connect through Named Pipes to this pipe name. To do this, enter Node A as the server name and edit the pipe name to be \\.\pipe\$$\VIRTSQL\sql\query.

6.       Connect to this instance using the alias SQLTEST1 as the server name.

(g)       Problem: SQL Server Setup fails on a cluster with error 11001

Issue : An orphan registry key in [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Cluster]
Resolution: Make sure the MSSQL.X registry hive is not currently in use, and then delete the cluster key.

(h)      Problem: Cluster Setup Error: "The installer has insufficient privileges to access this directory: <drive>\Microsoft SQL Server. The installation cannot continue. Log on as an administrator or contact your system administrator"

Issue: This error is caused by a SCSI shared drive that is not partitioned properly.
Resolution: Re-create a single partition on the shared disk using the following steps:

1.       Delete the disk resource from the cluster.

2.       Delete all partitions on the disk.

3.       Verify in the disk properties that the disk is a basic disk.

4.       Create one partition on the shared disk, format the disk, and assign a drive letter to the disk.

5.       Add the disk to the cluster using Cluster Administrator (cluadmin).

6.       Run SQL Server Setup.

(i)        Problem: Applications fail to enlist SQL Server resources in a distributed transaction

Issue: Because the Microsoft Distributed Transaction Coordinator (MS DTC) is not completely configured in Windows, applications may fail to enlist SQL Server resources in a distributed transaction. This problem can affect linked servers, distributed queries, and remote stored procedures that use distributed transactions. For more information about how to configure MS DTC, see Before Installing Failover Clustering.
Resolution: To prevent such problems, you must fully enable MS DTC services on the servers where SQL Server is installed and MS DTC is configured.
To fully enable MS DTC, use the following steps:

1.       In Control Panel, open Administrative Tools, and then open Computer Management.

2.       In the left pane of Computer Management, expand Services and Applications, and then click Services.

3.       In the right pane of Computer Management, right-click Distributed Transaction Coordinator, and select Properties.

4.       In the Distributed Transaction Coordinator window, click the General tab, and then click Stop to stop the service.

5.       In the Distributed Transaction Coordinator window, click the Logon tab, and set the logon account NT AUTHORITY\NetworkService.

6.       Click Apply and OK to close the Distributed Transaction Coordinator window. Close the Computer Management window. Close the Administrative Tools window.

(j)      Quorum log too small

The Microsoft® Exchange Server Analyzer Tool reads the following registry entry to determine the size of the quorum log configured for the cluster:

HKEY_LOCAL_MACHINE\Cluster\Quorum\MaxQuorumLogSize

If the Exchange Server Analyzer finds the value for MaxQuorumLogSize less than 4194304 decimal (0x400000 hexadecimal), a warning is displayed.

Warning: - The MaxQuorumLogSize registry value represents the currently configured value for the Reset quorum log at cluster quorum parameter. This warning is generated if the MaxQuorumLogSize is less than 4096 kilobytes (KB).

The cluster records all changes to the cluster database in the quorum log file. When the quorum log attains the specified size, the cluster saves the database and resets the log file. On Microsoft Windows® 2000 Server-based clusters, the default quorum size limit is 64 KB. On Windows Server™ 2003-based clusters, the default quorum size limit is 4096 KB. For Exchange Server clusters, it is recommended that the Reset quorum log at property be configured to 4096 KB. This ensures that there will be sufficient space to hold the cluster configuration information, such as which servers are part of the cluster, what resources are installed in the cluster, and what state those resources are in (for example, online or offline).

To correct this warning-

1.       Open Cluster Administrator.

2.       In the left pane, right-click the object that represents the cluster, and then click Properties.

3.       On the Quorum tab, configure Reset quorum log at with a value of 4096.

4.       Click OK to save the changes.

Please comment here other Clustering scenario based problems, Issues, Troubleshooting and Fixes - Jainendra Verma

How to restore database in Always-On --OR-- Database is Corrupted/Suspected and need to restore it in existing Always-On availability replica?

Please find below very smile 6 step to Restore the New or suspected database in Always-on

1.   Open SSMS    à Connect to Primary availability group Server (Server1) à explore Availability databases  à  Right click and remove the database<DB1>.

2.   Refresh instance   à  you will see the removed database<DB1> will not be available in Primary availability database group  à  Go to the databases (Here the Synchronized status will be removed)  à Now restore the Full backup file with Recovery mode and with Replace option.

3.   Now Connect to the Secondary Availability Group Server(Server2)   à   Go to the databases and restore the database<DB1.bak> with No Recovery option, also restore the T. log<DB1.trn> backup of the database.

4.   Now again Connect to Primary availability group Server   à    Right Click on Availability database and click on ADD database option    à  Select your Restored database <DB1>   à select the any one Initial data synchronization mode <Full, Join Only>    à  Next  à  click on Connect button, to connect Secondary replica <Server2>  à Validation  à Next   à Summary à  Next  à  Result.

5.   Now refresh your both Servers instance and see on the Primary Server, database<DB1> should be in Synchronized state and on the secondary server, database should be in Synchronizing state.

       6.  Also do the failover and check the health on AG Dashboard

Please let me know if you have any other step to do the same. - Jainendra Verma

What is TRACE and How to configure, Use and Read Trace with Examples in SQL Server?

What is TRACE?

               A Trace allows you to track the specific and particular actions and event that performed against a SQL Server databases. A TRACE provide vital and valuable information and details for troubleshooting and monitor database issues, problems and tuning database engine performance.

Why we use SQL Trace?

·  Has someone deleted a table?

·  Are you trying to track auto grow events?  Problem scenarios such as Database auto grow and slow database recovery

·  When did SQL memory usage change? Read SQL Server Performance, memory pressure and memory usage for SQL Server memory analysis

·  SQL Server security changes?

The default trace has loads of information.

WHAT TYPE OF DATA IS AVAILABLE FROM THE DEFAULT TRACE?

 Object creation, object deletion, error events, auditing events, full text events

WHAT SORT OF EVENTS DOES THE DEFAULT TRACE FILE CAPTURE?

--returns full list of events

SELECT *  FROM sys.trace_events

--returns a full list of categories

SELECT * FROM sys.trace_categories

--returns a full list of subclass values

SELECT * FROM sys.trace_subclass_values

How many types of TRACE categories?

1.      Cursors

2.      Database

3.      Errors and Warnings

4.      Locks

5.      Objects

6.      Performance

7.      Scans

8.      Security Audit

9.      Server

10.  Sessions

11.  Stored Procedures

12.  Transactions

13.  TSQL

14.  User configurable

15.  OLEDB

16.  Broker

17.  Full text

18.  Deprecation

19.  Progress Report

20.  CLR

21.  Query Notifications

HOW DO I CHECK DEFAULT TRACE IS ON?

SELECT * FROM SYS.CONFIGURATIONS WHERE CONFIGURATION_ID = 1568

HOW DO I ENABLE DEFAULT TRACE?

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

HOW DO I FIND THE DEFAULT TRACE FILE?

SELECT * FROM ::FN_TRACE_GETINFO(0)

HOW CAN I LIST OBJECTS DELETED IN THE LAST 24 HRS FROM A SPECIFIC DATABASE?

SELECT *

FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_154.trc',0) tf

INNER JOIN sys.trace_events te

ON eventclass = trace_event_id

INNER JOIN sys.trace_categories AS tc

ON te.category_id = tc.category_id

WHERE databasename = 'AdventureWorks' AND

objectname IS NOT NULL AND

te.category_id = 5 AND

te.trace_event_id = 47 

--SQL server- 47 trace event it is for Oject Deleted.

How to Create a Trace (SQL Server Profiler)

This topic describes how to use SQL Server Profiler to create a trace.

1.      Profiler -> File menu, clicks New Trace, and connects to an instance of SQL Server.

2.      In the Trace name box, type a name for the trace.

3.      In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.

4.      To save the trace results, do one of the following:

o        Click Save to file to capture the trace to a file. Specify a value for Set maximum file size. The default value is 5 megabytes (MB).

Optionally, select Enable file rollover to automatically create new files when the maximum file size is reached. You can also optionally select Server processes trace data, which causes the service that is running the trace to process trace data instead of the client application. When the server processes trace data, no events are skipped even under stress conditions, but server performance may be affected.

o        Click Save to table to capture the trace to a database table.

Optionally, click Set maximum rows, and specify a value.

When you do not save the trace results to a file or table, you can view the trace while SQL Server Profiler is open. However, you lose the trace results after you stop the trace and close SQL Server Profiler. To avoid losing the trace results in this way, click Save on the File menu to save the results before you close SQL Server Profiler.

5.      Optionally, select the Enable trace stop time check box, and specify a stop date and time.

6.      To add or remove events, data columns or filters, click the Events Selection tab.

7.      Click Run to start the trace.


Please provide your input if anything missing here- Jainendra Verma