Upgrading Sharepoint 3.0 to SQL 2005 Backend

At work we originally ran Sharepoint 3.0 with a SQL Server 2000 backend. We don’t have an extremely involved deployment, and currently Sharepoint is only used for internal purposes. That’s my nice way of saying we don’t know a whole lot about Sharepoint.

Gaining knowledge about Sharepoint is a pretty painful process, as there’s an entirely new vocabulary to learn.

Luckily, Stackoverflow exists. I asked.

The post on stackoverflow has the details on the process. We’re one DB closer to being rid of SQL Server 2000.

Advertisements

Problem with Orphaned Users/Logins in SQL Server 2005 Migration

Scenario:

You’ve run sp_detach_db on SQL Server 2000 and sp_attach_db on SQL Server 2005. Your database is now in 2005 and your application has now been reconfigured to point to the new server… but your application can’t log in.

Create the Login in SQL Server 2005. Logins and Users are different things. Logins apply to the server level, where users apply to the database level. One must have a LOGIN before having a USER.

Use the same password as your application. What’s happened is when you detached the USERS for your database were mapped to a LOGIN (via an ID) on your old server. You may have User(LoginID=1000) but the new login you made has an ID of 2000. Your user is an orphan. Let’s reunite them.

Run this T-SQL:

use databasename;
exec sp_change_users_login 
	@action = 'update_one', 
	@UserNamePattern = 'username', 
	@loginname='loginname', 
	@password='pass' 

Showdown: MySQL vs. SQL Server 2005 (Backups Edition)

My disaster recovery needs from a database standpoint:

  1. Ability to restore to a point in time
  2. Retention period of 4 weeks
  3. E-mail notification on failure

MySQL Backups

A basic cron job dumping your databases on a daily basis:

shell> crontab -e

Add this to your crontab:


0 2 * * * mysqldump -ubackup_user -ppasswordhere --all-databases | gzip -c > /path/to/mysql_backups/backup_`date +%Y-%m-%d`.sql.gz

This will run at 2:00 AM daily and back up all of your databases. It will produce a fancy .sql.gz file that has a date stamp. Example: backup_2008-03-12.sql.gz. For generating Cron jobs, look at this site.

This is nice, but you’ll have to pay attention to your backup folder because eventually you will run out of disk space. You will not receive any notification when the backups fail or when you run out of disk space from this method. You also see that it gives anyone who has access to your crontab access to your password. Make sure this is a database user that has READ ONLY access to all dbs/tables.

SQL Server 2005 Backups

Some will say I’m a little biased… but this is one of the parts of SQL Server that makes it worth the price. If an image is blurry in the display, click it for full resolution.

  1. Create a new Maintenance plan
  2. Click ‘Back Up Database’ on the toolbox.
    toolbox.png
  3. Drag to the blank window.
  4. Select your databases, and various options for your backups. I recommend using ‘verify backup integrity’ and having your .bak files moved off site.
    ss-maintplanoptions.png
  5. Schedule your maintenance plan.
    schedule.png
  6. Add a notify operator task (you must have Database Mail Configured) , and drag the red node to it. This will email your specified operator (I get them sent to my phone) when a backup fails.
  7. Clean up! I do separate a weekly data cleanup maintenance plan to check my indexes, delete my backups older than a month, and lots of other things. Here’s a screenshot:
    ss-maintplan-extended.png

Clearly, SQL Server 2005 meets and exceeds my disaster recovery needs over MySQL. Did I mention it was extremely easy to set up in SQL Server Management Studio?

I didn’t have to research bash datestamps or find a site to generate crontabs.

Tim suggests I use this perl script for deleting old MySQL backups (run at the same interval as the backups):


#!/usr/bin/perl
use File::Find;
my $path = shift;
my $threshold = 60*60*24*28;
find(sub { /mysqlbackup\.tar\.gz$/i and
unlink "$_" and
print "$File::Find::name deleted\n\n" and
(( ((stat($_))[9] ) + $threshold) > time);
}, $path);

Also from the comments:

find /path/to/mysql_backups/ -type f -mtime +N -exec rm -rf {} \;

USE AT YOUR OWN RISK.