Dynamic Management Views and Functions (Transact-SQL)

The Dynamic Management Views and Functions (Transact-SQL) it's a very important tools that Microsoft provided to all DBA. This tools help us to investigate about some issue. I will provide some tips about that tools in the next few month.

SQL Server 2012

SQL Server 2012 is now the official name from Microsoft.

If you need to deploy a solution next year, I will recommend you to go with SQL Server 2012.

All new of SQL Server since SQL2005, the engine are more stable and robust.

If you want me to list you the advantage, let me know.

Restore Database who was replicate

How many times we need to restore database into Dev or Test environnement?

The problem is when a databse is replicate and we restore it into an other environnement, the status of database not change. When we try to clear the transaction log, we get an error about some transaction is still open for replication.

Here is the step to resolve this issue:

Exec sp_replicationdboption @dbname = N'your db name', @optname= N'publish', @value= N'true'

Exec sp_repldone @xactid=NULL, @xact_segno=NULL, @numtrans = 0, @time=0 @reset=1

(
Exec sp_replicationdboption @dbname = N'your db name', @optname=N'publish', @value=N'false'

AND / OR

Exec sp_replicationdboption @dbname = N'your db name', @optname=N'subscribe',@value=N'false'
)


Exec sp_removedbreplication 'your db name'

DBCC opentran('your db name') -- to be sure you don't have any transaction open

kind of action you can do now

backup log 'your db name' with truncate_only

BUILTIN\Administrators on SQL Server 2005/2008

This Windows group BUILTIN\Administrators in SQL Server 2005/2008, do we need it?

I will say is depends!! If you have a DBA on place, he will take care of the security in SQL Server and I hope he will keep this groups but without sysadmin role.

Why we remove the sysadmin role for BUILTIN\Administrators, because any person who have the administrator right on the server, he will be sysadmin on the database server. If the person doesn’t have the knowledge of database, this is a risk for your data of your company.

Before to remove the role of sysadmin for BUILTIN\Administrators, you need to be sure you have another account (SQL or Windows) with sysadmin role.

By the way, a good practice is to disable the sa account.!!!

Backup strategy for SQL Server

What’s a good backup strategy for us?
Do we have a DBA in place? Do we have a Remote DBA? How long I could leave without my data? How much data I could lose?
That’s question you need to answer.

You need to think about how much time I have to recover the data. You need to have a procedure for all yours backups, because when it’s time to recover, you don’t have time to think how to do it.
I have experience, but I’m very happy to have in my briefcase the procedure for all my backups and where there are.

My best strategy is to have a cross backup, that’s mean have in 2 locations, the same backup.

SSAS trainning

How to prepare a good training for SSAS if you aren’t a trainer!!
I think the key is the student, collect the needs and start from there. You could pass all the session to explain something they don't need for them, you will find this very hard to motive them.
You could also ask to the base of BI and see what the knowledge of your attended is. The tool of SSAS is very complex and you need to have some requirement before, for example, you need to have some skill about Visual Studio.
I like share my knowledge and I will try to be prepared for the next time!!

Cluster 2008 and SQL 2005

Installation of SQL Server 2005 in cluster on Windows 2008 is like Windows 2003, but the cluster SQL Group doesn’t exist until you install the service Pack you want of SQL Server 2005.

Be carful to associate the good drive for SQL Server Resource if you want to use then for your data.