Category Archives: SQLServerPedia Syndication

“Make It Right”

Leave a reply

Over the years I have grown accustomed to watching a show called Holmes Inspection. I enjoy working projects at the house ~ am I good at it, eh some things yes some things no, but nevertheless I do enjoy it.

The premise of the show is Mike Holmes goes into these homes in Canada where contractors have come in and basically not done the job they should have done; or families have bought homes and the inspector hadn’t caught obvious issues with the home to report back to the family. His biggest quote and what he is known for is “Make It Right” and also a big believer in “Do it right the first time and you won’t have to come back to it”. He has built his show and business around three things:

  1. Quality
  2. Integrity
  3. Trust

I’ve started to sit and dwell on these things and how that applies to my DBA work that I enjoy doing.

Quality – without a doubt the quality of a DBA is very important. In some instances it can make or break a company. From security down to the backup of the tiniest of databases. If a DBA is not on top of his/her game daily potential catastrophic events can occur. This in no way means that the DBA will be perfect; we hold ourselves to very high standards but at the end of the day we are human and mistakes will be made. When those mistakes do occur how do we handle those?

Integrity – one definition found in the dictionary says that this about integrity, “possession of firm principles: the quality of possessing and steadfastly adhering to high moral principles or professional standards” I can’t think of a better synopsis of a DBA.

Trust – as a DBA do people trust you? To me this is an obvious question….if you are a DBA you have all the ins and outs to the database where all the data is stored. A whole topic can be delved into on trust.

I’ve done things in the past and have viewed other DBA’s work that was done before me at jobs. Some things I sit back in amazement and think, “Why in the world is it done this way?”, and “What was I thinking?”

I find it very prudent to “Do It Right” the first time even if it means taking a bit longer to accomplish the task and ensuring all aspects (if applicable) have been thought out, and along those same lines if I come upon something that I know isn’t right then, “Make It Right” ~ quality, integrity, and trust

 

T-SQL Tuesday #31 – Logging

Leave a reply

Looking at my schedule I realized after discussing with fellow DBA’s that I was missing out on something ~ T-SQL Tuesday. I think this is fantastic idea and to host a T-SQL Tuesday you can contact Adam Machanic.

Today is T-SQL Tuesday #31 and is by Aaron Nelson on Logging……….

Logging

In general, to me, logging is an essential part of my every day activities as a DBA. It helps me in many ways tracking changes from deployments from fellow DBA’s and myself to bringing back statistical information for us to determine growth.

I recently came across two real world examples that has helped me with logging.

  1. A DBA friend was in the middle of a deployment; after several minutes the deployment was labeled complete and everyone was on their merry way. A few days later some catastrophic events unfolded; and everyone wanted to know the why; because we were logging the who, what, when along with the commands given we traced back quickly the change and what was changed by the user.
  2. I’m a huge fan of the Utility Database idea; have been ever sense I sat in on it at PASS ( Chris Shaw presented it). After getting back to the office it was my mission to get this idea implemented in. Currently, we are building an enterprise edition out that encaptulates a plethora of information from statistical stats, heartbeats, file sizes, space on drives, and the list goes on. Logging this information will help guide and plan for sizing and space.

I’ve only touched on a couple of topics regarding logging; there is so much more out there.

Roles, Schemas, and Drops Oh My!

Leave a reply

I recently ran into an issue where a development manager was creating a new role in SQL for his data warehouse initiative and cube building. He granted the role ownership of a newly created schema then changed his mind on what he wanted regarding a few items.

I provided the explanation to him that I had ran into this in the past and provided the following so that he could transfer the owned schema to another user or role then drop his role he created.

If you try to drop the role while the schema is owned by it SQL will produce the error, “The database principal owns the schema in the database, and cannot be dropped”.

In this particular instance we only had one schema this role was tied to but if a user or role was tied to many you can run the below query to determine what all schema’s are tied to the user:

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘testname’)

The next query will transfer the schema’s you want to move over to dbo for example so you can remove the role then re-assign out as needed:

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

As always I recommend testing locally or in a test environment prior to doing any type of prod related activity.

Database Auditing ~ Who Did What?

Leave a reply

Real life scenario ~ I’ve seen companies that I’ve come into not have their production environment on lock down and every one who is anyone has access to the environment.

I like the idea of having a table with the following columns:

  • AuditID – Unique Identifier
  • AuditDate – Date the event occurred
  • LoginName – Who did the event
  • ObjectName – What object was affected
  • Command – The command that was executed
  • EventType – What kind of event occurred
  • ServerName – The server the Event occurred on
  • DatabaseName – Which Database the Event occurred on

Once the table is created you can create a database trigger that will dump data into your table so you can track who is doing what in the database at any given time. I’ve used this practice at previous job sites and was happy to see this being utilized at my current employer.

The trigger will look something similar to this:

CREATE TRIGGER [tr_Trigger Name]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

SET NOCOUNT ON

DECLARE @EventData XML
SET @EventData = EVENTDATA()

BEGIN TRY

INSERT INTO [DATABASE NAME].dbo.[TABLE NAME]
(
AuditDate,
LoginName,
ObjectName,
EventType,
ServerName,
DatabaseName,
Command
)
SELECT
GETDATE(),
t.c.value(‘(LoginName)[1]’,’VARCHAR(255)’),
t.c.value(‘(ObjectName)[1]’,’VARCHAR(255)’),
t.c.value(‘(EventType)[1]’,’VARCHAR(100)’),
t.c.value(‘(ServerName)[1]’,’VARCHAR(255)’),
t.c.value(‘(DatabaseName)[1]’,’VARCHAR(255)’),
t.c.value(‘(TSQLCommand/CommandText)[1]’,’NVARCHAR(MAX)’)
FROM
@EventData.nodes(‘/EVENT_INSTANCE’) t(c)

–Note you can add a where clause and exclude “Stats” if you so desire”

END TRY
BEGIN CATCH
EXECUTE [DATABASE NAME].dbo.[SP TO CAPTURE ERRORS]
END CATCH

END

I’ve found this technique useful in running reports after deployments on what was deployed; going back for future reference on what was done, capturing issues among team members of not knowing what they were doing in order to correct it, and just for overall satisfaction of knowing what is occurring on the database itself.

If you’re not sure what is occurring on your databases then maybe this is something that you can try to help build a base to capture events that are occurring from drops, creates, to grants, etc.

 

Where Did The Time Go?

Leave a reply

A lot has been happening over the course of the last month. It was very nice to take a much needed vacation with the family, upon coming back our team has had the opportunity to upgrade several of our SQL Servers to 2012 (which I’m thoroughly excited about).

While I was out, SQL MVP Chris Shaw, had the opportunity to host T-SQL Tuesday #30 where he discussed Ethics and the DBA. I felt it prudent to blog on this even though I missed the timeline as it seems to be a pretty important topic that will always be around with a lot of opinions.

After reading SQL MVP Chris Shaw’s post, I always had this preconceived notion of what I deemed ethical in my day to day activities as a DBA. I have my own standards that I set high for myself and strive to maintain; have I ever given much thought to a set of authoritative rules for the whole DBA governing body?? It has crossed my mind but like others I think how in the world would one police such a thing and who would be responsible for it?

The institution I work for very well could have a totally different set of standards than the institution you work for so how would that play out? No, to me, the situation is so diverse I don’t see a certain set of ethical standards being derived for DBA’s in general, but I do think that every DBA should have a very high set of standards in place for themselves even if the company where you work does not have any in place. DBA’s in general are responsible for so much and are such an integral part to a company that if standards aren’t in place I truly believe can cause severe damage.

After reviewing the post(s) by so many others I found it interesting to see everyone’s points of views. DBA’s are a brotherhood, we are a close knit community. We have high standards for ourselves which is the way it should be. Take pride in what we do and give it 110%.

To review the SQL Tuesday #30 wrap up on Chris Shaw’s site you can go  here.

Drop and Create Databases ~ SQLCMD Style

Leave a reply

Sometimes there are instances when I need to drop 6 or 7 databases on my local instance. Currently, I run 3 instances locally, two are for OLTP duties and one is for reporting. One method for dropping the databases that I like is based on just a couple of SQL files and a .bat file. First, I make sure that my replication is turned off on my instances. If replication is not turned off then the below process will error out when trying to drop the database.

SQL FILE CREATION FOR DROPPING AND CREATING THE DATABASES

In my case I need to drop multiple databases so my SQL file resembles:

DROP DATABASE db1

DROP DATABASE db2

DROP DATABASE db3

DROP DATABASE db4

DROP DATABASE db5

DROP DATABASE db6

DROP DATABASE db7

DROP DATABASE db8

