Showing posts with label SSMS. Show all posts
Showing posts with label SSMS. Show all posts

Thursday 21 December 2017

Always On: Trying out Read-Only intent connections to secondary replicas

SQL Server Always On Availability Group enables us to connect Read-Only workloads to secondary replicas automatically by using differents ways. For instance, to connect .NET applications to secondary replicas, we must also include "ApplicationIntent=ReadOnly" besides the AG Listener Virtual Name in the connection string. More specifically, after configuring the Read-Only Routing list, this would be the connection string if we had an Availability Group with a Listener called "AGLISTENER" (in these examples it is used Windows Authentication).

Server=tcp:AGLISTENER,1433;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

But that is not all, it is also possible to establish connection to secondary replicas via SQLCMD tool by using the new parameter -K (and optionally -M for faster detection of and connection to the active server). If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica. So, this would be a simple example of how to use SQLCMD with -K and -M parameters. I couldn't have been simpler.

Sqlcmd -S AGLISTENER  -E  -K ReadOnly -M

And last but not least, we can also do it by using SSMS Connection Dialog window by using “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab. I also wrote another post about it with more details, take a look at it.


To sum up, these are the basic ways to try out Read-Only intent connections to secondary replicas, the first way is ideal for developers whereas the second and third if you are working as a DBA.
That is all for now. Let me know any remarks you may have. Stay tuned.

Thursday 11 May 2017

ObjectExplorer error: Dedicated administrator connections are not supported

Dedicated Administrator Connection (DAC) is a type of privileged connection that we can establish to SQL Server database engine when it does not respond because of too much workload. In this context, DAC is truly useful which allow to diagnosis and solve this sort of problems without restarting the database server any longer. Its is said that it is not possible to establish a DAC connection to the Object Explorer panel via SSMS so that we can get the following error while trying do it in that way:

   Cannot connect to admin:<SQLInstanceFullName>.
   Dedicated administrator connections are not supported. (ObjectExplorer)




It is true up to a point, but it does not mean that SSMS does not support DAC connections. Therefore, the question is:  can we ONLY establish a DAC connection to SQL Server by using SQLCMD? the answer is 'NO'. We also can do it via SSMS while creating a new SQL Query, which means that instead of clicking on 'Object Explorer' and 'Connect', we must click on 'New Query' and finally write 'admin:<Full Name of SQL Instance>' on 'Server Name' text box. That is all, it couldn't have been simpler.



Having done that, we will have a DAC connection ready via SSMS. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Wednesday 7 December 2016

New per-operator level performance stats included in SQL2016 SP1 and SQL2014 SP2

Traditionally, we were used to getting only basic runtime performance stats per operator regarding to CPU, I/O and Memory, which were not good enough to allow us to automate recollecting tasks of them. I strongly believe that DBAs are always overwhelmingly interested in diagnosing some issues proactively so as to prevent bad-written queries from using hardware resources inefficiently. Luckily, this convoluted situation has been changed radically in SQL Server 2016 and SQL Server 2014 SP2 since Microsoft has included very useful information per operator (and also per thread in the scope of each operator) which can be seen looking into the Showplan XML. For instance, we are going to see that information for the following query (in this case it has run under a serial plan, that is one thread).



This is its actual execution plan.


It is highlighted all what is new in SQL2016, SQL2016 SP1 and SQL2014 SP2. Therefore, we can appreciate CPU and I/O runtime performance stats for the Clustered Index Scan operator on 'PurchaseOrderDetail' table such as I/O reads, CPU time and Elapsed time. In a parallel plan you will be able to see information per thread in each operator.



And now we will see not only CPU and I/O performance stats for the Hash operator but also Memory performance stats.



All this information can also be seen in the Properties window in the context of each operator inside the Actual Execution Plan by using the latest version of SSMS. If you display the detail of each statistic, the detail per thread will be seen when working with parallel execution plan.



Having seen this, not only are we now able to look into useful performance stats by using Showplan XML and SSMS, but also we can create some scripts for administration and optimization purposes in order to pick up queries with poor performance and then optimise them. That is all for now. Let me know any remark you may have. Thanks for reading.

Wednesday 9 March 2016

SQL Server Analysis Services Error: The following system error occurred: (Microsoft.AnalysisServices)

Clearly, while adding some users as Windows Administrators, not only do we do that, but also we add them as Administrator of Analysis Services (SSAS). So, if we delete them from Windows then they will become orphaned users inside SSAS. As a result of this action, not surprisingly, we are going to get the following error when we are trying to add other users to the server administrator role in SSAS via the GUI or by code.

“The following system error occurred: (Microsoft.AnalysisServices)”

This sort of error is raised at times as the orphaned users have not got a correct matching inside the Active Directory. So, only their invalid user IDs will still be registered.



What we should do to fix this issue and be able to add other users is, firstly, to remove these orphaned users and, finally, add the new ones. I hope you find this short post interesting. Thanks for reading!

Monday 23 December 2013

Error: Index was outside the bounds of the array

It is said that we may work on SQL Server connecting to Object Explorer by using the SQL Management Studio tool (SSMS) of an older version. It is true up to a point, but there is a risk of getting some unexpected errors, for instance, we might get this error while working connected to SQL Server 2012 Object Explorer by using SQL Server 2005 SSMS.



More specifically, the error is raised because superior versions support more features, which means that each feature is likely to have one folder on the Object Explorer. Put differently, there is a bigger number of folders to display in SQL Server 2012 than SQL Server 2005 whose Object Explorer cannot display more because the max array index is outside the bounds that this supports. Ideally, it is highly advisable to always use the latest SSMS to connect to older versions (but not vice versa). As simple as water. That is all for now, let me know any remarks you may have. Thanks for reading.

Monday 21 November 2005

How to connect to SQL Server 2005 much faster via SQL Management Studio

While connecting with SQL Server 2005, we have to deal with the 'splash' screen and then indicate, basically, the following information such as service type, server name, and authentication type. This process may sometimes slow us down, nevertheless, we can optimise it as long as we know where we want to establish a connection. Put differently, we can save some time by using 'sqlwb.exe' tool which allows to start SQL Management Studio rapidly. This tool has some parameters we can use them to speed up the logon process to SQL instances. Here I am coming up with an illustration how to use 'sqlwb.exe' with some basic parameters. For instance,  if we would like to connect to the 'TestDB' database on 'SVPRDB1' server and with Windows Authentication, it can be done in this way:

sqlwb.exe -E -S SVPRDB1 -d TestDB -nosplash

Only after successfully executing that will we connect directly to 'TestDB' database without specifying manually the parameters and also without seeing 'the splash' screen showing the presentation of SQL Management Studio version. Now we can also customise the shortcut as it is shown in the following picture:



As earlier I said, this is the fastest way to connect to SQL Server which will allow to save some seconds. It couldn't have been simpler. That is all for now. Thanks for reading. Stay tuned.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE