Subscribe: Mean Old DBA
Added By: Feedage Forager Feedage Grade B rated
Language: English
database  good  install  new  process  release  security  server  servers  service pack  service  sql server  sql  test 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Mean Old DBA

Mean Old DBA

Ramblings, grumblings, and other nonintelligible nonsense about SQL Server.

Copyright: Derrick Leggett

Migrating SQL Servers

Sun, 04 Dec 2005 19:40:00 GMT

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 Policy Statement This document will be used to build all SQL Server Servers and instances at .  All new SQL Server installations must follow this process and be documented accordingly. Scope This policy will cover the entire enterprise in regards to SQL Server installations.  No installations of SQL Server will be completed without following this process.  This document does not cover the following: 1         Processes and policies for acquisition of hardware and/or software. 2         Process for physical server build or installation of the OS, drivers, etc.  IT has a process in place covering physical server builds.  All servers with SQL Server should be built according to the IT process coving SQL Server builds. Build Process   Installation   Remote into the server to which the new instance of SQL Server will be added Go to \\filshare\directory\SQL Server 2000 - Enterprise Edition (or CD/DVD) and run the autorun.exe 1         Install Components 2         Install Database Server 3         Install onto the local computer 4         Create a new instance of SQL Server 5         Install server and client tools 6         If creating a named instance, disable the default instance and rename 7         Choose to run a custom install only if the instance should have a collation different than standard collation.  Use custom install if replacing or moving from an instance that had any other collation setting and use same collation. 8         Select to use the same account for each service (might vary by enterprise) 9         For Service Settings, select to use a Domain User account and provide the user account and password that will be used for SQL Server 10      For Authentication Mode, select Mixed Mode and provide the sa password if using mixed mode authentication.  It's preferable not to use this at all. 11      If doing a custom install then disable named pipes and enter the properties for TCP/IP and change the default number to the desired port when prompted (if doing a typical install then go into Programs>Microsoft SQL Server>Server Network Utility to perform this step) 12      For Licensing Mode, select Processor License for x number of physical processors on box (might vary by enterprise if your company uses CAL licenses) 13      Install the latest service pack which can be found at \\fileshare\directory\sql2ksp3\setup.bat for each instance that has been installed. 14     If installing SP3a instead of SP4, install mdac2.7 – sp1 refresh which can be found at \\fileshare\directory\mdac2.7-sp1refresh {This needs to run once only, this is a server patch not a per instance refresh} 15     If installing SP3a instead of SP4, install the SQL Server 2000 818, \\fileshare\directory\SQL2000 – KB815495-8.00 {This needs to be run for ever instance that has just been installed}   If you do not already have this software installed on your fileshares, the downloads can be obtained at:   configure logins   1  &n[...]

Have I mentioned???????

Wed, 26 Oct 2005 02:44:00 GMT

That EMC sucks!!!!  :)  We lost yet another hard drive today on the CX-700.  I would say I'm surprised, but lightning would strike me dead on the spot. 

We are buying EMC Replication Manager, which will allow us to save a LOT of disk space.  We currently have multiple copies of production for QA, UAT, Release, Enhancement Release, Mirror for parallel testing, Development, Backup, and Testing.  That consumes a considerable amount of space.  The SnapView technology from EMC lets you make a copy of production.  You can then have one of the other environments look at a “snap” of that copy.  The snap takes up about 15% of the original copy size, AND you can refresh it in a few seconds to make it look like the copy when you originally refreshed it.  The advantages of this are obvious in a rapid development environment. 

Adding Replication Manager will allow us to mount multiple snaps of the same LUN or copy onto the same host.  This will allow us to save space on consolidated QA, UAT, Mirror, ER, and Test server because instead of each environment having it's own copy of production, we will be able to maintain one or two copies and all the environments snap off those.  The cost in disk space will decrease by thousands of dollars, paying for the product in the first six month.  In addition, the flexibility to add additional testing and training environments will increase as disk space is freed up.

Anyway......just thought the world might want to know.  We have several terabytes of SQL Server disk space.  Anytime, we can find a way to add flexibility to the environment and lower the overall space cost, it's huge. 


SQL Server 2005 September CTP

Wed, 26 Oct 2005 02:32:00 GMT

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 leverage the new technology to replace our current reporting systems.  I'm also looking forward to the operational and maintenance improvements that will make it easier to identify performance needs, track deprecated stored procedures, etc.



SQL Server 2005 Install Notes

