Troubleshooting MS SQL 2005 Mirroring

Setup:

Principal: Backup database FULL recovery

Mirror: restore database with NORecovery mode

Mirror : restore all transaction logs

Principal : Right click database and click mirroring 

Principal : add Principal DNS name and Mirror DNS name

Principal: click start mirroring

Troubleshooting:

• GRANT CONNECT ON ENDPOINT::Mirroring TO public
• Turn off Windows firewall (normal port is 5022)
• Delete endpoints (the end points are on both Principal and Mirror)
• use IP addresses instead of FQDN
• Add user to Sysadmin Server Role

Common MS SQL server maintainance plans -- best practice

I've been working with MS SQL Database design and management for over 10 years. This is a great start for all maintenance plans.

Error “Failed to open a file. Access is denied”, when attempting to open SQL 2005 Profiler Trace

Yesterday while doing the regular stuff, I was reported a strange error related to profiler traces. I broke my head for a day almost to figure out what was happening.
Issue: I have a SQL Agent Job that creates profiler trace files using sp_trace_create. The job is being owned by my ‘Domain’ A/c which is also SQL Service start-up account.
My colleague (say user-X), when attempts to open the trace files gets error >>
“Failed to open a file. Access is denied”.
This is strange, since there another SQL 2000 Instance, where he can successfully open the trace files. He has NO access on both SQL 2005 and SQL 2000 instances. After several hours of agonizing and troubleshooting, I finally got this on CSS Blog >> How It Works: Trace (.TRC) File Security

Multi-part identifier could not be bound

UPDATE table2
SET training_date=a.training_date
FROM table1 a INNER JOIN table2 b ON a.user_id=b.user_id AND a.training_id=b.training_id

Choose your version of SQL Server 2012

Ever wonder how to change the version of SQL server that you are installing? We wonder no more. Just click on Setup> Options and you'e good to go.

Find the last time all databases were used

select stat.database_id as database_id ,db.name as database_name ,max(stat.last_user_scan) as last_user_scan from sys.dm_db_index_usage_stats as stat join sys.databases as db on db.database_id = stat.database_id group by stat.database_id,db.name order by db.name asc

Can I upgrade my SQL Server 2008 R2 Trial to a full version?

If you are a GUI person just launch the SQL Server setup and go to the “Maintenance Tab”, you will see the “Edition Upgrade” option there. Follow a couple of steps in the wizard, provide the key and you are all set.