DBA VS System Administrator

Database administrators (DBA) and System Administrators frequently get into conflict due to the overlap in the tasks they perform on a daily basis.
I have worked in many places, each have different rules. It mainly depends on the skills the individual have. If the DBA is more skillful, he or she tend to be doing more of the System admin tasks. However, when the system administrator have more technical skills, they tend to spread into the sql server/ database areas.

Here is a list of tasks that broadly fit into each role, some of them overlap.
The best environment I worked in is when the DBA and System administrators work together and trust each other and learn from each other..

I would be interested to hear your views!

System administrator

• Network
• Server hardware
• Server file/OS backup
• Offline backup
• Server upgrade and patching
• Monitoring and analysing server logs (Application/ security/ server ..etc)
• Disk space monitoring
• Third party backup tool/ Tape backup (e.g. veritas)
• Server level security (server certificates/ AD groups/ users management). Service accounts
• Server build and server rebuild (disaster recovery)
• Manager scheduled tasks
• Windows install and setup
• Windows configuration (Adding/ removing new features/ roles..etc)
• Windows cluster setup
• Allocate Lun from SAN
• Perform snapshot on volume/ server level (SAN)
• Server audits and change management
• Responsible for server security
• Manage server collation
• Server documentation
  

DBA

• Database performance monitoring
• Database server tunning (IO, memory, CPU, Network)
• Database backup and recovery
• Monitoring and analysing sql error logs/ sql agent error logs/ FT index error logs
• Database upgrade and patching
• Disk space monitoring
• Database instance security (logins and roles)/ object level permission management.
• Database / filegroup/ file restore (disaster recovery)
•  Manage SQL Server Agent scheduled tasks (create/ monitor/ troubleshoot)..
•  Database instance level configuration (e.g. setting Max server memory for an instance or configuring cpu affinity value)
• Database install and setup
• Configure database cluster
• Setup and configure High availability features (Database mirroring/ log shipping/ replication)
• Perform database snapshots
• Database audits and change management
• Responsible for Database security
• Manage database collation 
• Database documentation
  
  

Downgrade SQL Server database - from a higher version to a lower version

A backup of a Sql server database that was taken from a older version can be restored to a new version with ease (in most cases).
However, a database cannot be resotred into an older version. for example you cant restore a database backup that was taken in SQL Server 2008 R2 and restore it on SQL Server 2008 or SQL Server 2005.

If you are unfortunate enough to be in a situation where you have a backup of a newer version and you want to restore it onto an older version of SQL Server, here are the steps you need to do;

For this you will need access to two instances of sql server (They can be running on the same box or on seperate boxes);

• Instance 1: running the old version say SQL 2005, I will call it Destination2005
• Instance 2: running the new version say SQL 2008R2, I will call it Source2008R2

1. On the destination server, create a linked server called Source2008R2
2. Create an empty database on the Destination2005 server (this will become your database).
3. Using a tool like Redgate SQL Compare script the schema of the database on Source2008R2 and run it against the new (empty) database you have created in the previous step.
4. Create a script to disable the Foriegn key constraints and another script to enable foriegn key constraints. There are many blogs on the web for this, I got this from http://www.sqlmag.com/forums/aft/70529

Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

## execute updates here...

Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

1. 5. Now you have an empty database on Destination2005 instance with the same schema as the one on Source2008R2 and with all constraints disabled.
2. 6. Start importing the data by running the following script on Destination2005. This script will generate the insert statements required to import the data from the source.

/*

YB: A script to copy data from one database to another.

Run it in the destination database. AT YOUR OWN RISK..

*/

DECLARE

@TABLENAME NVARCHAR(100) ,
@ColumnName  NVARCHAR(100) ,
@HasIdentity INT,
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@NewLine NVarchar(4)

