Miguel Madeira

Verisign SSL Certificate on Windows 2008

This last weekend we’ve just migrated our production environment to Windows 2008 and SQL Server 2008.

One of our applications works with a SSL Certificate, wich we’ve bought from Verisign, whom is valid until January 15, 2010.

After configure the application in IIS7, and trying to reach the website under ssl on Firefox we’ve received an error message indicating that the certificate is not valid. Strange behavior, considering that in IE7 and Chrome it didin’t reported any issue.

After some investigation, i found that the problem was with the Intermediate Certificates installed by default on Windows 2008 Certificate Manager, that expired at 01-07-2004.

To solve this problem, we need to update the Intermediate certificates. To do this follow the next links:

https://knowledge.verisign.com/support/ssl-certificates-support/index?page=content&id=SO8227

http://www.verisign.com/support/verisign-intermediate-ca/secure-site-pro-intermediate/index.html

After installing the Intermediate Certificate, you need to restart the server. An iisreset doen’t fix the problem.

Install TFS 2008 on a Domain Controller

Today, i've tried to install TFS 2008 on a single server. The machine is the only server on the network, and is also the domain controller of my local domain.

After having everything up & running, SO, SQL Server 2005, i've started the installation of TFS 2008, at the health check step i've received the error that installing TFS is not supported on a domain controller.

However, just making a little hack in the installation configuration file, we can avoid this error message.

1. Copy the contents of <CD>\dt in a temp. directory, e.g. C:\TEMP\dt.
2. Open the file hcpackage.xml in Notepad or any XML capable editor
3. Search for the phrase “domain controller”.
4. Change the first WQL after the first match to

   	<WQL
   	namespace="\\.\root\cimv2"
   	query="SELECT * FROM Win32_ComputerSystem WHERE Domain !='' AND 
   	DomainRole &gt;3"
   	action="="
   	count="1"
   	/>
   	

   You have to change count="0" to count="1".
5. Restart the setup.

Hope it helps!

Changing Server Collation on MS SQL Server

Changing the server collation of a SQL Server is not just a checkbox option. Specially if you have you have user databases on the server.

However and to make it simple for the purpose of this post, if in the case of just after installing your SQL Server you need to change your server collation, you can run one of the following commands, depending on which SQL Server version you are using.

For SQL Server 2005

Insert the media of SQL Server installation and run the command:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=YOUR_PASSWORD SQLCOLLATION=Latin1_General_CI_AI

For SQL Server 2008 

Insert the media of SQL Server installation and run the command:

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="YOUR_SA_PASSWORD"  
/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AI

Hope it helps!

Visual Studio Database Project Errors

I'm using Visual Studio Database Project (DBPro) since beta versions. It's a great product but with several issues that had being fixed during the time. The last version in VS 2008 SP1, has a lot of improvements. I would like to mention the support to synchronize the database against the project, which does not work very well in previous versions.

Last week I've experiencing 2 new errors, when opening a Visual Studio solution with a database project in there.

Error 1: "Instance not set to an instance of an object"

This happens when the solution try to load the DBPro and it fails. The project remains in an unloaded state.

You can't even create new DBPro projects. I've tried to create a new project, but at the end of the wizard Visual Studio returns me the same error message.

My good friend Rui Inácio, told me that he had fixed, the exactly same error, creating a new user profile in his machine. After login with the new account he was able to create and open DBPro projects.

I thought this was not a good solution, because i need to recreate all my old profile (mail accounts, desktop preferences, etc ...), so i've decided to keep searching on a better solution. After a quick googling i was not able to found a solution to fix this error. So i've decided to dive in the DBPro options, and try to understand what should cause this.

After changing a few settings i've remember that, for a specific project, i've changed the setting "Maximum errors and warnings to display" to 0. The default is 200.

You can find this setting in the following location: Tools -> Options -> Database Tools -> Design-time Validation Database.

I've changed this setting back to 200, and press and VOILA !!!, it fixes the error. I'm now able to create an open DBPro projects again.

Error 2: "Failed to attach database"

Following the error 1, Visual Studio presented me with the error message "Failed to attach database".

This was easier to fix. Just delete the .user database project file and open the project in Visual Studio again. The .user file will be recreated and you will be able to open DBProj projects again.

Test your website on different browsers

For Web Developers

When playing the web developer role, i'm always asking myself the same question:

How does this site looks on all browsers?

Of course, i have installed on my machine, IE 7, Firefox, Safari, Opera. But, still, how does other browsers see my website?

Surfing on the web, i've found BrowserShots. This very interest online service, thant basically does the following:

"Browsershots makes screenshots of your web design in different browsers. It is a free open-source online service created by Johann C. Rocholl. When you submit your web address, it will be added to the job queue. A number of distributed computers will open your website in their browser. Then they will make screenshots and upload them to the central server here. "

