Browsing articles in "Useful Tweaks and Tips"
Nov
15
2017

Setting up Always ON Availability Group in Multi Subnet Cluster – Recommendations

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 […]

Oct
3
2017

Fix: SSMS 2012 opening Debug window when pressing F5

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 […]

Oct
3
2017

SQL Server End to End Encryption – Always Encrypt, SSL

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 […]

Sep
28
2017

Capture High CPU consuming process using Process Explorer

As a DBA we encounter situations when CPU is high on a DB server but DB is not consuming any CPU, we can see the processes consuming high CPU but don’t know how to identify that what process is doing causing issue and in cases where process is an encapsulated process like wscript.exe or any other system process we generally recommend server reboot but that generally washes out all logging; now as a DBA we […]

Sep
28
2017

Change SQL Server collation of master database

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 […]

Jul
11
2016

How to: Run NETSH trace for Network monitoring/timeout captures

While trying to troubleshoot the mirroring timeout issues which was in milliseconds, came across recommendation by a MS engineer to enable NetSH, below are instructions on how to enable it and capture logs, the logs can be supplied to Network vendor for further analysis.   Run following command to enable Netsh   Netsh trace start capture=yes scenario=netconnection maxsize=2000 filemode=circular overwrite=yes report=no persistent=yes tracefile=c:\%computername%.etl   Note: Please make sure you place this on a drive which […]

May
11
2016

Fix: Issue with Remote Desktop screen resolution while working from a high resolution system

Came across a unique issue on a friend’s laptop where he has high resolution screen but when trying to remote desktop any server then the remote server also inheriting the same resolution and text looks very small, MSTSC settings, desktop resolution settings, etc. everything is tried what possible but it didn’t fixed the issue. To resolve this, he found following solution to fix. Note: I am writing this post to keep a record of this […]

Oct
14
2015

SQL Server to Oracle Linked server error 7303: ORA-12504 listener was not given the service_name in connect_data

While configuring Linked server to oracle database from SQL server, faced following error,   Error: “ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA” (Microsoft SQL Server Error: 7303)     I did following validation checks to ensure things are in place: Tnsnames.ora – validated that SID is present in tnsnames.ora file which is generally located in C:\Oracle\product\11.2.0\client_1\network\admin\ folder Validate syntax of connection string, it should be in below format, ORA123 = (DESCRIPTION = […]

Sep
4
2014

How: Take SQL Server Full Memory Dump from within SQL Server and via Automated way

Sometimes there is need to take full dump for various kind of SQL Server issues and most of the time a DBA don’t know when issue going to occur hence becomes difficult to capture the dump, now to do same we need to have a standard approach, this post is compiled to work as procedure to take required dump file or automate it via trigger to ensure dump is taken even when no one is […]

Sep
4
2014

All about SQL Server Trace flags – Links

  While doing search for SQL Server 2005 trace flags found few good links which greatly describe the info in detail, please use as per your requirement, most of them may be applicable to higher version of SQL Server as well.   Flags can be set for Session or Global (some are startup) levels though for later some can only be switched at startup using –T (you are suggested to avoid –t which turns on […]

Pages:123»

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 34 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

December 2017
M T W T F S S
« Nov    
 123
45678910
11121314151617
18192021222324
25262728293031

View Post by Categories

%d bloggers like this: