SQL Server 2008 Event Just Announced

Stephen Wynkoop and I have been working on a “special event” for some time now. We have been trying to figure out how we can help get the word out about SQL Server 2008. The really exciting news is that we have figured out a way to do it. Everyone on the planet can join this Green online SQL Server 2008 Community Launch Event. We are really excited about this. This event will take place on October 24^th, 2008 and will not cost anyone a thing. The start time is 9 AM PST and is going to be a great way to open up the Fall Conference Season with SQL Connections, and SSWUG Conferences.

I will be speaking on some of the new features that are in SQL Server 2008 and Jason Strate will be discussing the new features in SQL Server 2008 SSIS. I don’t think this is going to be an event that you are going to want to miss. The cost for this is free, you cannot lose. This is going to give you some great information that you can pass on to others on why they would want upgrade to SQL Server 2008. In my opinion the best released version of SQL Server ever.

The day will finish with Jason and Stephen doing a candid sit down discussion, followed up by some wrap up notes. I really look forward to everyone being there.

SQL Server 2008 Tools review

I got some really exciting news today. I talked with Stephen Wynkoop, the founder of SSWUG and Microsoft MVP. It looks like we are going to get him to do an additional session. A review of some of the new tools that come inside SQL Server 2008, he will also be doing all the keynotes that went really well in the last VConference that were based on SQL Injection. The code samples that he showed in the keynotes were amazing. It one thing to hear about how people do it, but a completely different thing when you can see how this code damages your database so bad.

To top the day off I got to talk to Donald Farmer a BI Guru that really had a lot of great things to say. Many times you meet these people and when you talk to them about problems that you see on a day to day basis you worry that you are the only one that is going to see that problem. Let me give you an example. I ran into something funny with SQL Server 2008 last week. My system computer was showing that I had x amount of memory yet the SQL Server that I thought was installed locally was showing 4 times that. I thought wow, maybe something is really wrong here. I jumped up on the newsgroups on Microsoft and posted my question. I was really surprised when I got a lighting fast response from a friend of mine Kalen Delaney author of the Inside SQL Server books. After a couple of posts that went back and forth I figured out that it was completely on my side and that I was looking at another server via my client tools. Thanks to Kalen for the help. But did I feel stupid. I guess it just reminds me to stick to the details.

Well good day for now. Chances are you are going to see an exciting announcement from me some time tomorrow. There is some things that I have been working with and I think it will be announced in the next 24 hours.

SQL 2008 Here I come

I have loaded SQL Server 2008 CTP on my laptop a few times now. I used it for presentations during the June SSWUG SQL Conference. I even put the RTM on my Vista machine and had not a single issue. Yesterday around 4:00 PM I ran started to put the same install on my laptop to prepare for my sessions for the Oct SSWUG Conference and I ran into an issue. For some reason I was getting a .Net Framework 2.0a error. After reading a few blogs and MSDN articles I was not better off. I tried to repair my .NET Framework. Heck I even tried to install sp1 to it just to see if that would fly and I was stuck.

You know my last post I spent an hour writing about how we tell people that it is ok to reboot their machine. Well this is a lot along the same lines. After working on it until 2AM this morning I went to bed. I repositioned my mind in a happy place this morning and decided that it was time to build from the ground up. I removed all the .Net Framework installs and reboot my system then proceeded with this process.

• Installed .Net Framework 2.0
• Reboot
• Installed .Net Framework 2.0 sp1
• Reboot
• Installed .Net Framework 3.0
• Reboot
• Installed .Net Framework 3.5
• Reboot
• The fired away with my SQL Server 2008 Standard Edition.

So hear I am 24 hours later and I am up and running on SQL Server 2008 on my laptop running Windows XP Pro. I hope you want to read a lot more about SQL Server 2008 because I am starting to write my presentations now.

SQL Server 2008 Released

It’s been a busy last few days. We started it off with the release of SQL Server 2008. The excitement that comes from a major release like this is almost too much to contain. I know I am ready to start installing it and checking out how all the new features made it into the final release.
Well from what I am hearing you may want to wait a bit before you go and install it. From my understanding the best way to increase your chances of installing SQL Server 2008 is by starting from scratch on a new OS install. I have heard that there are issues with software that exists on the server before SQL Server 2008 has been installed and it is causing many problems.
If you have been able to get SQL Server 2008 installed with some success I would love to see a comment or two that shares with me the configuration that you had prior to the install.

