70-443 Chapter 1

I will be giving a summary of most of the sections of each chapter. Not all information will be in the summary but the things that I think are most important will be included in my summary. Keep in mind that if you are studing for these certificates that you should go get the book and do the work. It will pay off for you. These are just some of the highlights.

Planning a database server infrastructure

Server sizing can be done in a couple of ways and it really depends on the situation that you are in. Server Sizing refers to more than just the size of the database. This includes the hardware of the server. Procs, Network, Disk, Memory…etc. If you are lucky and you are looking to replace an existing server, or you have the money to build a full test server and perform realistic load tests, then you can gather performance data and baselines to help make your decisions. If you are not so lucky then you will want to do interviews with Management and perhaps the support team of the app that the database server will be supporting. If this is a third party application then hopefully they have done the due diligence and can give good guidelines on what hardware you will need and what type of load and growth to expect out of an organization of your size.

I think that I liked the section on disk the most. It talks about different types of disk, SCSI, SATA, and so forth. It also talks about disk configuration and describes why certain raid levels are better than others depending on what your database transactions look like. I found it very interesting that a RAID 10 was the best for writes and reads and that a RAID 5 was so bad for reads. That is going to make me re-think the way that just about all my servers are set up.

MCITP Continued

I am still working on getting my SQL Server 2005 MCITP. I am on to the second exam, 70-443, and I thought I would blog about it as I study and go through the book. All of the entries related to this will have the tag of 443 if you would like to refer back to them and read the series. I am using the books that were supplied in the MCITP Self-Paced Training Kit (Exams 70-431, 70-443, 70-444): Microsoft SQL Server 2005 Database Administrator Core Requirements: Microsoft(r) SQL Server … Core Requirements (Pro Certification)
and I really did like the first book. I think that it did a good job of helping me prepare for the first exam. I hope to get the second exam done soon and move on to the third. I really think that posting about it here as I go will be a good way to better retain what I am reading. I am downloading SQL Server 2005 Enterprise right now fom my Technet account so that I will be able to go through all the examples and practices. On the first exam I learned that you must know the theory but you must also know how to apply your theory to real problems. Following along in a SQL Server and doing all the practices will really help me get this down. Well I hope that this helps me learn more and pass the exam and also hope that you will follow me and learn something as well. After I get the 2005 cert I will be moving forward and attempting to get my 2008 MCITP as well.

PASS SUMMIT 2010

It has been confirmed. I will be at the upcoming PASS SUMMIT in November. I am very excited to get there and learn some new things and meet some new people. I also have my first SQL Server Users group coming up early next month. All very exciting to me. Now I really need to get moving on my certifications. I do not want to be the farthest behind at the Summit. Hope to meet some of you there.

DBA Roles

I have a scenario that I would like to talk to you all about and get your take on what should be done. I am a Systems DBA. I am not a Developer. Lets say that in the not so distant past my team of System DBA’s got a phone call from a developer in a panic. They needed a restore and needed it now. Something happened to a production Database and a site that is mission critical was down. Of course nobody had any idea what happened or who did it. I looked into doing a restore and guess what. No backup had ever been done on this Database. All of the other Databases on the instance were being backed up but not this one. What could have been going on here?
Well let me give you a bit more of the back story. In my company the DBA group, my group, is responsible for Backups, Restores, Indexing, etc. We are also in charge of migrating any database object into production. This is meant to keep developers from developing in production. They all have test instances and they are all sysadmin role in those instances so that they can do what ever it is that they need in order to develop. Well, we have one group who has convinced the right people that they, the developers, need full access in production as well. They are too important to the business to be slowed down by process and that will cost us money.
Now that you know that, let me explain why the database was not backed up. The backup job was put in place before this database was created. Now, creating a database is usually up to my team, but since they all have sysadmin role they created it and did not tell us. They also did not set up backups to run against this database. They also were developing in production on the day that it all went down. Millions of rows were lost, database using simple recovery, and no backups. Then they blamed my team for not making sure that the database was backed up.
This brings me to my question. Since I am not a developer I would love to hear from System DBA’s as well as Development DBA’s on this. What should the seperation of roles be here? Am I crazy to think that the System DBA’s should not allow development and creation of databases and database objects in a production database by the developers?
I would like to tell them that either we lock it down and the System DBA’s will migrate all Database Objects and do all Database creation in the future and at that point we will be responsible for all that a Systems DBA should be responsible for. Or, We can give them all SysAdmin role and we will do nothing. If they want to muck around in it then they deal with the fallout and not us. Does that sound right to all of you? Please let me know what you think.

