Contains posts related to SQL Server administration, best practices, design, and code.
I see a lot of question on the forums about migrating to new servers. At all the companies I have worked at, we have implemented server build processes to help with this process and also document needed DR procedures. For this post, I’m including a generic SQL Server Build Process and some nice scripts I keep on all the servers to assist in the process of migration.
SQL Server Build Process
This document will be used to build all SQL Server Servers and instances at . All new SQL Server installations must follow this...
We are now running full-speed with SQL Server 2005 at work. I have my team testing the new features and spending part of each week learning the technology. I've been telling everyone how much there is to learn. If you are a database manager with SQL Server and do not have your people ramping up, you should be shot (2 cents thrown in there). They are currently focusing on reporting services, SSIS, and analysis services, as those represent the major push my department will be making over the next 15 months. It'll be interesting to see how we can effectively...
I've now installed SQL Server 2005 about 18 trillion times since the first beta came out. :) Seriously, since I have been trying to take all the free webcasts, training, etc that's out there and ramping up my skillset on the new technology, I have completed around 25 installs of the product. I am currently installing three more instances of the June CTP on various laptops and computers at home. So.......I thought I would start recording notes on the installs here.
This will probably end up like all my other blogs (rarely updated and very seldom read). lol But, in the...
As we all know, SQL Server SP4 arrived on the scene late last week. It’s been a LONG time since we’ve had a major service pack release for SQL Server 2000, so this service pack is very large. If you look at the release notes, the service pack fix list includes 285 fixes for SQL Server and an additional 90 fixes for analysis services. You can find the complete fix lists, along with release notes and the service pack downloads here:
Here are some snippets I noticed in the release notes and fixes that...
Microsoft has set up virtual labs for people to train on their new technologies, including SQL Server 2005. You can find them here.
The labs I have taken so far are really good for an introduction to SQL Server 2005 and Visual Studio technology. These are Windows 2003 virtual servers with the latest CTP of SQL Server 2005, Visual Studio, etc installed. In addition to following the manual, you can play around a little during each session. It lets you experiment with 2005 without actually installing it on your machines, which can be a real treat if you don't have VMWare, Virtual PC, etc. ...
In this world of third-party insanity, I'm constantly amazed how much companies spend for third-party applications that are written like crap and have ZERO security. Lately, I've been plagued by a series of third-part applications using the sa username and password. When you ask them WHY, they get angry and explain that's how the applications were designed. When I politely explain to them that they are idiots, they don't seem to comprehend WHY. So, I'll say it again.....YOU'RE AN IDIOT!!!!!
Magic Helpdesk Software (http://www.remedy.com/solutions/magic/)
Focusing on the...
I feel like posting today. :) We get asked about datetime formats a lot on the forums. Here is a simple little script to tell you what formats SQL Server supports using the CONVERT function.
SET NOCOUNT ON
CREATE TABLE #results( conversion INT, result VARCHAR(55), code VARCHAR(255))
DECLARE @min INT, @max INT, @date DATETIME
SELECT @min = 1, @max = 131, @date = GETDATE()
WHILE @min <= @maxBEGIN
IF @min BETWEEN 15 AND 19 OR @min = 26 OR @min BETWEEN 27 AND 99 OR @min BETWEEN 115 AND 119 OR @min BETWEEN 122 AND 125 OR @min BETWEEN 127 AND 129 BEGIN GOTO NEXT_LOOP END
INSERT #results( conversion, result, code)
SELECT @min, CONVERT(VARCHAR,@date,@min), 'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(@min AS VARCHAR(5)) + ')'
SELECT @min = @min...
Physical Database Security
· Move a SQL Server out of DMZ (this one was ticking me off). --Completed.
· Create new VLAN's for SQL Server and migrate servers. --Completed.
o Created four VLAN's to provide separation of database servers on network.
o Separates production, development, third-party, and back office.
o Allows separate rules governing activity and access security at a group level.
· Implement SQL Server Firewall --Not Started
o Will review security and firewall policy at later date.
o If current security is not sufficient for business owners, will create database firewall.
· Server Consolidation --In Progress.
o Had over 30 servers, which is not manageable. An environment that...
I know this is a little late, but I wanted to finish it up for my own purposes. The final day of PASS was incredible. Anytime you get a chance to listen in on Kimberly Tripp you should take that chance and remember it. This is how a PASS presentation should be.
Very Large Databases with SQL Server 2005 (by Lubor Kollar) -- **GOOD**
The presentation by Lubor was a high-level presentation that led really well into a lot of the presentations at PASS. He did a good job of explaining how the engine works to determine locks. He also explained the threading/fiber...
WOW....what an interesting last couple days. I was priviliged to attend a couple incredible meetings. I was unfortunate enough to attend a session that was not so great (cough, cough)!!!!! Such is life in the world of conferences. :)
SQL Server Locking Internals and Troubleshooting - **Good**
This session covered a lot of the tools and techniques used by PSS in troubleshooting issues. He went really into depth on how scheduling, threading, fibers, crabbing work. SQL Server 2005 is changing the way threads are allocated. By allocating them to tasks, we will be able to get better management of available threads. It also...
High Availability with SQL Server 2005
This was a great session. It was cut a little short by an EMERGENCY EVACUATION!!! Go figure. :) It focused on the new enhancements that SQL Server 2005 has added to provide for greater flexibility and implementation of high-availability networks. For those who are members of PASS, the slides can be downloaded from www.sqlpass.org when the conference is over. Here are my takeaways from the session:
Covered Database Mirroring.
Can be a long ways away.
i. Have to keep transactions in sync though, which can cost performance.
ii. Don’t need to be on compatability list like...
Just thought I'd give a brief rundown of what's going on at PASS. I just attended the keynote address and my first session. The session was Developer Productivity and Server Extensibility with SQL Server 2005. The session covered many of the concepts in 2005 that involve CLR integration and benefits to development from this the extensions to the XML datatype and associated tools.
Each session I attend or book I read just enforces the fact that 2005 is by far the biggest “learning curve” upgrade since the advent of SQL Server and it's migration off the Sybase framework. It will...
This recently came up as a discussion on the www.sqlteam.com forums. I posted the options below and Kristen suggested I post it in my blog, so here it is. :) This is by no means meant to be comprehensive. I would be interested in the feedback people have. Is there interest in actually having an article describe in detail several options? Does anyone have any options to add, or other options they have implemented?
1. Active/passive cluster--This provides you with server redundancy, not "drive" redundancy. If one server fails though the other picks up.--The applications need to be cluster aware.--Cheapest to...
This is something I see posted continuously on various forums. Some fine, young idiot on one forum yesterday said the solution was to just detach and attach all the system databases. That doesn't work in case you 're just itching to try it.
Luckily for us Oracle, I mean Microsoft, has done a fine job though of outlining a solution for this very issue (hard to believe I know). Every DBA should take this link and store it away in their little toolbox of bookmarks:
Why would you want to do this?
Many people, myself included, like to keep all the databases in a...
I got to attend the second part of a Yukon review last week that was really informing and interesting. It covered some of the basics which I had already seen, such as the additions to Transact-SQL (pivot, apply, etc). The demonstator had a post-beta1, not quite beta2, version of Yukon, so we also got to see several new additions and many things that had been refined.
One of the exciting new features I really hope makes it to the final release is the ability to script commands in the Workbench. Any command you run from the Workbench such as backups, restores,...
While installing the EMC SnapView software, we had a problem with our LUNs not “anchoring”. We clone the drives, then use mountvol to mount them. We then change the LUNs to the appropriate drives for our SQL Server instances. The problem was that the drives would move everytime we rebooted the machine, which is not something you really want happening on a SQL Server installation (or any other type of installation I can think of at the moment).
After EMC researched the issue for several days and discovered they really had no idea what they were doing, I found out that...