SQL

SQL Class

Found a nice article on some basic SQL. Check it out, it is never bad to have the knowledge even if you are not a DBA or SQL Developer.

 http://searchsqlserver.techtarget.com/ti…

Powershell SQL Load Script

In my environment we have some servers, test servers, that are loaded from the Production Servers on a regular basis. Since this is the case I have decided that I would like to attempt to script this. I just started but I am already well on my way to getting this done. I am pretty sure that I will be able to do what is needed here, I just need to take it slow and work through the issues as they arise.

So far I have been able to have the script list all the instances that may need to be loaded and ask what instance you would like to load. I select the number next to the instance, try to avoid typing errors, and the script then determines what host this instance needs to be loaded from.  So essentially I have done all of my variable setup at this time. Here are the rest of the steps that I have in mind for this script.

Once I know the host and the destination servers I will connect to the destination and delete any backups that I might have on disk. I will not need them anymore since I am loading from Prod anyway. I will then check to find out the amount of free space that I have on the disk and compare that to the size of the production backup file. If I have room then I will copy the prod backup to the test host. Once that is complete I will lock all logins, kill any spid in the target DB, and then load the DB. After that is complete I will have to deal with users in the DB and making sure that I can match them up with the appropriate logins. Then I will make the DB available to users again by enabling the logins.

Simple enough plan but I am sure that I will run into some hurdles along the way. Should be a fun script to do though. Any thoughts or ideas about this script please let me know.

SQL Server Certs

I have been talking about what I would like to do to further my education and my carreer as a DBA. I started reading the books in order to study for my MCITP DBA certification and the other members of my team decided that they would like to do the same. They both ordered the books as well and now we are going to attempt to get the certification as a team. We just started working as a group on this and we are doing a 3 hour study session per week and trying to get three chapters of the book done per week. We read the chapters on our own and then we can work the problems, case scenarios, and the examples in the book together. This is great since if any one of us has any questions we can talk about it and make sure that we all understand the concepts.

I think I will gain the most out of this arrangement since I am the new guy still and they have a lot more experiance than I do. I recommend trying to do this type of thing in a group for a couple of reasons.

1. For me this will really push me forward and I will be more driven to get it all done

2. I work better in a group and if I have others that understand concepts I learn best from them.

SQL Server Education

In an earlier post I said that I was about to start reading some books from the advanced Sybase Training course. I have changed course since then and I have decided to move on with some SQL Server education instead. I have decided that I would like to get my MCITP: Database Administrator certificate. I have never really felt like certificates were very meaningful while I was doing workstation and Windows administration. I have changed my views of certificates when it comes to the Database world. I have decided to tackle SQL Server first for a couple of reasons.

1. It is a MS Product and has a nice GUI. I come from a Windows Administration background and feel that this is the logical course.
2. My company has roughly 50 production SQL servers that we really do not do much with. I have written a Powershell script to monitor the SQL Servers for errors but we need to do more.

I have already started my journey and have finished reading my first MS SQL Server book. I read Beginning SQL Server 2005 Administration by Wrox publishing. I enjoyed the book and I ordered the next in the series called Professional SQL Server 2005 Administration. I like the way the books are laid out and have hands on examples that you can try if you have installed the AdventureWorks Database.

I have also ordered the study material for the three tests that I will have to take in order to get the certification. I am hoping to get these books in the mail in the next day or two. I would like to try to take one test a month and have my certification in about three months. I am looking forward to doing more in SQL Server and have learned a lot just from the first book. I will continue to keep you updated on this and hopefully you can learn along with me.

If you have any questions please head over to http://sysadminsmith.com and click the submit a question link to the right.

Sybase Kill Runaway Process

I am working on a stored proc that will be launched by a threshold that I set up on the log segment of my databases. It is a bit of work but I think that it will be nice not to get the page that someone has written some poor SQL and is filling up the log segment. I will not be running this in Production but I will in all other environments. I have started the proc and it is a work in progress at this time. I am pretty new to SQL as in my position I do not write a lot of SQL and this is one way that I am trying to learn. Here are the guts of it so far. It is getting me almost all of what I need. Now I need to figure out how I can get just the top row of the resultset and then kill that spid as that spid is the one with the most IO. Anyway here it is and I will let you know how it is going and when I have it complete.

Just in case you are wondering, The sp_threshold will pass the @dbname variable.

USE sybsystemprocs

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

BEGIN

DROP
PROCEDURE dbo.sp__killrunaway

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< FAILED DROPPING PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< DROPPED PROCEDURE dbo.sp__killrunaway >>>’

END

go

create
procedure dbo.sp__killrunaway

(@dbname varchar(30))

as

declare @dbid int

select @dbid = dbid from master..sysdatabases

where name = @dbname

select hostname, a.spid, b.name, physical_io, c.name

from master..sysprocesses a, master..syslogins b, master..sysdatabases c

where c.dbid = @dbid and

a.suid = b.suid and

a.dbid = c.dbid

order
by physical_io desc

go

EXEC dbo.sp_procxmode ‘dbo.sp__killrunaway’,‘unchained’

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< CREATED PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< FAILED CREATING PROCEDURE dbo.sp__killrunaway >>>’

go

_

Learning SQL

I have been a DBA for about a year now and I still have not had to really do much SQL. I do other things such as backups, restores, indexing, and other things to make sure that our databases are in good shape and that our data is safe. I thought it would be a great idea to learn some SQL though so when a developer complains about a query running slow I can take a look at the query and understand what it is doing and perhaps help them re-write the query so that it will preform better. I installed the AdventureWorks DB on my SQL Server Express instance that is running on my Desktop and I printed out a diagram of the database. I found a good site http://w3schools.com/ that has tutorials for many different proggraming languages and I have been working down the SQL Tutorial. I would not claim to be a developer but I have learned a lot and would recommend this to others that are trying to learn SQL.