Fri, 17 Jun 2005 05:20:00 GMT

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 interest of the great and almight blogosphere, let's begin.

  1. I had a brilliant idea of setting up one of my virtual server instances as a DC, so I wouldn't have to use multiple computers to have my domain environment.  I also installed SQL Server 2005 on there.  This was brilllllliant.  It didn't work though.  lol  There is no NT Service\Network Service blah, blah account on a DC, so Integration Services wouldn't install.  I learned that the second time this little scenario failed.  The first time it failed because I tried to install with the domain\administrator account.  It didn't have permissions to one of the local .msi files and things kept failing along the way.  I would try this scenario out again to see if I can replicate, but I'm not that bored.
  2. The MSDN site has now very nicely placed the April CTP inside the Visual Studio 2005 section of downloads.  There's a really good reason for this.  The .NET Framework used by the June CTP and the Visual Studio 2005 Beta 2 are DIFFERENT.  I, of course, had to learn this the hard way.  Just to prove the point, I tried it twice.  It failed twice.  BRILLIANT!!!
  3. Do yourself a favor.  If you insist on installing both VS 2005 AND SQL Server 2005, install SQL Server 2005 first.  It works MUCH, MUCH smoother.  Be sure to use VS 2005 Beta 2 and the April CTP.  Together, they work a charm.
  4. Use Virtual Server, VMWare, or whatever you want to use.  It's kewl.  It's awesome.  I love it.  I think it's the best thing since sliced bread with bananas, peanut butter, and bologna all mashed together and topped with mint ice cream.  Setup a Virtual Server instance with just Windows 2003 and all the stupid updates that take 1800 hours to install.  Copy the file.  Hide it.  Protect it.  You'll need it.  After you've done that, embarc on the great voyage of beta testing.  May Gates rest your merry little soul.
  5. Forgot a fun one (I woke up).  I installed the April CTP after VS 2005 Beta 2 once (I think this was the was like 3:30am again).  Under the stupid MS SQL Server 2005 menu I didn't see anything.  IF you INSIST on installing in this order, uninstall the stupid Express edition FIRST.  Otherwise, your tools are ummmmmmmm missing in action.  AWOL tools are a bad thing. 

I might add to this.  I might not.  It depends.  I'm doing this one at midnight.  It shows.  My 2803419840019841 installation (June CTP) is almost finished.  I can't wait.  My journey to conquer the world has began. 


Yawn.  Yawn.  Yawn








Virtual Server 2005 Woes

Sun, 08 May 2005 23:47:00 GMT

I’m using Virtual Server 2005 to setup all my labs and training sessions that I’m trying to write for SQL Server 2005.  I ran into a little problem when I tried to install Visual Studio 2005 on the virtual machines though.  Apparently, VS has a 2.2gb limit when you mount .iso files as a CD/DVD that’s not very well documented.  :)  You basically have to find out about it by using Google and reading people’s blogs.


After finding the issue, I started looking for ways around it.  I tried using the Virtual CD application by Microsoft.  It unfortunately is a piece of CRAP!!!  It locked up my laptop a couple times really nice.  When it did work, it presented an empty DVD drive instead of actually showing me the data.  I finally found a free tool called Virtual DAEMON Manager, which works great. 


I wanted to throw this out there for anyone else who likes just using the .iso files.  I’m building quite a library of them, and I don’t want to actually have to burn a physical DVD for every single one over 2.2GB.


SQL Server SP4 Rollout

Sun, 08 May 2005 17:53:00 GMT

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 are interesting:   SQL Query Analyzer will permit connections to SQL Server 2005. However, some functionality may not be available.  That’s still really kewl!!! FIX: Concurrency enhancements for the tempdb database This particular issue caused us a LOT of issues at my current employer, so it’s good to see this as part of the fixes. There are a lot of issues related to cursors.  Imagine that.  :)  Reading this fix list should give you several more reasons to avoid cursors when at all possible.  They SUCK!!!  Profiler (we hope) will finally return the CPU counters correctly.  This has always been aggravating.  Hopefully, they also fixed the issues with functions not showing up correctly in Profiler when called from stored procedures.   As with any of the service packs they have released for SQL Server, thorough testing should be completed before rolling the service pack to a production system.  There have been service packs in the past that didn’t exactly go smooth, if any of you were around in the 7.0 and pre-7.0 days.  In addition, they usually catch some things during the first few weeks they missed or didn’t get quite right (SP3a?).  Here is the general release roadmap we are currently planning:   Review the release notes and fix lists to determine what needs to be tested at SQL Server and applications levels before releasing service pack to any environment.  Review release notes and fix list. Monitor MS and SQL Server forums for issues. Make a formalized test plan from review. Release to development environment and test for two weeks. Have DBA teams and applications teams test and signoff on the test plan when complete.  Work through issues as encountered and document. Roll to QA and test for an additional week. Have QA team test and signoff. Work through issues as encountered and document. Roll to UAT, Release and Production. Complete final test of environment and major production applications after release.   When we complete the review and have the test plans created, I will add them to the blog.  These are not extensive test plans.  They are created just to insure we test all the major components and processes we believe might be affected after our review of the service pack documentation.   As one last item to cover on the service pack, if you haven't gone through the process of upgrading MDAC components throughout the environment, then you need to get a test plan together and get it done.  Old MDAC installations caused a host of issues with the latest SQL Server service pack release in our environment.  In addition, there have been a lot of important performance, stability, and security fixes throughout the MDAC release cycle.  At a minimum, all MDAC components should be at 2.7 SP1 Refresh even before the upgrade.  With the upgrade though, you should be up to the latest version.  You can find out more about this by reading the release notes for SQL Server SP4 and MDAC 2.8.  Here is the download site for all MDAC components:      UPDATE #1:  (20050516)   We[...]

Oracle and jhermiz

Wed, 04 May 2005 04:31:00 GMT

Oracle and jhermiz

This has been a test of absolutely nothing.

I do believe this might be the most ridiculously stupid blog post I ever make.  :)


SQL Server 2005 Free Training

Wed, 20 Apr 2005 04:00:00 GMT

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. 

If you haven't started reading up and learning 2005 yet, you need to get started.  People who are content with their current knowledge will find themselves very unprepared for the future.  It's coming, so get the reading glasses out, stop procrastinating, and get to studying.  If you don't, the Junior DBAs out there are going to take your jobs you old lazy farts.  :)




Applications that SUCK!!!!!

Thu, 13 Jan 2005 01:45:00 GMT

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 (

Websense (

  • Focusing on the security of the web.   HAHAHAHA

RATA HMDA Compliance software (added 20050517)

  • This one kind of cracks me up.  The default installation creates an account and assigns sysadmin server role rights to it.


I’ll add to the list as I think about more.  Let me know your personal favorites.



SQL Server Datetimes

Sat, 08 Jan 2005 23:31:00 GMT

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. 


CREATE TABLE #results(
 conversion INT,
 result VARCHAR(55),
 code VARCHAR(255)) 

 @min INT,
 @max INT,

 @min = 1,
 @max = 131,
 @date = GETDATE()

WHILE @min <= @max

 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

 INSERT #results(



SELECT @min = @min + 1

 @date AS datetime_format,
FROM #results

DROP TABLE #results


SOX Auditing Companies SUCK!!!!

Wed, 27 Oct 2004 04:18:00 GMT

Just gotta rant for a second.  (I know it's hard to believe.)  There are only a few approved SOX auditing companies out there currently.  The law is so broad in scope, yet undefined, that auditing companies really have no idea what they are auditing for.  We have internal auditors talking to D&T; and one of the biggest problems we face is the vagueness of responses received back.  None of the auditors really agree with each other on what needs to be done.  Passing and/or failing an audit will not be determined by the security of the companies data.  It will be determined by your auditors interpretation of their auditing companies interpretation of a law the courts haven't yet interpreted.

That should give anyone truly concerned about productive process and data security a really bad headache!

Long live stupid laws......job security for IT people that couldn't keep a real job.

Why do all these SOX auditing companies have IT consulting divisions???? HMMMMMMMMM


Database Security Initiative

Sat, 23 Oct 2004 20:07:00 GMT

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 can't be managed isn't secure. §         This is down from over 70 installations when I started. o       Consolidating to 4 pairs.  Production, development, third-party, and back office. ·        Port/Protocol security --Completed. o       All SQL Server use non-standard ports with TCP/IP enabled only. o       Only approved servers are allowed access to specific DB servers on appropriate ports. ·        Named Instances --In Progress. o       Only named instances are used in environment. o       All environments have separate instances. o       Sensitive business units such as accounting and HR housed on own separate instances. ·        Server Lock down --Completed. o       Only database admin and enterprise admin group allowed in local/Administrators group. o       No login allowed outside of approved group. ·        Monitoring of network and server performance -- Completed. o       Look at server closely if unexpected activity levels occur. ·        Server installation policy --Completed. o       There are to be no installations of SQL Server outside of DBA group. o       There is to be no purchase of software with a database component without consulting the DBA Group during review.   Logical Database Security ·        Administration Lock down --Completed. o       The sa password is insanely long and complicated.  Not used.  Only a couple people have access to it. o       Database Admin group added in; and the BUILT IN/Administrator taken out of all SQL Servers. o       All server level (System Administrator, etc) membership deleted with exception of Database Admin group. ·        AD Security --Completed. o       No individual user access to the database server. o       Everyone is a member of an AD group. §         Group corresponds to an application or job function. § [...]

PASS 2004 - Part 4

Thu, 07 Oct 2004 01:20:00 GMT

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 technology used by SQL Server and how that ties into the OS.  He covered many of the concepts in 2005 that allow it to scale better.  One of the main items is the advanced partitioning and online operations in 2005.  I'm already setting up databases in the betas using this technology.  It's going to be a good learning curve for DBAs not participating or looking at the betas to learn all these new features.  You NEED to get started now if you haven't began looking at SQL Server 2005. Security in SQL Server 2005 (by Girish Chander) -- **Excellent** This guy should give presentations for a living.  :)  Girish heads up the security team responsible for rewriting SQL Server Security.  They have done just that.  With the exception of the CLR, security could be the single biggest change in terms of impact, administration, and skill transition.  Here are a few of the highlights:  SQL Server logins have been drastically changed.  They now tie directly into the Windows API if you are using W2k3 to enforce the complexity of the passwords.  There is only a simple complexity check in W2k since the API isn't available.  Blank passwords are off by default, which shouldn't be allowed at all.  :)  You can now disable logins and worry about deleting them later.  We're in the middle of a security audit and redesign right now for SOX.  I wish this feature was availabe now.  You can turn the password policy and expiration (yes, I said expiration) off.  That won't happen where I work though.  Everyone has, or should have heard, of how schemas are now handled.  The naming convention for objects is server.database.schema.object.  Objects are now owned by schemas.  Users own, or are allowed access to, these schemas.  This allows deleting of users not previously possible without renaming all their objects, etc.  It's a LOT like the Oracle model (shhhhh). You have new permission levels that are useful.  You can now give someone rights to JUST execute procs, making them part of their own schema.  You also have a view definition that allows you to see only the metadata.   This will be nice for auditing purposes. Here are a few concerns I have from the presentation: Deny always takes precedence, regardless of the level it's implemented at.  I understand the reasoning for this.  This will continue to cause major issues though when you need to grant an exception access to denied objects.  There should be an override feature.  Oh well....such is life. You can grant someone without permissions to an object the right to that object by using “EXECUTE AS 'user'“.  This is a nice feature, but you need the ability to override it and deny people the ability to execute this.  A reporting person in accounting shouldn'be be able to grant a janitor the rights to see sensitive accounting data.  A few people recommended this in the session, so hopefully this will be addressed. There is still no really good solution to database ownership chai[...]

PASS 2004 - Part 3

Thu, 30 Sep 2004 21:37:00 GMT

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 now supports the option to dynamically change threads.  Right now it requires a reboot.   There were also a lot of good utilities covered.  It's good stuff I tell you.  Everyone should check out the new read80trace tool available at Microsoft.  DBA 101 – SQL Server 2005:  Transferring Existing Skills to a New Platform - **Needs Help** This session was not so great.  The session speaker didn't seem to actually know that much about 2005.  I'm not sure how much he actually used it.  It would be nice to have a session that compares the two and gives you items to watch out for as a DBA. Prescriptive Architecture Guidance on SQLCLR - **ROCKS!!!!!!!!** Speaker - Ramachandran Ven Katesh   This guy is now one of my heros.  :)  He should speak or train SQL Server for a living.  He explained the architecture of the CLR, including CLR, best practices, examples, etc.  Many of his samples can be found by searching MSDN.  If you are a PASS member and didn't get to attend, buy the conference DVD and download his slides.  It will be worth it.    He also showed us the Visual Studio database project in 2005.  This is a project that allows you to debug and register items automatically in a database.  It's much easier than the Beta 1 way of doing things.  In addition, it allows debugging of both CLR and TSQL in the same context.  It's a great upgrade and tool.  One of the things I'm really looking forward to is being able to tell vendors I don't let them put XPs on my server because the SUCK!!!  It was nice to hear an MS person say the same thing.  The CLR chnages all that.   Relational Data Warehousing with SQL Server 2005 - **Good**   After this, I'm tempted to ONLY attend conferences with MS speakers.  They have really done a great job with their presentations this week.  We covered the vast improvements to partitioned, indexed views.  They have been extended to allow use in more situations.  They have also improved the matching algorithms and now allow concurrent processing against multiple segments of the index.  Index functions have also been expanded with online index functions and the INCLUDE clause.   Enhancing your SQL career by making a name for yourself - **Good**   I typed my blog in this session.  It was great.  :)  Seriously, the biggest thing it stressted was:   DO SOMETHING!!!  Help people out, write a blog, answer a question, start a website.  It pays off in the end.   Later....I'm out of here!!!!  [...]

PASS 2004 - Part 2

Wed, 29 Sep 2004 19:00:00 GMT

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 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 clustering.                                                             iii.      Should run equal hardware though; however, it’s not a requirement.                                                            iv.      No shared disk. Can pick synchronous/asynchronous. Terminology                                                                i.      A client attached to the “principal” server.                                                              ii.      Principal server sends “secondary transactions” to the “mirror” server.                                                             iii.    [...]