Bad Data Models

We all know what a good data model is and spend significant time designing a good data model. So if there are good data models then there are bad data models too.

There is a difference between incorrect data models and bad data models. An incorrect data model would not satisfy business requirements and hence they don't make to the implementation. But bad data models would get implemented. They do more harm to the system. Every part of the application has to compensate for the bad data model.

Bad data models evolve due to various reasons – time and resource constraints, inexperience, over engineered modeling and sometimes sloppiness.

SQL Server 2008 – Maximum Capacity Specs

I found this article which gives the maximum size specifications for SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms143432.aspx

SQL Server 2008 Min and Max Date Ranges

Here are the Minimum and Maximum date ranges

SQL Server 2005
Min Date: 1753-01-01 00:00:00.000
Max Date: 9999-12-31 23:59:59.999

SQL Server 2008
Min Date: 0001-01-01 00:00:00.000
Max Date: 9999-12-31 23:59:59.999

Reporting services 2008 does not need IIS

Here is how it handles without IIS

http://blogs.msdn.com/bwelcker/archive/2007/12/04/everybody-why-leave-iis.aspx

SSIS Configurations

There is a significant change in how SSIS applies configurations in SQL Server 2008. The configurations are applied twice – once before applying the command line options and once after applying the command line options. So it overwrites the value specified on the command line.

This is a nice improvement since you can set up a package with SQL Server Configurations and change the connection string for SQL Server configuration.

Check this link for more information

http://msdn.microsoft.com/en-us/library/cc671625.aspx

Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue). The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring

1. Backup the log file to a location

BACKUP Log YourDatabaseName TO DISK = 'D:\BACKUP\DBNAME_20090201.TRN'

2. Check if there is enough free space on perform the shrink operation

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);

If there is no sufficient free space then the shrink operation cannot reduce file size.

3. Check if all the transactions are written into the disk

DBCC LOGINFO('YourDatabaseName')

The status of the last transaction should be 0. If not, then backup the transaction log once again.

4. Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)

If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.

Also check the column LOG_REUSE_WAIT_DESC in the sys.databases catalog view to check if the reuse of the transaction log space is waiting on anything.

Check this link to find the factors that can delay log truncation

http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx

Connecting to Remote SSIS Server

If you get the “Access is denied” error while connecting to remote SSIS server, you may have to perform the steps outlined in the following link

http://msdn.microsoft.com/en-us/library/aa337083.aspx

Additional steps to perform which are not in the MSDN page

1. Add the user to the Distributed COM Users group on the server
2. Restart the SSIS service after making changes
3. At the minimum, add the user to the MSDB db_dtsltduser role to edit their own packages. Check this link for more permissions on the msdb database

http://msdn.microsoft.com/en-us/library/ms141053.aspx