How much effort is SQL Server 2008 Administration? [closed]

SQL Server is easy to adminsitrate, but it still needs administration. That includes at minimum:

• Regular backups. You want that - or another model - to clean up your log files (as they otherwise accumulate and never get deleted).
• Regular patching. Every 2 months for a cumulative update.

On top, set up some autoamtic maintenance within SQL Server (like weekyl database checks etc.).

From all databases SQL Server is the one best to administrate.

That said, you are on a bad track potentially:

• SQL licenses on the internet have to be SPLA (Service Provider License Agreement). Not purchased, rented monthly. Cheaper than purchasing, on top. Your licesne simply does not cover service providing.
• IO performance possibly sucks. Hyper-V standard servres are NOT made for SQL hosting. SQL, as any database, when getting more busy has VERY specific IO needs - the disc layout is normally planned for a database, not just a "put some discs into a case" thing. It works nice on Hyper-V, too (I run my main SQL Server on Hyper-V), but you wont want to have a "normal" hyper-V setup which most likely has totally crappy IO values to start with.

Example of the IO issue:

Depending on how you run Hyper-V as host you may have a "mid sized server" (8-12 processor cores, basically a dual processor Opteron platform) with 32-64gb RAM that you load with VM's. Discs - hm - a couple of large 1tb discs in a RAID possibly. Either 2x1TB to 2x2tb mirrored, or three in a RAID 5 is likely (cheap). Performance? Good enough for most things. Db wise? STUPID SUCKING LOW IO. You would not do that for a SQL Serve alone, but here it is shared between all hyper-v instances... and there will be many.

My own setup (just as comparison):

• Hyper-V server 2 rack units, 2x4 core opteron 64gb ram. So far the same.
• 4x10k rpm velociraptor drives as RAID 10 for OS and virtual machines.

Already a lot better and more expensive. But that is NOT where it ends.

• For the SQL server another 12 (!) velociraptor drives in 2 raid groups (logs, data) that are DIRECTLY mapped to the hyper-v instance. (And yes, the drive case has 24 hard disc slots).

Ergo: I have all the IO performacne I need (and I need it). Though this is not a typical low cost hyper-v virtual server setup.

Be carefull about your needs and about what you get as Hyper-V server. MOST offers make BAD database servers. Not relevant for a small server, though.

If you think MYSQL is an "install and forget experience" SQL 2008 should be a breeze relatively speaking. While there are patches, most DBA's put them on (if they think they require them) at scheduled times.

Is running a halfway efficient MS SQL Server 2008 difficult?

Just one? Not really IMO.

Does it require any in-depth administration knowledge?

If you got MySql down, then you have an understanding of database concepts and such, you'll just have to learn how Windows/SQL Server does things (which can be a task at times). But it's not that complicated, and there is wealth of info out there.

Or perhaps recurring administration effort (such as keeping the server up to date with the latest patches)?

You'll need to patch the OS if you want to keep things up to date like any OS, as well as SQL Server. That can be set to be done automatically at your specified time, or done manually. Maintenance plans can do a lot things automatically for as well on the database side.

I'm all Windows/SQL Server experience so I can't speak to how much more difficult it is compared to your past solutions, but I can tell you that over the years, SQL Server hasn't been my biggest headache in my server environments.

renting a Hyper-V server and installing my license of SQL Server 2008 on it.

Unless you have a per-processor license, you cannot expose a licensed SQL Server for use from internet for an ASP application. CAL licenses cannot be used, since you'll be multiplexing an unlimited number of users. You may be better off hosting from a provider that has negotiated a volume license with MS and rents you a licensed SQL, like a EC2 VM with SQL in it.

As per the difficulty of administration: basically nothing from your OS know-how about administration will transfer to SQL. Some of the concepts do overlap (like backups, security, performance monitoring) but really only as a concept. SQL Server has its own restore/backup model, has its own security model (significantly different from OS), has its own job scheduling infrastructure, has its own performance concepts. Some examples of differences could be things like:

• log backups, which are a concept specific to SQL and the cornerstone for some recovery scenarios (point in time recovery, log shipping)
• database principals (users) vs. server principals (logins), no equivalent OS concept
• performance monitoring: lock contention monitoring, it has no equivalent OS concept

These are just a few examples, but the gist of it is that SQL Server administration is a different skill set to which few of your previous OS administering skills will transfer. Whether is hard or easy, I cannot say. If you don't know what to do or where to look will be extremely hard. I'd suggest to you pick up a good book about SQL administration before.

I think this is a very difficult question to answer because it's so subjective.

I have sql express installs that I never touch besides patches, that have been installed for several years.

I have SQL 2000 and SQL 2005 installs that I touch with regularity. I'm creating DTS/SSIS packages for them. I'm constantly running queries, checking backups, etc. These are high volume transactional db's though...

I'm not entirely sure your question can be answered without more specific information from you.