Once complete I create a generic directory on my C:\ and saved this file as DropDatabases.sql. The create database script is the same script just replace the drop with the create. Keep in mind on the create database statement you can add more setting defaults to the script but I will not go into those here. You can also tinker around with this script and add some standards such as checking to see if the database exists before dropping and creating etc. This is just an idea up to you on how you use it.

CREATING THE .BAT FILE(s)

I use the sqlcmd utility in my .bat file and make calls to the server instance and specific file. Simply open up a notepad editor and type the following command(s)

sqlcmd -S (local) -i”C:\CI\DropDatabase.sql”

sqlcmd -S (local) -i”C:\CI\CreateDatabase.sql”

Then save the file as DropDatabase.bat and CreateDatabase.bat

The two statements will call either the DropDatabase.sql file or the CreateDatabase.sql file

—————————————————-

I believe the SQLCMD utility is a pretty useful tool that I will eventually incorporate into my continuous integration initiatives. For those of you who know me or follow my blog for a long period of time know that I am Red-Gate fan. Because of this I have many useful tools at my disposal and currently am going to set up a form of CI following some of their practices which can be found here

MSDN provides a thorough listing of SQLCMD syntax and variables you can view here

I enjoy trying different ways of doing things that help daily performance initiatives. Hope this helps someone else in the future.

To Document or Not To Document ~ That is the question

Leave a reply

Recently, I approached a person in a business unit and asked if they had some standard documentation on one of their processes. The reply I received in general terms was, “No we do not have any standard documentation but we do continue to build on our processes”.

Knowing how I am, I began to read into it and I couldn’t get passed that statement. Then it dawned on me, of course everyone is different and we all have different mindsets. My DBA processes I feel must be documented, but others will not necessarily share that same view point – especially in other business units.

I’ve always heard that documenting is an age old battle; most people I know don’t like doing it but for me I do believe it is a necessity. I’m not the best at it but having standards in place within a team or solo act is beneficial for the next person that comes in. One of my old mentors several years ago told me if he walked out to lunch and something happened to him he would want me to be able to pick up a process even if I didn’t know it and look at his documentation and be comfortable in completing the task. Somewhere along the way that kind of stuck with me. Some of the standard documentation we have put in place for the DBA team that I’m on are:

  • Backup Procedures
  • Job Retention
  • Security
  • Maintenance
  • Server Installations
  • Code Promotions
  • Object Naming Conventions
  • Job Notifications

The list could go on but those are some high level ones that I just threw out there. Documentation made it on SQL Server Pedia’s “10 Things Every DBA Should Do” – the article came from John Sansom who really has a great blog; I suggest you check him out.

One of the things I like to do when starting new employment is to look at their documentation; some cases it doesn’t really exist. I like to take that and use it to gain knowledge of the systems and what they do. Documentation……”To Document or Not To Document ~ That is the question” To me it is a no brainer.

Drop me a line and let me know what your thoughts are if you think documentation is important or not important and some standards that you may have in place.

Someone Forget to Logoff that Remote Desktop Session?

3 Replies

 

Did Someone Forget To Logoff?

Phone rings….. alerts start going off……… coffee hasn’t been made yet….. it’s 2:13 a.m. and the issue requires you to remote login to the server to perform some task; only to find out that the server has already exceeded the maximum number of users. Has that happened to you before? Has me; either other people are on the box looking at something else or someone forgets to go ahead and log off when they are done using the box.

When this occurs I like to use the QWINSTA\RWINSTA operations from the command line to help me identify who is on the box and then graciously log them off. I know there are other methods to complete the same task; however I tend to roll with this one as it is engrained in my head.

To display information about Terminal Sessions you can type the following command in the cmd prompt:

  • qwinsta /server:[servername]

This command will give you the session name, username, id, state, type, and device.

This information is good however; how can you get someone off the server? That can be accomplished by using a similiar cmd line; howerver I DO RECOMMEND that you check with the individual prior to just booting them out unless it is an extreme emergency

  • rwinsta /server:[servername] [sessionid]

You need to make sure that you capture the sessionid that you saw from the qwinsta function in order to log them off.

I know Windows Administrators go through the Terminal Services Manager route but if you’re like me and enjoy some good command line based tools this can be another option for you; especially when you are woken up at night and the other people logged in could be fast asleep!!

SQL Saturday #122 – Pre cons

4 Replies

Just a reminder of the pre cons that are available this July 20th, 2012. Kevin Kline will have a session on Leadership Skills for IT Professional, Bill Pearson will have a session on Practical Self Service BI With PowerPivot, and Dave Fackler will have a session on SSIS – Live it, Love it, Learn it.

