While planning to configure always on availability group for first time on a multi subnet cluster found following use articles, it provides clear picture in terms of design and architecture, our requirement was to build SQL 2012 cluster on DC1 along with a reporting standalone SQL instance in same DC1, along with it, we want to have DR solution in DC2 where two standalone SQL instances are present on their respective server, to achieve this […]

    For setting up replication on AlwaysOn availability group in SQL Server 2012 and above, following need to be taken care to ensure replication will work irrespective of the AAG placement.   The following sections build the environment described above:   Configure a remote distributor Configure the Publisher at the original Publisher Configure Remote distribution on possible publishers Configure the Secondary Replica Hosts as Replication Publishers Redirect the Original Publisher to the AG Listener […]

On a recently built SQL Server 2016 observed following errors every 5 mins,   Login failed for user ‘DOMAIN\COMPUTER$’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] Error: 18456, Severity: 14, State: 5        As part of troubleshooting, tried following,   Created login DOMAIN\COMPUTER$ with public access as well as SA access but error continue to arrive Validated all SQL agent jobs, OS scheduler tasks, etc. but found nothing […]

With SQL Server 2017 GA announcement for October-2 2017, Microsoft is introducing changes to the servicing model through GDRs, CUs and SPs.   Excerpt from article published:   —————————————–   The Modern Servicing Model   Starting with SQL Server 2017, we are adopting a simplified, predictable mainstream servicing lifecycle: SPs will no longer be made available. Only CUs, and GDRs when needed. CUs will now accommodate localized content, allowing new feature completeness and supportability enhancements […]

During my evaluation of SQL Server 2014, I happened to face a problem, when I hit the F5 key in the Object Explorer, the SSMS was starting the Debug action instead of Refresh.Usually most of them will be happy for F5=Refresh and F5=Query Execution. Here is the procedure to Re-assign F5 key to refresh action in SQL Server Management Studio. Open Management Studio Go to Tools >> Options Expand Environment >> Keyboard >> Keyboard In […]

Starting Windows 2012, Windows cluster supports asymmetric disks which is required for Cluster participating in Always ON technology hence is a useful concept to be aware of,   http://www.sqlserverfaq.net/2012/09/16/when-do-we-need-asymmetric-storage-with-alwayson-fci-and-availability-storage/   https://social.technet.microsoft.com/Forums/windows/en-US/493bf438-eb9d-405c-81c5-42aaa31eef8a/configure-asymmetric-storage-for-crosssite-windows-failover-cluster?forum=winserverClustering  

As we know the solution for SQL Server Data at rest encryption is TDE (Transparent Data Encryption) but what if application need End to End data encryption for PII fields, the solution is Always Encrypt columns in SQL 2016 Ent. Ed., from support DBA perspective it’s more of setting it up and supporting it but major liability lies with application developer, they need to validate and implement solution in first place; certainly there are few […]

To change server collation following steps needs to be done,   Use the SQL Server set up, and run the following command from the folder where set up file is located,   Imp Note: this will rebuild master database and subsequently all information like user db, logins, jobs (msdb) will be lost, script out everything as described below before triggering the command.   <sql server media folder>:/Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=INSTANAMEHERE /SQLSYSADMINACCOUNTS=DOMAINNAME\SERVICEACCOUNNTNAME /SAPWD= SAPASSWORD /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI (The […]

After struggling a lot and working with vendor, identified list of exclusions for SQL Server related to Anti Virus Software (McAfee, Symantec, etc.), the details can be found in this link, last one is important as it impacts performance.   https://support.microsoft.com/en-us/help/309422/how-to-choose-antivirus-software-to-run-on-computers-that-are-running-sql-server   Directories and file-name extensions to exclude from virus scanning When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. Doing this […]

Issue: While trying to restart SQL Service, it failed and following error found in error logs,   yyyy-mm-dd hh:mm:ss.xx Server Error: 17182, Severity: 16, State: 1. yyyy-mm-dd hh:mm:ss.xx Server TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol’s ‘IPAll’ configuration key. The data is invalid.   yyyy-mm-dd hh:mm:ss.xx Server Error: 17182, Severity: 16, State: 1. yyyy-mm-dd hh:mm:ss.xx Server TDSSNIClient initialization failed with error 0xd, status code […]