Android Dev…. Why Not?

I know that I have so much to learn about Databases and SQL Server, but man I get tired of focusing so much on one thing. I have many things in my life that I enjoy. First is by far hanging out with my wife. But I love technology and I love my Android phone. Since I enjoy my phone so much I thought I would take on a side project and try to learn how to develop for Andriod. I got online downloaded the SDK started reading and decided I would like to get a real book as well. I searched around and I finally decided on:
Professional Android Application Development (Wrox Programmer to Programmer)
I picked this book because of the reviews, and I really like books by this publisher. I have read a few others and they seem to make the reading interesting. Not as dry as many technical books. I have not completed the reading but so far I think it is very good. It is a bit dated, since Android is moving so fast, but still good and pertinent.
I do not really know what I would like to develop at this time, I just think it will be fun to try out. I look forward to developing, even if it is an app that only I use, for Android. Any one got any app ideas? Keep in mind that I will need to keep it simple at first.

Health Check into SQL Server

I have recently posted about my new job and how I am walking into the unknown. Like I have said in the past, I did get a list of Host Names that are running SQL Server. I have written a script to go out and look at each of those hosts and get me all instances. I have put together a list of about 150 SQL Servers that are running but I still no very little about. I have also posted about a Powershell script that I have written that goes out and gathers information about the SQL Server instances in question. I ran this and I was shocked. After glancing at the output I see things that just should not be. I see many DB’s that have logs larger than the Database. I see many that are running from the system drive, I see some that are far behind on patching, I even see one with only system databases on it. Like I said I have about 150 identified running instances and just over 1000 databases. You read that correctly over 1000. I just really do not even know where to start.

Because I have so many servers and so many databases, I thought that a simple Excel Spreadsheet is just not going to do it for me this time. I need more than just a simple report. I want to put all this data in a database so that I can slice and dice the data more easily, when I finally figure out how to prioritize what needs to be done. So here is what I have so far. I will use the same Powershell script to go get all the data that I am after and then I will have it insert the data into the database. This is just the beginning for this database as I have some bigger plans brewing in my head now. For now though, I just need something quick and basic to get the data into so I can prioritize and identify the largest problem areas.

I have two tables consisting of the following:

I think that this will give me the ability to look at the basics and identify major problems. As I get things taken care of I will then start to add more to this Database so that I can do some more tunning, baselining, trending, and so forth.

Got any thoughts on what I should add to this initially. I am all ears.

More fun with MS SQL Server

I have a customer who would like to have a couple things. They would like to have, above all, HA. Secondly they would like to be able to configure the application to write to one database and read from another. They want to do this to boost performance as they feel that IO may be an issue. I suggested to them that we do and Active - Active Cluster and in one instance we will have the full writeable database, and in the other instance we will have a copy of the database that is maintained by using transactional replication. This should give them exactly what they are looking for. I am excited that the customer also likes this solution and would like to get going on it ASAP. We do have some things to consider here before we can start. One big thing is that they do not really want any downtime, SHOCKER, while setting this up but the live database is currently sitting on one of the servers that will need to be a part of the cluster. I am hoping that we can move the database temporarily, do all the re-architecturing that is needed and then move the database back to the cluster. Once I get that done then I will be able to set up the replication.

I will post more about this project and how I get it done as I actually get to do it.

SQLIO

