Finding SQL Server Analysis Services listening port
I have not worked with SQL Server Analysis Server very much. That is apparently changing as of now and I am still undecided on how I feel about that. Don’t get me wrong. I really enjoy working in SQL and will keep an open mind as my learning path continues. My heart however is with the main SQL engine.
During the server setup and configuration we record basic information on the server. One of the pieces of information I needed was what port Analysis Server listens on. I had no idea. So how do we determine what port?
Search for ASConfiguration
There is a chance you may find SSAS configuration by searching for msmdredir.ini.
You may also try looking in this directory
If you can’t find the ini file anywhere you may find it via the Process ID and netstat.
Finding Process ID
We need to start with finding the process ID that SSAS is using.
Using the Configuration Manager we can find what ID we need to look for.
Now open a command prompt. Start > Run > cmd
Now with the following commands we can use netstat to find what port SSAS is listening on.
Reviewing the output we find what we are looking for. The port is 4132.
Using the following commands we can output the information to a text file for whatever purposes you require.
And the output should look something like this.
Conclusion
As we can see, there are a few different ways to track down what port SSAS is listening on. The default port for SSAS is 2383. As most of you know, the port this component listens on can be changed. The evidence in the screen shot confirms that. Knowing how to find the information you seek as it relates to SQL Server and it components is an important part of a DBAs job.
Cheers!
Database in Transition: Error 952
I got a call the other day from a night shift DBA with an issue. Seems he tried to contact the primary DBA for the server but they didn’t answer their phone and I was the unlucky knucklehead to answer mine. As it turns out, it wasn’t a big deal and gave me a good topic for a post.
Issue
The DBA in question had an Incident ticket he needed to work on for a Project Manager a little heated due to overtime being scheduled and they were behind in getting things ready for the work. We needed to restore a database from the 3 AM backup over the database they were going to work on. One thing to note, our evening and night shifts work both Oracle and SQL. I did that in my past life and remember it is not always easy to switch modes from one to the other especially when a manager is constantly calling you to see if you were done with the task.
Error
The DBA who called explained he was getting an error when trying to restore a database. I asked if he was first putting it in single user mode. His response, “No”. I explained he needed exclusive access to the database to restore it. I then walked him through navigating to the database Properties window by right clicking on the database name.
He then said he was getting an error when trying to open the properties window. The error was:
“Database in Transition” Error 952.
After logging on myself and looking in the logs I found this.
It seems when he tried to restore the first time and was unsuccessful he tried to take the database offline.
I then explained we needed to use a query to make sure nothing was blocking us.
This is a very basic script to find the blocking SPID.
After tracking down the SPID causing the issue ( and killing it) we were able to open the properties window, script out the following command(s) for future reference/use and restore the database without issues.
If you are not familiar with how to script out the above T-SQL code you can follow these basic steps to do so.
In the Object Explorer expand the server and expand the database folder. Then find the database you want to use and right click the database. A menu opens, select Properties at the bottom. Once the database properties window opens, navigate to the Options page and scroll to the bottom. Under State group there is a setting called “Restrict Access” change that to SINGLE_USER and script out to a new window by clicking the “Script” button at the top of the window next to “Help“.
There are a few comments on forums that mentioned restarting the SQL Server service. While that will work (since it kills all sessions) it may not be the best solution especially if you are on a production server and can’t restart the service. Blindly restarting a server is not usually the best option.
Conclusion
There are (almost) always multiple ways to solve an issue. Coming up with the best solution sometimes comes with experience or finding the correct blog post/forum entry that works best for your situation.
Cheers!
Decompression Time
As DBAs we work long hours. Many times fixing issues before they become issues to end users. Constantly get thrown under the bus and told how to do things on our servers by uninformed and non-technical weenies people. For time to time we need to decompress I hit the road for Spring Break.
Heading to the Slopes
This year similar to the last three we are going skiing in Colorado. We are heading back to Winter Park this year. The last time we were there two years ago our son broke his ankle about two weeks before. Over the entire trip was a really good. Even for him. Since he was not able to ski Winter Park that year we are heading back. The only difference besides the lack of crutches my wife is not making the trip with us. It’s a guy’s trip this year.
The Drive and Weather Factor
The past two years we had a convoy with 2 other families and we drove straight through the night. This year I will be the only driver. I am not planning on driving straight through even though it would be nice to do. If I get tired I will just pull over and take a nap for a while.
I have been watching the past three weekends in a row winter storms have moved through the area we will be driving through. Last week, certain parts of the pan handle of Texas the roads were shut down. If Mother Nature is listening or reading this post, if you could hold off until Sunday evening to drop the snow that would be great, emkay?
Lunch at the Blue Bonnet
Heading Over the Pass
No not SQLPASS, Berthoud Pass. The best part of driving to Winter Park and going over the pass is once you get to the top, it’s all downhill to the resort, literally. Hopefully the traffic will be on our side and we will not have any issues like they did yesterday where I-70 was closed for a time with a multi-car pileup.
Conclusion
We all work long stress filed hours each day. It does not matter what you do for a job/career. From time to time you must “get away” from the daily routine and decompress. Weekend trips here can help over the long haul but downtime is a must to keep your mind, body, spirit and drive going. Maybe I will see you on the slopes. If not, I usually take a trip to the beach in the summer, maybe then.
Cheers!
How to get useful information from sys.dm_os_performance_counters
This DMV can be very useful to get information about SQL server. I need to point out a few key points before going too far into how we can use it. While it does return a wealth of information it does not return any counters outside of SQL Server for obvious reasons. The data returned can be a little confusing as well. Without further ado let’s get into it.
sys.dm_os_performance_counter
See the Note on the column named cntr_value
For now let’s take a look at the cntr_type information returned and see if we can clarify that a bit. Using the below query to get the counter types.
Now we have our cntr_type but we need to figure out what these numbers mean.
If we look on msdn we find information to help understand…hopefully 
PERF_COUNTER_LARGE_RAWCOUNT: 65792: Decimal: This value does not need calculations is the same as PERF_COUNTER_RAWCOUNT (65536) but is 64-bit for larger numbers values
PERF_COUNTER_BULK_COUNT: 27266576: Decimal: Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.
PERF_AVERAGE_BULK:
537003264: Decimal:
1073874176: Decimal:
Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.
PERF_LARGE_RAW_BASE: 1073939712: Decimal: This counter type collects the last observed value. It is the same as the PERF_RAW_BASE counter type except that it 64-bit and uses larger fields to accommodate larger values.
Using the query above written by Amit Banerjee twitter | blog
The below image is what we will see in the results. Still not the useful data we are after but we are getting closer.
Now on the first image there is a Note we need to be aware of.
For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
Keeping that in mind, if we look at the cntr_type 272696576 definition it is an “each second” value. We can run the query below gather the values run it again in say 1 second and determine our value for one second.
As you can see this DMV has some very useful information but at face value can be a little confusing. Only after digging deeper into the details on the cntr _type and doing additional querying and calculations can we get useful data.
Cheers!
Reference:
sys.dm_os_performance_counter
http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx
WMI Performance Counter Types (Windows)
http://msdn.microsoft.com/en-us/library/windows/desktop/aa394569(v=vs.85).aspx
A lighthearted look at our jobs
I need some help and I am reaching out to the community for some feedback. I am looking at gathering information along the lines of something like this.
If you aren’t checking to see if backups ran last night…
…You might be doing it wrong
If you aren’t reviewing SQL and or Server logs
…You might be doing it wrong
If you aren’t practicing restoring your backups
…You might be doing it wrong
Hopefully you get the idea and what I am trying to compile. Add comments and if I use your input proper attribution will be mentioned, that is, if I already don’t have it myself or someone else has not already provided the feedback.
Now to get things started.
If you don’t leave a comment here…
…You might be doing it wrong
Cheers!
SQL Server Accounts Locked Out
Those dreaded account lockouts. We all have to deal with them from time to time. I created a job to be notified when accounts become locked. That way I am aware of the issue and depending on the account, server and other factors I can deal with it with it in a timely manner.
Problem
When accounts become locked users can’t do their job or in worst case scenarios applications fail to work. I won’t go into details about application accounts locking other than saying it is an application design flaw.
The Code
You can see we need a few variables defined.
Then comes the formatting for the HTML output, this can be a little cumbersome if you don’t know HTML or want it to be really visually appealing. Personally, I just want the basics and that means looking better than plan old text display. It also provides a little more control over the output.
Then creating a temp table and insert the results. The heart of this code is query. Without the use of CAST and CONVERT or you will get an error when trying to insert into the temp table. I insert only for row counts, I then use @@ROWCOUNT to set a flag to send/not send the email notification. I did try and get the row counts from the query within the @tableHTML but always got one extra row even though no accounts were locked.
If there are accounts locked, the sp_send_dbmail code is used sending an email. If you look at @emailsubject the server name is inserted after some descriptive wording. This used for identification for the DBA group as well as the help desk when creating the tickets and the project it gets routed to. Makes it much easier to unlock accounts if you know what server they are on.
This is an example what the email looks like when received.
Conclusion
If you have unlock user accounts frequently or just have a need to be notified for awareness of accounts being locked this is one way you can do it. Are there others? Sure there are, but this is what I have come up with for now. The code is based off of this link from msdn on sp_send_dbmail http://msdn.microsoft.com/en-us/library/ms190307.aspx
I have added this code to my DBA Toolbox. To see some other snippets look under the DBA Toolbox category.
Cheers!
Dam the Security Risks, Full Speed Ahead
Being a former Navy man I thought a spin-off of the phrase “Dam the torpedoes, full speed ahead” said by Admiral David Glasgow Farragut was appropriate. Changing the phrase to fit my thoughts on how many software companies and developers still to this day don’t consider security when designing or updating application design and code.
Three Strikes and You’re out
It seems enforcing policies of complex passwords and locking accounts after 3 failed attempts is causing problems for one application in particular. It was written back at the beginning of the millennium and apparently not updated since.
According to complaints, users are being locked out because the policies are too strict. In addition several service accounts created during installation and needed to run the application are being locked out. One of the accounts is having major issues. Seems it runs a critical part of the application and is used by users when creating ad-hoc reports. If they don’t create the reports correctly and lock the account it brings down a major part of the application. Now that is some fancy coding right there folks.
Vendor Recommendations
They software vendor is requesting we remove the password policy so the users and the service accounts will never lock out. Their reasoning is based on how “it works” in the civilian sector without any password restrictions or lockout polices.
This way the users can attempt to log into the application as many times as needed until they log in with the correct password. Not to mention some of the passwords like “password1” and others similar can continue to work. After all it is a COTS product and any modification will require moths of code changes and tests and could cause major delays in the deployment.
Attitude Adjustment
Normally when those words are spoken by me or someone I call a friend, we are talking about adult beverages being consumed. This particular occasion I am speaking of my attitude and apparently what I must do to remain sane due to all the anti-intelligent thinking going on.
During a conference call the other day I was chatting with one of the managers via instant messenger about the request. Their advice; “Lower your standards”. Now, I really like this manager. But I must respectfully disagree with them on several levels on this one. Why is doing the right thing by enforcing policies for safety, security and just plain common sense so hard?
You can rationalize all you want if that makes you sleep better at night. I on the other hand have a real hard time doing things that just don’t make sense or are changed due to the political nature of the project.
Conclusion
“If doing the right thing is wrong, then I don’t want to be right.” – Unknown Author
The image wording to the left may be little extreme. Perhaps I am over reacting. Or maybe I actually care about some of these things others seemingly don’t care about. Until there is some type of preach or someones data is exposed due to loosening of some of the security safeguards. I don’t know. I think for now, I am just going for my type of attitude adjustment.
