Blog Archives
Sunday Funday
Today’s installment of Sunday Funday brings you a great little drawing with T-SQL exercise by Roji P. Thomas. Enjoy!
What’s In A Datetime Anyway?
Last week we talked about my Oracle migration to SQL Server and the vendor’s use of datetime over datetime2. Today, I thought I would discuss the differences between the two datatypes.
First of all, datetime2 is recommended by Microsoft as opposed to datetime as it provides a larger data range, a larger default fractional precision as well as the optionally defined user precision level. Datetime2 is also ANSI and ISO 8601 compliant, whereas datetime is not. Which would you use?
Example:
Datetime2: 2012-06-25 12:45:10.1234567
Datetime: 2012-06-25 12:45:10.123
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment involves the Prime Number Challenge! This is a clever and fun T-SQL script, so check it out!
Enjoy this #SQLAwesomeness!
My Mistake For Your Enjoyment
This morning when an aggravated user contacted me that they could not get into a new database that I setup on Friday, I discovered that I had made a mistake. Well that’s never happened before, well not today anyway.
Alright, here we go. I created three new logins that would access development and acceptance copies of a production database that I had just setup on an acceptance server using Red Gate‘s SQL Compare and SQL Data Compare (which I will blog about soon, excellent product). In my possession, actually a sharepoint list, I had the password for each of these three new logins. Today I found out that the password I had listed was out of date. No problem, easy fix!
Using SSMS I would easily change the passwords and then update my sharepoint list to the new passwords, this is DBA 101 stuff. Not so fast my friend! SSMS then presented an error stating that the password did not meet our ultra-rigid fort knox password policy (see posts on security). No problem, I forgot to uncheck the ‘Enforce password policy’ check box. That happens all the time when I get in a hurry. Now it is unchecked and SSMS presents the following error:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
There was my real mistake committed on Friday; I forgot to uncheck the ‘User must change password at next login’ check box when I created the logins. It was a rookie mistake, but I was in a hurry to get things done so I could go and get my root canal in the afternoon. Luckily for me this was not a resume updating event. In order to fix this we need to do the following:
USE MASTER
GO
ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’
GO
ALTER LOGIN [userlogin] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
Then I was able to change the password, which I used ALTER LOGIN since I had it in the query window. What a way to start the week off. Enjoy!
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment is from one of the funniest technical presentations that I have ever seen. If you get a chance to see it at a SQL Saturday or Code Camp near you, by all means attend. It is by Rob Volk (blog | twitter) and it is called Revenge: The SQL!
Enjoy this #SQLAwesomeness!
Cleaning Up SQL Database Mail Logging Records
This morning in my production cluster I noticed MSDB had grown to almost 10gb. After some help from my friends at #SQLHelp, I ran one of the built-in reports, which are great…thank you Microsoft, ‘Disk Usage by Top Tables’ to tell me which table(s) had grown since I last checked this system database. I think I found a winner: sysmail_mailitems had an outrageously high amount of records. Why aren’t these being cleaned up in a job similar to sp_delete_backuphistory or sp_purge_jobhistory?
In order to clean these items up, use the following code:
USE MSDB
EXEC sysmail_delete_log_sp @logged_before=’2012-01-13 12:00:00′
You could script this out to run nightly or weekly and remove the last 30 days. Enjoy!
sysmail_mailitems
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment is Solving Soduku with SQL! This is a clever and fun T-SQL script, so check it out!
Enjoy this #SQLAwesomeness!
Configuration Data Best Practice
After my recent Health and Risk Assessment visit from my Microsoft Premier Field Engineer, I learned some new and exciting SQL Server best practices and I thought that I would share them with you in this blog space. Our topic-o-choice today is all about backing up your configuration data. I must admit that I have not given this topic a second thought in the past and luckily it has never bitten me before. Microsoft defines configuration data as your sp_configure, SSIS packages and your replication topologies. These items are not usually backed up in your nightly backups and may not be contained in your system databases.
Microsoft recommends scripting your SQL Server configuration data to a file using SELECT * FROM sys.configurations anytime you make changes to your server. I would then include this in your disaster recovery walk-away bag or remote location destinations. Personally, I would recommend documenting these settings if at all possible in a Word document explaining why you have chosen the settings and if they relate to your department’s best practices so that when you get hit by a bus, your replacement DBA will understand the intricacies of your choices and not chalk them up to the ‘this guy was an idiot who knew not the ways of the force.’
In addition, if you are using replication then you should script out all of the components for your disaster recovery. It is also a good idea to back up your master and MSDB databases for the publisher, distributor and all of your subscribers. I personally have never done that, but Microsoft recommends it…so do IT! Enjoy!
Windows Logins Basics
As most people would agree, Windows Authentication is the preferred authentication method in SQL Server as it provides consistency with your Windows or AD administration and it allows you to use groups for ease of administration if your users are organized into groups by security level and function.
To create a login in T-SQL use the following code:
CREATE LOGIN ‘AD\Ed’
To remove a login using T-SQL, use the following code:
DROP LOGIN ‘AD\Ed’
To deny a login using T-SQL, use the following code (NOTE: if the user or group does not exist in SQL Server, this command will add them first):
DENY CONNECT ‘AD\Ed’
This is a good foundation for a couple of other posts that I am working on for this week. Enjoy!
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment is called Minesweeper in T-SQL! This is a clever and fun T-SQL script, so check it out!
Enjoy this #SQLAwesomeness!
SQL Swampland 