Blog Archives
Weird DBMail Error in 2008
Found a weirdness this morning that I thought I would share with you. I have recently setup up two new SQL Server 2008 clusters and setup the database mail yesterday so that I can receive a nightly backup report, which is much easier than 50-100 individual emails per server. The database mail worked on the send test email, but was not sending out for jobs.
I talked to our exchange guy to make sure there was nothing on that end preventing the flow. I reviewed the settings and review the SQL agent job. Nothing jumped out at me. The job history showed SQLSTATE 42000, ERROR 14607.
Upon bingling the error code, I found the error to mean incorrect profile name. I named the profile, so now SQL Server is judging my naming convention?
Wait, I scripted out the job from another server, but I changed the names in the script. I rechecked the profile on the source server and it was named “<server name> Mail”. However my profile on the new server was simply named after the server name. Once I changed the name by deleting the profile and creating a new one, the job worked successfully. Attention to details! I hope this helps someone, enjoy!
Removing a Perfectly Good Cluster, Part Two
Yesterday I started a new project to downgrade our two new SQL Server 2008 R2 clusters down to SQL Server 2008 clusters. The uninstall went off without a hitch as we removed both nodes and then removed the support tools. I find it interesting that when we went to install the 2008 server, there was still tempDB data files which prevented the new install from moving forward until we deleted them. I am not sure if the other system databases were there as the installer did not complain about those. In hindsight, I probably should have removed all of the directories and files before installing as a general best practice but I did reboot the server prior to the new install and thought it would be fine.
Because I do not spin up new clusters everyday (that would be a great job), I took screen shots during the initial 2008 R2 install to serve as a guide because I knew that I had a total of three clusters to build by the end of the year. In this scenario, documentation is an amazing thing (well it is amazing in most areas but most DBAs become complacent about doing it myself included). Originally I built the first two clusters back in January and since they were the first clusters that I had ever built I wanted to document it as I am responsible for many systems and quite honestly I would not have remembered the settings chosen on each screen. Having worked with SQL Server for sometime, I could have configured a stand-alone server in my sleep, but I was not as confident with clusters. My confidence is building at this point.
Now, it appears that building 2008 SP3 servers is almost identical to 2008 R2 servers from slipstreaming through the configuration for best practices. In a future post, I will discuss some best practice troubleshooting I did for these reinstalls. Enjoy!
Removing a Perfectly Good Cluster, Step One
Today we embark on a somewhat sad journey. A couple of days ago, I told you a story about a vendor and the miscommunication of specifications. Today the chickens have come home to roost. We must replace the SQL Server 2008R2 clusters with 2008 clusters. These were perfectly good clusters, fine tuned and ready to burst out of the starting gate and win the triple crown, well you understand what I mean. I’ve grown attached to these clusters as they were the first ones that I have ever personally built from the purchase order to production.
Today, we will uninstall both of the SQL Server nodes and then begin the reinstallation of the previous version. We will run the installer on the passive node first. In the installer go to the maintenance section and choose “Remove node from a SQL Server failover cluster.” After this is complete then we will go to the active node and go through the same process. Tomorrow we will look at the reinstallation of SQL Server 2008 SP3. Enjoy!
Vendor Rant, Starting Over
We interrupt this regularly scheduled blog series to bring you a rant. Now wait don’t click away just yet. There is a lesson here somewhere, I hope.
To give you some background, we have a vendor, henceforth known as XYZ, to whom we have worked with for several years on their financial package. The package is currently in Oracle and it was decided that we could reduce costs by upgrading to the newest version of their package but migrating it to SQL Server to reduce our Oracle licenses which makes sound financial sense. Being a predominantly Oracle shop, I have been masterminding the demise of Oracle for the year that I have been here quietly chipping away.
This project has been in the planning stages for several months. During which time we order four identical super servers to be clustered into a production and development/acceptance active-passive clusters. I cannot deny that I indeed was excited about this project whole heartedly because of the hardware as well as the chance to reduce the Oracle footprint and to champion SQL Server as the preferred database. Plus, I have never built clusters from the ground up.
We took our time setting this servers up with Windows 2008 R2 Enterprise making sure that everything was well tuned. Then we setup our SQL Server 2008 R2 Enterprise clusters on all four boxes even bringing in our Microsoft Premier Field Engineer to ensure a successful migration ensuring that best practices were in effect. Most would view bringing in help as an insult to their pride, but I welcomed the learning opportunity and it helped with the learning of our green junior DBA who has no server or SQL experience. In addition, whenever I can be around our PFE, I am the eager padawan and she is the jedi master especially since she has an extensive Oracle background as well.
Fast forward and these machines are ready to go and all of the specifications were discussed and communicated several times through planning meetings. I even spoke with their DBAs during the install process to ensure that our settings were commensurate with the project. Now on Thursday of this week, the day before we are to begin migrating some of the Oracle data to the development box, I discover on one of the documents that the only version supported is SQL Server 2008 SP3 running on Windows 2008 R2 Enterprise. Hold the phone!
Rewind, notice I said we communicated several times that we were going to install SQL Server 2008 R2 Enterprise clusters on our new boxes and the vendor was compliant offering assistance if we needed it. THEY NEVER MENTIONED THAT THEY DID NOT SUPPORT R2. Now we have to uninstall R2 and install plain jane 2008 in effect putting us two versions behind and this project does not go live for another year after extensive testing. We even offered to be a beta testing site so that they could certify and say that they supported R2 since we have a year of testing ahead of us. DENIED! They were not interested whatsoever since none of the third-party tools such as BOXI support R2, according to them. Now I have to uninstall my beautiful creations and go backwards, this is progress. 😦
The moral of the story is to get the vendor to verify and sign off that the version you are installing is indeed supported before you install it. My supervisor and I thought by telling the vendor in the meetings that this would be evident. Next time we will force the issue before proceeding.
Logon Triggers aka How To Lock That Annoying User Out During Lunch
Earlier this week, I chose a few SQL Server 2008 topics for our #SQLHelp MCITP study group to discuss here on the blog. Thus, today I will discuss logon triggers, one of the new features available in SQL Server 2008 (well actually if I remember correctly they were snuck in 2005 SP2…shhhh). Logon triggers are a special form of trigger that the database administrator can use in order to monitor logon events.
Logon triggers can be used for the following purposes:
- To audit and control server sessions (such as tracking logon activity)
- Limiting the number of sessions allowed for a particular logon
- Restricting logons to SQL Server (similar to AD snap-in that lets you set the hours that a user may logon)
Here is an example from books online showing how to set up a logon trigger that limits the number of concurrent sessions for a particular logon, logon_test in this case. This example, in particular, will limit the concurrent sessions to three. You must create this logon in order to test this code, or change the logon name to match your requirements.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘logon_test’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘logon_test’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘logon_test’) > 3
ROLLBACK;
END;
Here is some sample code for restricting the user based on logon time (between 12Pm and 1pm in this example for all users except the SA\SQLAdministrator):
CREATE TRIGGER Restrict_Logon_HoursON ALL SERVERFOR LOGONASBEGINIF (DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13) AND(SUSER_SNAME() != ‘AD\SQLAdministrator’)BEGINROLLBACK;
END;END;
Once you have created logon triggers, you can view them by a simple query to the sys.server_triggers metadata.
SQL Swampland 