If you are a web developer, this is someting you won't miss.

SQL Server Reporting Server Authentication Error

After installing Reporting Services on a Windows 2003 Server SP1 Box, everything seems to be ok, but when i browse to the Reports website: http://localhost/Reports i've got the following error message: HTTP status 401: Access Denied.

After some Google search i found that this is not a problem related to MS Reporting Servers, but it's an issue related to Windows 2003 Service Pack 1.

If you have this problem, and you have a box with Windows 2003 with SP1, follow these steps to fix it.

1.Click Start, click Run, type regedit, and then click OK.

2.In Registry Editor, locate and then click the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

3.Right-click Lsa, point to New, and then click DWORD Value.

4.Type DisableLoopbackCheck, and then press ENTER.

5.Right-click DisableLoopbackCheck, and then click Modify.

6.In the Value data box, type 1, and then click OK.

7.Quit Registry Editor, and then restart your computer.

Shrink SQL Server transaction log files

Once I'm using database mirroring, all my databases has the recovery model set to Full.

When using this recovery model, you must have a good backup policy to regularly backup your transaction log, between full backups of your database. If not, the database transaction log file size may increase a lot.

To shrink the file size I use the following script:

exec sp_dboption DBName, 'trunc. log on chkpt.', true

checkpoint

DBCC SHRINKFILE (DBNameFileName, 500);

exec sp_dboption DBName, 'trunc. log on chkpt.', false

Sql Server Database Mirror and Recovery Model

When i've configured the Database Mirroring on my production environment, i've changed the database recovery model to full. Then, i've faced the issue with transaction log file size, that is increasing a lot, on all mirrored databases.

After some research, I found some information to solve this that i would like to share with you.

To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:

• Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

• Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
For additional information about the issues to consider when you configure the autogrow option, click the following article number to view the article in the Microsoft Knowledge Base:

315512 (http://support.microsoft.com/kb/315512/) Considerations for autogrow and autoshrink configuration

• Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:

•Simple recovery model

•Full recovery model

•Bulk-logged recovery model

By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.
By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.
You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.

• Back up the transaction log files regularly to delete the inactive transactions in your transaction log.

• Design the transactions to be small.

• Make sure that no uncommitted transactions continue to run for an indefinite time.

• Schedule the Update Statistics option to occur daily.

• To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
For additional information about defragmenting the indexes in SQL Server 2000, see the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

If you must run the DBCC DBREINDEX statement as a job that is a part of the database maintenance plan, you must break up the job into multiple jobs. Additionally, you must take the frequent backups for the transaction logs between the execution of the jobs.

You can see the full document on the following address. http://support.microsoft.com/kb/873235

Kill all SQL active connections

For the last couple of weeks i'm using SSIS a lot. In some tasks i need to make sure that any client is connected to the database where i need to perform a specific task.

To solve this issue i found this script that basically, kills all the active connections on the database.

USE master

DECLARE 

@p_SPID int, 

@p_SQL nvarchar(2000),

@dbName nvarchar(100)

SET @dbName = 'MyDBName'

DECLARE #cur_Processes CURSOR FOR 

SELECT 

p.SPID 

FROM 

master.dbo.sysprocesses AS p 

JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid ) 

WHERE 

d.Name = @dbName AND p.SPID > 50 -- AND spid >= 51 (because spids of 50 or less are reserved for internal use.) 

OPEN #cur_Processes 

FETCH NEXT FROM #cur_Processes INTO @p_SPID 

WHILE @@FETCH_STATUS = 0 

BEGIN 

SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID ) 

EXECUTE( @p_SQL ) 

FETCH NEXT FROM #cur_Processes INTO @p_SPID 

END 

CLOSE #cur_Processes 

DEALLOCATE #cur_Processes 

VSTS 2008 VPC Image - Expires on December 31, 2008

As you probably notice, the VSTS 2008 VPC Image expired on April 1st. Microsoft has published a newer version with many enhancements including an updated Hands On Lab.

You can download it here.

http://www.microsoft.com/downloads/details.aspx?FamilyID=c7a809d8-8c9f-439f-8147-948bc6957812&displaylang=en

SQL Server Training Videos