Cost is $100 per pre con session

The main event will be held July 21st, 2012 at the University of Louisville School of Business

University of Louisville

2301 S 3rd St

Louisville, Ky 40208

You won’t want to miss this event. Alot of good industry speakers will be at your disposal; visit the  main site to register today.

Are you a fit DBA?

6 Replies

Over my career I have seen many articles on what it takes to be a DBA. I always enjoy reading other viewpoints on the topic; so much so I’ve wanted to share my mentality on how I attack being a DBA. Every person is different and unique in their own way; however some of the principles, I believe, can remain the same.

How does a DBA stay on top of his/her game?

For those that know me individually know that I enjoy playing the game of basketball. I grew up with it; played up through college and once college was over still delve in it for fun. I can remember putting in countless hours in the gym shooting thousands of jump shots, running hundreds of laps, waking up at 4 in the morning to work out then going to class, then back to practice after classes. The reason I trained so hard was to be the best that I could be and squeeze the best out of my potential.

The other day as I was driving down the road i thought about my career as a DBA really is no different. In order for me to reach my goals and to stay on top of my game and enhance my skill set is to condition myself in the realm of DBA expertise. How do I become a better DBA? To me I need to take my conditioning mentality and make that into a SQL conditioning mentality. As I stated before each person is unique so your goals might be different than my goals but the underlying base is similar in many ways.

TRAINING YOURSELF

In any training program you have to continually work at it. My jump shot didn’t just happen over night; I couldn’t even begin to tell you how many hours I put in at the gym to where it became second nature to me. It was no longer thinking of the mechanics “Step, Bend, Follow-Thru” it became automatic. To me why would my SQL training be any different? I took 5 (my magic number, yours may be different) and looked at those 5 in ways that I can improve them. Then guess what; training begins! Train yourself and keep training. Just because you train for a day here or a day there means you will improve. You have to keep at it and train regularly just like you would when you train for a race, lifting weights, or in my case putting a ball through a hoop.

EAT HEALTHY

Part of my training consisted of eating healthy, and eating certain foods before big games to maximize my energy. Same thing goes for my SQL Skill sets. You will see on this blog many various bloggers who I look up to. To name a few of them:

The list could go on but these guys who I look up to I try to gain as much knowledge as I can from them and be like a sponge. They provide a plethora of information and you know what….I found that all of them are as eager to help as I am eager to learn. The SQL Community is abundant with tips and resolutions from such sites as:

To grow in this industry take advantage of what is at your disposal. Chances are issues or questions that you may have will be answered by someone or some form of partition in the SQL Community. That is why I enjoy the SQL Community so much; it is one big family. Eat knowledge regularly; grow your knowledge just like an infant eats and grows as he or she gets bigger.

Maximize Your Abilities ~ Fourth Quarter!

In high school (4th quarter) and in college (second half) I had this thing mentally I would do. I’d come out and slap both hands on my chest and then smack the floor. By this time I was dog dead tired; I had pushed myself as hard as I could the whole game and then I had to find the strength (where conditioning comes in) to make it through this quarter or half and stay focused when I was tired.

Have you ever pushed your limits with SQL? If not then how are you growing? I challenged myself and I will challenge you; push your limits and get out of your comfort zone. Learn something you didn’t know before; add some skill sets to your arsenal. You can’t expect to go out and knock out every inch of transactional replication if you’ve never seen it. Think of it in terms of taking small baby steps, but keep moving forward.

HELP OTHERS

I could chalk this up to getting older, the longer I’m in the SQL industry the more I enjoy helping others along their way achieve their goals. Take advantage of the many forums out there; share your knowledge; who knows maybe one day you can help others as they have helped me. I think a lot of times it is easy for us in the industry to take for granted the ones who are at the forefront such as the Grant Fritchey’s, the Paul Randal’s, the Glenn Barry’s, the Chris Shaw’s, the Brent Ozars, and the list could go on and on. These guys get it and have worked hard so that people like you and I could also get it.

As I close this thought, I recently read a post from Paul Randal. I can’t find it now but in essence it spoke of a request he received for advice and the request didn’t even say please or a thank you. I implore you to not only challenge yourself but also remember that a lot of times these guys help us for free. A simple please and thank you can go along way. Take the time to thank them for what they do. You may have others that you follow or mentors ~ have you thanked them lately for their advice or for being a sounding board as you maneuver through your career. If not think about it.