Just the other day I was asked to prove to a user that our SAN disk would be faster than the servers Local Internal Disk. Now I am no SAN Admin but I know that in most cases the Fiber attached SAN is going to be faster than the internal local 10,000 RPM drives in my servers. Well thanks to Brent Ozar, once again, I was able to use SQLIO to run some tests and prove that the Fiber Attached SAN outperforms the local disks, at least in my case.

I watched and read the article that I have linked above and I ran SQLIO on both the internal and the SAN disks. I used the same parameters for each test. Both ran for about 18 hours since I did what is suggested and tested with multiple parameter values.

After the tests were complete I had about 90 pages of output for each test and was asked to come up with a summary of results to present by the next day. I was a bit worried about this. In Brent’s post he talks about pulling the Data into SQL Server and I am sure that is a great way to handle it. I do not have a server set up at this time to put this data into and, I am sorry to say, I am not comfortable enough with writitng SQL to do this and slice and dice the data in a short amount of time and I was up against a deadline.

I decided to use Powershell to sort the data for me and pull out only what I needed. I had Powershell put this into a csv file so that I could then manipulate the raw data in Excel. After I had it in Excel I was able to make some conclusions, Reads on the SAN were almost 80% faster and the Writes were almost 30% faster. Now SQLIO does not simulate SQL Server behavior, but this proves to me that the SAN is faster at the most basic level. If you would like to simulate SQL Server than you can use SQLSIM. I have looked at it but I have not dug into it much so I can not really say much about it at this time. Just something else to learn.

Disneyland

So, my company has asked all employees to take a week off. Sounds awsome but they do not want to pay us for that week. Ok so they did not ask, they told us to take a week off. Anyway, I thought what better to do then go to the happiest place on earth to forget about the unpaid week off I had. My wife and I spent two days at Disney, one day was a special event through her work. It was great, almost no lines both days. We waited at most about 40 minutes for Space Mountain. Other than that I bet we did not wait more than 10 minutes for anything.

This was my first time going in about 20 years so a lot has changed. First time that I was able to go to California Adventure and I thought that was pretty cool as well. Loved the Tower of Terror.  If you are planning a trip I think that this is the time of year to go.

Now that I had a great time at Disney I am back to work and knee deep in it again. I am still working on getting my health check script to work against all of the instances that I have identified. Also have a few servers that need some TLC real quick. Looks like some new instances without any backups running and Databases are using the Full recovery model. Need to get those backed up before the logs grow out of control. It is good to be back to work.

I also am going to start hitting the books hard again. Need to take my second test so I can move on to the third. I really want to get the 2005 certification out of the way so that I can get started on the 2008. I would really like to have both of those before the end of this year. Something else to add on to the list of things to accomplish in 2010. I know Disney was not on the original list, but at least I got that done. ha ha ha….

PASS Summit

I am trying to get my company to pay for me to attend the PASS Summit 2010 in Seattle Washington. I am still pretty new in the world of Databases but I think that attending the Summit would be very good for me. First I might get to meet some of the people that I read daily and respect so much. That would be really cool, but I think more importantly than that is getting to know people like me that have their hands in the trenches everyday. I would love to hear the horror stories about Database Corruption and how they recovered, or perhaps a Disaster and recovery. I prepare for DR all the time but have never, knocking on wood, had to go through one. Listening to real problems and how to deal with them is what I am most interested in. Especially performance, I have not really been able to get to deep into performance tuning but man I want to. I think that the presentations at the Summit will be invaluable to me as well.

I just sent my boss a breakdown of the cost to send me and it was just over $2500.00. That is a lot but not really when you think about it. I am pretty much the only SQL Guy at the company and I am still new but I have identified over 140 instances of SQL Server running in the company. I am still attempting to make sense of all of them and get my basic health check script working on them all. But I have to say, with that many instances and all that data, I think it would be a good investment to send me to get more knowledge and meet people that have more knowledge than I do. I have my fingers crossed and I hope that I will see you there.