Ever wonder what your usage statistics are for your transaction log files without opening each individual one in the SQL Server Management Studio shrink file dialog. DBCC SQLPERF is a neat little utility that can tell you the size of the log file and the amount of the space occupied by transactions inside the file.
You can also use this utility to clear the sys.dm_os_wait_stats and sys.dm_os_latch_stats statistics, in such a manner:
A few weeks ago, I let loose a rebel yell and issued a command directive: Death to Oracle! Then I delivered the death knell as we migrated all of the data to SQL Server over Memorial Day weekend. Today, I am proud to announce that after two weeks of the Finance SQL Servers being in production, the users are happy and productive.
Most would say that is how it should be, but the Oracle DBAs told me that the users would complain daily. They were happy to rid themselves of those users and now I know why.
Hopefully I did not jinx myself but it looks like the domination plan is working!
We had a meeting this morning where our Director talked about initiatives and people bringing forth new ideas to help streamline our department. Therefore, soon I will be bringing forth a new initiative to eliminate Oracle altogether and replace it with SQL Server. It will ultimately be denied because of the cost involved with the conversion but they will have to discuss it and think about it. Take that Oracle, SQL Server Global Domination plan is gaining traction.
Last week I let loose a rebel yell and issued a command directive: Death to Oracle! Today I am proud to announce that I dealt a death knell to one of our Oracle servers by migrating our finance applications from Oracle to SQL Server. This was a year-long project that culminated in one really, really long weekend of SQL Fun. I survived and Oracle bit the dust. My plan for SQL Server World Domination has completed phase one.
My part of the project included a 27-hour marathon involving migrating the data using SQL Server Migration Assistant and then numerous scripts and comparisons to day-zero databases from the vendor while upgrading the application across five different product upgrades using RedGate SQL Compare (which worked beautifully). It was a day full of Starbucks triple espressos and five-hour energy shots before I crashed and the testers took over. We then rounded out the weekend with a 33-hour ETL process which all completed successfully before the application went live at 7am this morning. Mission accomplished.
This project has great visibility in my organization and its success will open the flood gates to future migrations in showing our world that SQL Server is an enterprise level database. I am not stopping here, I want their 100 terabytes of production data! It will be mine, oh yes it will be mine.
Today signals the end and the beginning for a year-long project. My project for SQL Server Domination. Our shop is primarily an Oracle shop with dot net developers. Although I do have almost 300 databases across all levels, the bulk of mission critical applications are written for Oracle.
A little over a year ago we began a plan to migrate our financial applications to SQL Server in order to retire the oldest Oracle servers on site and reduce our massive licensing footprint. This was all part of my plan for SQL Server World Domination (said in a maniacal voice, imagine that). We knew that if this mission critical migration was a success that we could leverage our product against the Oracle servers in-house and begin the destruction of their infrastructure in favor of my glorious SQL Servers! (Hear the trumpets heralding the new king?)
This project, if successful, will be the Oracle death knell. Over the last year I have prepared diligently with one goal in site: this migration weekend. The weekend of my birthday seems apropos to give me the present that I so desire, SQL Server Domination. You may have noticed that the number of blog posts recently have been limited especially after blogging every day last year. For the last few months, I have had one purpose and one purpose only: ensuring the success of this migration!
Let the trumpets herald the coming of a new dawn. Long live SQL Server. I will blog again next week on the other side of this project to declare that the Queen is dead and a new King has ascended the throne! Until then queue the Imperial March, hello Starbucks and massive four day working weekend with little sleep migration project here I come!
When I saw this month’s subject, I lunged forward and began to write dropping everything else that I was working on. Why? Any chance to incorporate SQL Server with the Beatles is worth the time and effort. After all, they are the greatest band of all time and if you disagree then you are just going to have to live with being wrong, right?
Many moons ago, well it was actually 1997, I had written some applications in Visual Basic: yes, the old and horribly slow one, you know the one. But then I discovered Delphi (Object Pascal for the kiddies) and wrote amazing some amazing applications. I even wrote a few games back then just to prove to myself that I could, they were not horribly great but I felt accomplished and I often wonder where those floppies are so that I could play them again. Programming was my passion and it felt so amazing to create something from scratch, my own creation, my own Frankenstein: “It’s Alive!”
From there a company noticed some of my freeware applications and offered to move me to Virginia and teach me to write business applications in Progress 4GL for Unix and Windows. Wait, you are going to train me, mentor me, and pay me double my current salary. I’m in. Progress was an amazing product with a very well written SQL-compliant RDBMS that rivaled Sybase, Informix and Oracle only without the marketing hype. Their claim back then was that they were in over 60% of the Fortune 500. The problem was that with their Value Added Reseller structure, many vendors would repackage their database as their own (as was allowed if they paid enough for the rights to distribute the product), so many of those companies had no idea that their database was “Powered by Progress.”
My years with the product were amazing learning to write code that would run in Unix, compile and run in Windows and then eventually would become used inside of tags of HTML to deliver data to the web was ground breaking at the time. I was sure that this was the future, so much so that I became a consultant for the company traveling the globe for two years helping clients with installation, troubleshooting and training. I drank the kool-aid. I was pretty sure that with a little marketing they would sink the Oracle juggernaut.
At one company I even administered a SQL Server 6.5 and 7.0 boxes because they had vendor software that required it and I was the database guy for Progress. I remember thinking how SQL Server was so miniscule compared to Progress. I remember thinking that they will never make it in this sector. Well I was wrong about the Xbox, too.
Enter early 2004, I had just successfully completed a fulfilling project and was looking for my next contract opportunity. The well was dry, there were no opportunities for Progress. I had a home and a small children and my family did not want to move. The road had ended that day. I had to take a job as a systems administrator to make ends meet. The dream was over. In the words of the Beatles….
The wild and windy night, that the rain washed away
Has left a pool of tears crying for the day
Why leave me standing here? Let me know the way
Then in 2008, I started a position that was part programmer and part SQL Server DBA. Prior to this, I contemplated going back to school and leaving the IT field. But as the Beatles would say…
And still they lead me back to the long winding road
You left me standing here a long, long time ago
Don’t leave me waiting here lead me to your door
The dream had come full circle, I found my new passion: SQL Server. It really was my old passion in disguise: DATA! Hopefully this technology will be the one for many years to come, what do you think?
Back in January, I did a series on simple auditing which walked through setting up an audit and creating a simple SSRS report. I actually used the report and the query that I demonstrated for a couple of servers in my stack that require auditing. The basis of this revolves around the following line of code:
SELECT * FROM fn_get_audit_file (‘g:\auditing\*’, DEFAULT, DEFAULT)
This line of code runs great for my login and I never gave it much thought because I am running it from a sysadmin account. When asked for users to be able to run this report that is when things are getting sticky. I cannot fathom why a simple query for log data cannot be attributed to a read-only account. In order for you to run this select statement you either need a sysadmin level account or the CONTROL SERVER permission which pretty much gives you the keys to the kingdom. Why would they do that?
I am sure there is a great technical reason, but why not allow it to filter down to read-only access to simply view the data? With those permissions whoever is in charge of running the audit can manipulate the data however they see fit. That makes no sense to me but then again who am I in the grand scheme of things? Shortly I will post a few different scenarios in how to solve this problem. Enjoy!
I was doing some mentoring today with online restores, so I thought I would share it with my blog readers. The most important thing about online restores is that it is only supported in SQL Server 2005 and later versions. In addition, for the bulk-logged recovery model the following conditions must be met in order to complete an online restore:
- All of the log backups must have been taken and completed before you start the online restore.
- If you have bulk changes in the database, the files must be online.
- All of your bulk changes must be backed up before starting the online restore process.
In addition, I have seen it recommended that you switch your bulk-logged recovery model to the full model before starting the online process and then switching it back afterward for smoother recovery. Enjoy!
For me I am not sure that I am at that point. I love being a presenter, I love going to the speaker dinners for SQL Saturday events, I love meeting other speakers and talking about presenting, but in all honesty I am not sure that I am in love with presenting. But I am getting there. Presenting still terrifies the hell out of me, but it is a fear that I set out to conquer last year as a goal in my personal development plan.
For a long time, if an activity involved a public speaking component I avoided it like the plague. Seriously. I am not kidding here. I coached Dixie Youth baseball and Pop Warner football and every year I had to give a first practice speech. I would spend a couple of sleepless nights and I would vomit several times before muttering something from my note cards and then quickly move on to begin the practice. The last few years I stopped doing it all together and went around to the parents individually and this worked much better for me. Crisis averted. Problem put back on the shelf for a later date.
After several SQL Saturdays, I started to think that I could do this again. Some time ago, I was a consultant that also provided week-long training courses in addition to programming and DBA work in Progress 4GL RDBMS, but all of the material that I presented was created by a team (similar to a Microsoft training course) of professionals. Whenever I got nervous in those courses, I could always go back into the material as it had notes on the slides. It was fool-proof, and after doing them over and over I was a pro at it. But after not doing this for twelve years, the fear took over again.
After presenting a lightning talk in Pensacola, a couple of user group meetings and a few SQL Saturdays, the fight or flight reflex has lessened and the vomiting is non-existent. I can do this, I am doing this, and I will conquer this. I can proudly say that I brushed the fear back and now think that I am getting better at this. I am starting to love presenting, it has a foothold in the depth of my being. What about you?
On day two of our adventure, we spent the day reconnecting with my wife’s uncles and cousins and their respective families which was the original purpose of the trip. I had a great time meeting all of these people, what a great family.
However, on day three we set out for the tourist destinations of my youth: the Gateway Arch and the Saint Louis Zoo. The Gateway Arch is now a national park and it is a beautiful and spacious park at the base of the Mississippi River with the historic courthouse in the background as shown in the gallery below. Also at the base of the Arch is the Museum of Westward Expansion which seemed much grander when I was a kid but did have some animatronic characters for me to enjoy.
Later in the day we also visited the Saint Louis Zoo which was free (with a $15 parking fee, still not sure how that’s free). The zoo seemed bigger than when I was a kid which is always a plus. What a great day of adventuring. Enjoy!