Project Lead/ Leader Responsibilities

• Draft initial charter and project plan.
• Coordinate efforts for completing activities in plan.
• Update plan regularly.
• Provide regular status reports for all activities related to the project.
• Identify and resolve issues.
• Identify and mitigate risks.
• Work with Team Manager to ensure resource workload is balanced across projects.
• Serve as the single point of contact for the Team to the project stakeholders.
• Identify relevant Team capabilities in reference to the project.

How to repair a SQL Server 2008 Suspect database after upgrade to Windows server 2008 R2

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(’yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Migrate MySQL to MSSQL

EXEC master.dbo.sp_addlinkedserver
@server = N'teste_dsn',
@srvproduct=N'test_db',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
DATABASE=test; USER=root; PASSWORD=root; OPTION=3';



SELECT * INTO test.dbo.lookup_master
FROM openquery(test_dsn, 'SELECT * FROM test_db.lookup_master');

Recovery of Read-only SQL DB

Login as sys admin, and run the following commands;

1. ALTER DATABASE SET EMERGENCY

2. ALTER DATABASE SET SINGLE_USER

3. ALTER checkdb(,REPAIR_ALLOW_DATA_LOSS) (The database should be restored from a backup made prior to the corruption, rather than repaired)
Note: This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify “REPAIR_ALLOW_DATA_LOSS” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.

4. If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:
ALTER DATABASE SET MULTI_USER

get sleeping process from sql

SELECT 'KILL ' + CONVERT (char(3),spid) FROM master.dbo.sysprocesses
where dbid = (select db_id('Northwind'))
and status = 'sleeping'
GO

Database backup with full text search catalog

To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online.

The full-text catalog may be online because one or more of the following conditions are true:

• The full-text catalog folder is either deleted or corrupted.
• You did not enable the database for full-text indexing.
• The database is restored from a Microsoft SQL Server 2000 database backup. Therefore, the folder of the full-text catalog in the database does not exist on the server where you restore the database.
• The instance of SQL Server 2005 that you are running was upgraded from SQL Server 2000. However, the full-text search service cannot be accessed during the upgrade.
• The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

To work around this behavior, follow these steps:

1. Locate the folder that contains the files for the problematic full-text catalog.
2. Run the ALTER DATABASE statement. Specify in the statement the correct location for the full-text catalog.
   
   
3. Rebuild the problematic full-text catalog in the database.
4. Perform a full backup of the database in SQL Server 2005 again.

• If you have not enabled the database for full-text indexing, you must enable this option first before you can perform a full backup of the database in SQL Server 2005.
  
  
• If you do not need the full-text catalog any longer, you can drop the problematic full-text catalog. Then, perform a full backup of the database in SQL Server 2005.

Top 20 SQL Server 2008 Enterprise Edition Features

1. Hot-add CPU—recognizes newly added CPUs without a restart
2. Hot-add RAM—recognizes additional RAM without a restart
3. More instances—up to 50 named instances (other editions support only 16)
4. Data compression—automatically compresses database data
5. Transparent database encryption—encrypts databases without making application changes
6. Resource governor—allocates system resources per workload
7. Partitioning—divides large tables and indexes into multiple file groups for better performance
8. Partition table parallelism—uses separate threads for queries over multiple partitions
9. Asynchronous mirroring mode—SQL Server 2008 Standard Edition supports only synchronous database mirroring
10. More failover clustering nodes—up to 16 nodes (Standard Edition supports two nodes)
11. Database snapshots—for capturing point-intime database copies
12. Fast recovery—system availability at the end of the transaction-log roll-forward phase
13. Online indexing—rebuilds indexes while the base table is in use
14. Online restore—restores file groups while a database is active
15. Distributed partitioned views—creates scale-out clusters by dividing tables between multiple SQL Server systems
16. Filtered indexes—lets you selectively index column values
17. Oracle replication publishing—lets Oracle act as replication publisher
18. Peer-to Peer (P2P) transactional replication— replicates data changes to all nodes on the network
19. Advanced transformations—adds SQL Server Integration Services transformations such as Fuzzy Lookup and Data Mining
20. Change data capture—ability to track changes on a table and capture them to a mirrored table