We may get the situation where we need to run SQL Profiler Trace to capture information when Query is running on an Instance. We all know that running Profiler on a Production will impact the performance but still sometimes its necessary . Instead of selecting default or all options to run profiler, Please use the following selected options which are sufficient to troubleshoot the Issue which will have little performance effect :

• Run the SQL Server Profiler
• Click File -> New Trace
• Input the server name and click Connect
• Check “Save to file” and specify the file to store the SQL profiler log
• In the Events Selection tab, click “Show all columns”
• Drag the scroll bar and select the items under the column DatabaseName. Make all the items under DatabaseName Checked.
• Click Show all Events,

–> Expand Errors and Warnings, Select the following items: Attention, Exception, Errorlog, Eventlog, User Error Message
–> Expand Stored Procedures, select the following items: RPC: Completed, RPC: Starting, SP: Completed, SP:Starting, SP: StmtStarting
–> Expand TSQL, select the following item: SQL: BatchCompleted, SQL: BatchStarting; SQL: StmtStarting

• Click Run
• Wait for 5 minutes.
• In the SQL Profile, click File -> Stop Trace

We all know that only ONE Availability Group Listener can be created for Availability Group , if we tried to create another Listener using SSMS we will get below error :

As per the above error we can’t create another Listener in an Existing Availability Group.

But sometimes having Multiple Listeners will help when performing Migrations. So, my todays post is how to create Another Listener on Existing Availability Group .

Creating another Listener is not supported by Microsoft that’s the reason we are not able create using SSMS and its undocumented. This can be approached through Failover Cluster Manager.

open your WSFC–> expand your Cluster –>click on Roles–>on Right Pane select the Availability Group –> down at User Setting click on RESOURCES TAB to see existing Listener we have

To create new Listener on the existing Availability Group go t hrough below steps :

Now in Roles on Right pane–>Select your Availability Group for which you want to create another Listener–>Right click on the Availability Group–>go to Add Resources –> click on ‘ Client Access Point ‘

After clicking on Client Access Point–> A Pop Window ‘ New Resource Wizard ‘ will be opened–> Provide your New Listener Name in Name Field–>Also Provide the New Listener IP in ADDRESS Field–> click on NEXT

Your New Listener AGL1 will get created and it will be in OFFLINE Status , right click on AGL1 and click BRING ONLINE as shown below :

As I configured Multisubnet Failover Cluster seeing two IP’s for Listener.

Once new Listener was created –>Right click on Availability Group under User Settings–> AG Properties Pop window opens–> select DEPENDENCIES TAB–>Enter your New Listener Name on Resource Field and Select OR option as shown like below :

Once above all steps are completed we can see your New Listener in SSMS now :

But if you try connecting with New Listener Name AGL1 it will fail as we have not provided the PORT info in above steps .So, go to your New Listener AGL1 Properties in SSMS –> enter the PORT Number –>click OK

Now try connecting with New Listener Name AGL1 and it will get successfully connected.

This post is continuation of my previous Availability Group Listener creation post

If the domain Administrator is not allowing the Permission to create and read computer Objects then we can use this method for successful creation of Availability Group Listener.

Here we are creating the VCO ( which is Virtual Computer Object ) manually in Active Directory as we don’t have permission to create computer objects Automatically.

For doing this make sure the user who logged in has a permission to create computer objects in domain.

• Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
• Click on VIEW and select ‘ Advanced Features ‘

• Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in below

Now right click on the container where VCO needs to get created in my case it is in COMPUTERS –>click on NEW–>Click on Computer

Here we are creating the Listener Object in COMPUTERS Container

A Pop window ‘ New-Object Computer ‘ will be opened to provide your Listener Name –> Enter your Listener Name in Computer Name Field (AGL)–> click OK.

Now right click on the created VCO which is AGL in my case –click on Properties–>Click ADD button–>enter your cluster name ( in my case AWON$)–>click on Object types–>Enable check box for Computers–> click OK –>Again click OK

Now click on the Cluster Name ( AWON ) and see the permissions below and then click OK.

Read
Allowed To Authenticate
Change Password
Receive As
Reset Password
Send As
Validate write To DNS Host Name
Validate Write To Service Principle Name
Read Account Restrictions
Write Account Restrictions
Read DNS Host Name Attributes
Read MS-TS-GatewayAccess
Read Personal Information
Read Public Information

So, now Virtual Computer Object which is Listener is Created in Active Directory Domain.

Now try to create Listener Using SQL Server Management Studio(SSMS) and it will get created successfully without any Issue.

The most common issue we face when creating Availability Group Listner in SQL Server using SQL Server Management Studio(SSMS)

Below is the Error Screenshot we get :

To conclude that the above error is for CNO Permission Issue, please generate the cluster logs on your Primary replica and go through the logs , once it confirms the issue is with Lack of CNO Permission in Active Directory to read and create computer objects for Listener

CNO is nothing but Cluster Name Object which is a Cluster Name in my case it is AWON

To resolve CNO Permission Issue follow below instructions:

To fix we have to GRANT below 2 permissions to CNO :

1. Read all Properties
2. Create Computer Objects

For providing above permissions proceed with given steps:

• Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
• Click on VIEW and select ‘ Advanced Features ‘

• Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in below

• select computer–> right click –>Properties

• Now click on ADD button and enter your CNO name. In my case it is AWON$

• Now click on object types and enable check box COMPUTERS then click on OK ->OK

• And now you will be back to Computer Properties TAB, click on ADVANCED TAB –>Advanced Security Settings for computers Pop window will be opened–>Select your CNO ( Mine is AWON ) from Permission Entries –> Click on EDIT button

• A pop window ‘ Permission entry for Computers ‘ will be opened , now Enable check boxes for ‘ Read all Properties ‘ and ‘ Create Computer Objects ‘ –>click OK –> Again click OK –> click Apply –> click OK as shown below :

Now try again to create Listener and it will get created without any issue.

Command to execute CHECKDB

DBCC CHECKDB (DBNAME); — This Option use a SNAPSHOT
GO

Command to avoid creating a SNAPSHOT and saving the space on disk by using the option WITH TABLOCK .
By using this option there will be Exclusive locks on database which will prevent accessing database by users
Also dont run this command on Bussiness hours as it will impact the Production. This showuld be run only on Non-Bussiness hours.

DBCC CHECKDB (DBNAME) WITH TABLOCK; — This option does NOT use a SNAPSHOT
GO

The other alternative command to get CHECKDB runs faster and this will reduce the Contention
This Option DBCC CHECKDB with PHYSICAL_ONLY checks physical structure of the page and it will detect the torn pages ,common hardware failures which can compromises the users data.

DBCC CHECKDB (DBNAME) WITH PHYSICAL_ONLY

TempDB Usage and Intensity of I\O

We all know that DBCC CHECKDB is an I\O Intensive operation and major concern is related I/O caused by spooling to TEMPDBDB Database
The process of CHECKDb uses TEMPDB Database heavily and also we may get Performance Impact
To reduce the Impact of Performance we may have to configure disks Properly .So, that we get better throughput