SQL Server 2008 Gets Smart with IntelliSense

I found it found something interesting today as I was helping a client work through an issue with SQL Server 2005. See the client of mine is working on moving a bunch of code to Stored Procedures. The client is currently on SQL Server 2005 and I was showing them a couple of samples of code by using SQL Server 2008 because that is what I have installed on my machine. What I found interesting was as I was going though this we ran across the function in SQL Server 2008 that helps you complete your code. I believe the official name of this is called IntelliSense. I was explaining what it was and it dawned on me that they might not know that the Dynamic Help feature was there.

This brings me to the though process that there are still a lot of hidden gems inside SQL Server 2005 that a lot of people are not using or just are not aware that they are there. When 2008 is released in a couple of months we are going to be in the same situation.

As far as IntelliSense is concerned, if you have not seen it yet, check out the latest RC to see how it works. It may take a little getting use to for us DBA’s but for those people who have been using .net for a while will pick it up right away. Some of the neat things that it can do for you is underline what it believes to be bad syntax with the red you spelled something wrong line like in Word. However, I found that so far its not working with Inserts and Updates as well as it does the other SQL Statements. The best part about this is that it is installed on default. It’s not a feature that you have to go in and tune or set it up.

Cross Database Joins

An old friend of mine today asked me a question that I thought I should go ahead and post.  The question was; how do you run a join across databases.  Do let’s say for example you would like results to be a combination of sales from the Employee database and the Sales database (so you want to know who the best sales person is), but they are in different databases.

Good question but depends on the version of SQL Server.  All you have to do is hit it by the fully qualified name.   So for an example.  If you wanted to do this and both databases (Employee and Test1) are on the same server then you would do a join like this.

Select (Columns)

From Employees.dbo.Employees e

                Join Sale.dbo.sales s on e.id =s.id

So really the answer is no matter what the version you have to use a fully qualified name.  Here is the name pattern <LinkedServerName>.<DatabaseName>.<Owner>.<TABLE> now this is for SQL Server 2000 and older. 

 If you are working with SQL Server 2005 or newer then it changes to <LinkedServerName>.<DatabaseName>.<Scheme>.<TABLE>

There really is not a big difference here.  Just remember if you normally when you write a SQL Statement the defaults of the database you are in and DBO as the owner are used.  If you use one to the left of the Table name such as database name like this (Employees..Employees) I think it looks and reads the best if you still include the owner or the scheme. 

“Don’t forget to sanitize your data inputs”

I was looking at a friend’s IM message today and it said “don’t forget to sanitize your data inputs”. I got to thinking about how I will put up stupid NASCAR sayings just to poke fun at another friend of mine.  The more that I sat down and thought about it I realized the importance of the statement that my friend was making.  So the question is to why should I sanitize my data inputs, and what does that really mean?

When you create a stored Procedure the format you use will look like this:

Create Procedure ProcName

@Var1 (varchar)

AS

Select Name from PhoneBook

Where name like @var1

Go

If you are new to stored procedures the data input here is the “@Var1”, this is the place where the users request is going to be sent to the database.  For example let’s say that this stored procedure is called from a web page.  A user could select to see if there was someone in the database named “Chris Shaw” or “Bob Smith”.  Why would we want to sanitize it? Some people may think that we would be limiting the search results by doing this.

What is sanitizing? Well its making sure that any value that is inserted into that @Var1 is data that we expect it to look like.  An Example of this would be to make sure that there is not a ‘;’ in the var.  I am not sure I have ever seen anyone with a semi-colon in the name.  These non useful characters need to be pulled out and/or a message that this input did not produce any results.

Well the end results are this:

• Keep your data clean – The example I gave you was a search and not in insert, but if it were an insert the end result is that you don’t want data in your database that is not sharp, accurate and clean.
• SQL Injection – Many developers in the past and still today will put the SQL code into the web page itself. This has caused a lot of issues but ads a lot of flexibility, the end result is no matter where the input is; if you are not checking to make sure that data is clean you are at risk.

In the Red Trunks SQL Server 2008 In the Blue Trunks SOX

So a few years back there was this whole big issue with public companies not reporting the financials accurately. I am sure many of us remember this. There were a number of companies involved and well the end result was that these companies made it look like they were doing better then what they were. When the truth came to light the stock plummeted, and a lot of people who were invested ended up losing a lot of money, in some cases life savings.