The series features 13 professional videos that start at the very beginning and then explore designing tables/schemas, using data types, creating SPROCs, using reporting services and the business intelligence engine, enabling full text search across your data, and more (I'm guessing even most devs with a lot of data experience might learn some new things from the reporting services and full text search videos):

http://weblogs.asp.net/scottgu/archive/2007/03/01/free-sql-server-training-videos-and-other-good-data-tutorial-pointers.aspx

LINQ Extension Method PagedList<T>

Looking at the ASP.NET MVC Framework, i found something very useful.

Basically this few lines of code adds to your project an Extension Method wich implements support for paging on any Generic List.

using System;
using System.Collections.Generic;
using System.Linq;

namespace System.Web.Mvc
{
    public interface IPagedList
    {
        int TotalCount
        {
            get;
            set;
        }

        int PageIndex
        {
            get;
            set;
        }

        int PageSize
        {
            get;
            set;
        }

        bool IsPreviousPage
        {
            get;
        }

        bool IsNextPage
        {
            get;
        }
    }

    public class PagedList<T> : List<T>, IPagedList
    {
        public PagedList(IQueryable<T> source, int index, int pageSize)
        {
            this.TotalCount = source.Count();
            this.PageSize = pageSize;
            this.PageIndex = index;
            this.AddRange(source.Skip(index * pageSize).Take(pageSize).ToList());
        }   

        public PagedList(List<T> source, int index, int pageSize)
        {
            this.TotalCount = source.Count();
            this.PageSize = pageSize;
            this.PageIndex = index;
            this.AddRange(source.Skip(index * pageSize).Take(pageSize).ToList());
        }

        public int TotalCount
        {
            get; set;
        }

        public int PageIndex
        {
            get; set;
        }

        public int PageSize
        {
            get; set;
        }

        public bool IsPreviousPage
        {
            get
            {
                return (PageIndex > 0);
            }
        }

        public bool IsNextPage
        {
            get
            {
                return (PageIndex * PageSize) <=TotalCount;
            }
        }
    }

    public static class Pagination
    {
        public static PagedList<T> ToPagedList<T>(this IQueryable<T> source, int index, int pageSize)
        {
            return new PagedList<T>(source, index, pageSize);
        }

        public static PagedList<T> ToPagedList<T>(this IQueryable<T> source, int index)
        {
            return new PagedList<T>(source, index, 10);
        }
    }
}

Many thanks to Rob Conery.

TFS add-on list

If you are using Team Foundation Server, exists a set of tools that you should consider, at least take a look at them.

Bellow, you can find a list of my favorite ones, that i would like to share with you:

TFS File Sharer - Mimics SourceSafe like file sharing.

TFS Continuous Integrator - A TFS 2005 based continuous integration solution.  Of course, it's built in if you are using TFS 2008.

TFS Dependency replicator - A tool to make changes in the source tree when certain events occur (like copying files when a build completes).

TFS Build Virtualizer - Rather than having one physical machine for each team or build server configuration, manage a pool of virtual machines automatically.

SLR TFS Get Latest Version on Check-Out

Outlook 2003 TFS Addin

Team Foundation Server Administration Tool 

Team Foundation Server Event Subscription Tool

Team Foundation Server 2005 Power Tools - best practices analyzer, work item templates

Team Foundation Server 2008 Power Tools - command line tools, build notification, process template editor, custom check-in policy, best practices analyzer, work item templates

Feel free to suggest others.

CMMI - At what level am I?

Recently, I was making some research about CMMI. I work in a financial consulting company, that works mostly for banks. For some of them, specially outside Portugal, to work with them, they ask us for at least CMMI level 4.

We are just starting our CMMI certification path, and I was learning a lot about this. In an early future, I expect to post more things about this subject.

Below, i try to make a very small resume about CMMI.

CMMI stands for Capability Maturity Model Integration. and helps integrate traditionally separate organizational functions, set process improvement goals and priorities, provide guidance for quality processes, and provide a point for appraising current processes.
CMMI is not a process, is a model that describes the practices applied on effective processes.
The CMMI Framework is the structure that organizes the components used in generating models, training materials and appraisal methods.
The CMMI Product Suite is the full collection of models, training materials and appraisal methods generated from the CMMI Framework.
The components in the CMMI Framework are organized into groupings, called constellations, which facilitate construction of approved models.
Basically, exists 3 constellations: CMMI-DEV that provides guidance for managing, measuring, and monitoring development processes. CMMI-SVC that provides guidance for delivering services within organizations and to external customers. And CMMI-ACQ that provides guidance to enable informed and decisive acquisition leadership. At Finsolutia, we are specially focus on CMMI-SVC and CMMI-DEV.

Next, I will share some links that can provides some useful information about CMMI.

The official website: http://www.sei.cmu.edu

Try this CMMI mini-survey, to get some information about your actual CMMI level. It produces a report with lots of information. It's a good start.
Please, be honest when answering the questions :)  http://survey.cmmi.hu

Presentations
CCMI Version 1.2 Overview
CMMI for Development Version 1.2

Web Service Software Factory: Modeling Edition just released

The Web Service Software Factory: Modeling Edition (also known as the Service Factory) is an integrated collection of resources designed to help you quickly and consistently build Web services that adhere to well-known architecture and design patterns. These resources consist of patterns and architecture topics in the form of written guidance and models with code generation in the form of tools integrated with Visual Studio 2008.

Read more here http://msdn2.microsoft.com/en-us/library/bb931187.aspx