SET @newline=NCHAR(13) + NCHAR(10)
DECLARE ParentCursor CURSOR FOR
SELECT Tab.TABLE_NAME, HasIdentity =CASE WHEN Ident.INCREMENT_VALUE IS NULL THEN 0 ELSE 1 END
FROM
INFORMATION_SCHEMA.TABLES Tab
LEFT OUTER JOIN SYS.IDENTITY_COLUMNS Ident ON Tab.TABLE_NAME=OBJECT_NAME(OBJECT_ID)
WHERE
TABLE_TYPE='BASE TABLE'
and TABLE_CATALOG='MyDatabaseName'
OPEN ParentCursor;
FETCH NEXT FROM ParentCursor INTO @TABLENAME, @HasIdentity
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT 'HAS Identity : ' + convert (VARCHAR(10), @HasIdentity);
SET @SQL1=N''
SET @SQL2=N''
DECLARE ChildCursor CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG='MyDatabaseName'
AND TABLE_NAME=@TABLENAME
AND DATA_TYPE <> 'Timestamp'
ORDER BY
Ordinal_position
OPEN ChildCursor;
FETCH NEXT FROM ChildCursor INTO @ColumnName
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL2 = @SQL2 +'['+ @ColumnName + '],'
FETCH NEXT FROM ChildCursor INTO @ColumnName
END
CLOSE ChildCursor;
DEALLOCATE ChildCursor;
   -- Remove the last , from @SQL2
SET @SQL2=LEFT ( @SQL2, LEN(@SQL2) -1)
SET @SQL1=@SQL1 + 'TRUNCATE TABLE [MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine + 'GO' + @NewLine ;
IF @HasIdentity=1
BEGIN
SET @SQL1 =@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] ON' + @NewLine
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END
SET @SQL1=@SQL1 + 'INSERT INTO [MyDatabaseName].[dbo].[' + @TABLENAME + '] (' + @SQL2 + ')' + @NewLine;
SET @SQL1=@SQL1 + 'SELECT ' + @SQL2 + ' FROM [Source2008R2].[MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine;

IF @HasIdentity=1
BEGIN
SET @SQL1=@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] OFF' + @NewLine;
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END

SET @SQL1=@SQL1 + 'GO' + @NewLine;
 

PRINT @SQL1

FETCH NEXT FROM ParentCursor INTO @TABLEName, @HasIdentity
END

CLOSE ParentCursor;
DEALLOCATEParentCursor;

Error: 30087, Severity: 17, State: 5

I noticed this error in the log of one of my servers that hosts around 100 databases and heavily uses Full text indexes.
the error messages says :

Error: 30087, Severity: 17, State: 5.
2012-08-20 16:24:24.60 spid40s Data coming back to the SQL Server process from the filter daemon host is corrupted. This may be caused by a bad filter. The batch for the indexing operation will automatically be retried using a smaller batch size.

I also noticed a couple of dump files which appeared to be related to the above error message. I googled a bit, but did not find anything useful then opened the dump file and started looking for clues.

This is an extract from th edump.

The highlighted text in the log, reads;

IFTS Encountered memory pressure. decreasing the batch size, number of simultaneous crawlers and/ or ISM size can help alleviate the problem. Global\ISM

This was a sign that the full text deamon was consuming too much memory running out of memory.
The server has 12 CPUs and plenty of memory, but the max server memory was set at default which meant that Sql server was taking all the memory.

To resolve the issue, I performed the following;

- Set a limit to Max server memory that sql server can use, this will give the full text deamon and other services running on the server to have a share of the memory.

Finding the correct value for Max server memory is different for each server and depends on many factors including what else is running on the box along side sql server. The following article gives a formula to calculate the amount of memory that is needed for fdhost.exe

Performance Tuning and Optimization of Full-Text Indexes

The second thing I did was to set the Max full-text crawl range, this is a configuration option that optimises CPU utilisation when performing a full crawl.

The optimum value for  Max full-text crawl range is the number of CPUs on the server, in my case I have 2 sockets with 6 core each

sp_configure 'max full-text crawl range', 12
Reconfigure

The third thing you should do is reorganise and compact the FT indexes using
ALTER FULLTEXT CATALOG [CatalogName] REORGANIZE;