Because of this a new Act was passed, more or less (and I am not a lawyer) this Act makes the CEO and the CFO of public companies responsible personally if there is an issue with the books not being done right. The boards of these companies started to hire firms to come in and do audits on everything that touched the revenue stream. The called it the Sarbanes Oxley Act or (SOX). And the CEO’s and CFO’s take a good look at this.

This is where this gets very fuzzy, I have been at some places where anything can touch the revenue stream. I have been at a company where only select applications touched the revenue stream. Yet they tested the security at the building.

More to the point though, SOX has been a big unknown cloud. I have been through 3 or 4 SOX audits and every one was different. I have even been to places where they have pre-audit companies come in to help with the Audit and they ask completely different questions.

Where I am going with this? Well the new Audit Features inside SQL Server 2008 are going to allow you to automate most everything that I have been asked about before. Questions like; when did all the accounts last have a password change. Or they can validate that the database has good backups or even when the last restore was done. This new Audit feature will even log when it has been changed.

The point that I am trying to make here is not only is Microsoft trying to make our lives easier by helping use with new features like compression or policy management, but they are also looking at what the industry needs at the time. We are being asked to provide audit info and SQL Server 2008 will help DBA’s like me get there.

SQL Server 2008, Why would I Upgrade?

For years I have been working in the Corporate Work Place, I think going on 15 now. The biggest challenge that I have faced over the years is how do I manage 200 SQL Servers? Microsoft has provided me tools along the way that include SQL H2, that I used, and Reporting Services that helps but out of the box they both require set up, and some custom code that will gather information about your servers. For anyone that has heard me speak, I talk a lot about Management and how to we get a grasp on managing servers. One well attended presentation has been SQL Server Checklists, what I should do on a day to day basis. I have wrestled with myself for years about how to do it.

• Do I do it manually the brute force method, to time consuming and to easy to forget or put aside doing checklists when I have a server down.
• Do I do it with SQL H2 – I could, but the process to set this up can sometime be a bit tedious when looking for performance counters, and then I am left with writing custom reports for me. Another issue that I found with SQL H2 was my service would stop sometimes and if my staff did not catch it I was left without information for days.
• Do I use Reporting Services – Well that was the direction that I went. And it is a good solution. It requires some coding and some work each time a server is taken off line, or a new one brought on line.

I have used all 3 methods in my management style, the one I have liked the best is Reporting Services, well that was until now. SQL Server 2008 gives me the ability to group my servers and to manage them together. I can run queries on all the servers at the same time along with dumping the results to a single point. From this single point I can create new reports, or run existing ones with a few changes.

So when people as me why I want to Upgrade to SQL Server 2008, I tell them. My life as a DBA is going to be much easier to find problems. It may still take time to get to the problem and fix it, but the big deal is that I can do this now with a lot less code in my trouble shooting process. In other environments I have been in I would hear that an order had not processed. Once I heard that I would have to trace down what server a job may have failed on. But it may not have been a job, it may have been that the order status was not set to process. Then I had to check inventory, and billing, and shipping, and so on. In 2008 I can run a query that will tell me all the failed jobs, I can run a query that will tell me all the order status and then I can put this in a table. I can create a report that will tell me what looks good and what does not.

SQL Server 2008 will make my life easier. It will give me the tools that I need to manage hundreds of databases across hundreds of servers. I believe that Microsoft is trying to make it easier to do my job and I thank them for that.

The Eve

Not much to say today, I am really excited about everything that is going to happened on Tuesday the 24^th. I have been the Conference Chair for the SSWUG SQL Server conference and the team has put in a lot of work. Many people don’t realize how much effort goes on behind the scenes to create a conference like this.

I am truly honored to be part of this through my career of public speaking this has been the most challenging experience that I have faced to date. There is a ton of great information that I am really excited to get feedback on. It was interesting to watch MVP’s do their work, speakers come up with ways to teach subjects so that people with different learning styles could pick up what was being taught. A big and special thanks goes out to all the speakers, all the attendees and all the staff at Bits on the Wire.

It has been my pleasure to work with some of the finest people in my career. I plan to post a day by day review of the conference. If you are registered, I will see you there (well virtually of course). If you have not registered, it’s not too late. Any sessions that you may have missed will be available on demand starting Friday at noon.

http://www.vconferenceonline.com/sswug/