Category: Microsoft Certifications

Install TFS 2008 on Windows Server 2008

Last week I decided to buy the TFS license for the TFS 2008. After 2 days and a lot of headaches I decided to follow step by step the official guide which is available here: Team Foundation installation guide and to be honest, if you follow, step by step the guide, you will have a full working version of TFS. Also my MVP friend Simon Chiaretta suggested me to use the official guide.

This short guide will show you how to install a stand alone version, this means, a single server version which will contains: WSS 3.0, SQL Standard 2008 and TFS 2008. So first of all we need 3 licenses, SQL Server 2008 standard (the express edition is not working with TFS), WSS 3.0 and Windows Server Standard 2008 and TFS 2008.

Hey buddy, my suggestion is “spend more money and buy a x64 server with a SCSI disk system, otherwise everything will be very slow …

  1. First of all, install windows server 2008
    Probably this is the most straightforward step.
    1. I don’t suggest any particular installation here, just a couple of few points. Install Windows Server 2008 standard, configure it as an Application Server and a Web Server.
    2. Install IIS 7.0 and the NET Framework 3.5 with SP1 and verify it works, you can just point with the browser to http://localhost
    3. Create a dedicated account for the installation, you must have administrative rights with the account, otherwise you will encounter a lot of trouble …
  2. Install SQL Server 2008 Standard
    1. Lunch the setup for SQL Server 2008 Standard edition.
    2. You must install the Server components, the Client components, the Reporting Services, Full-text search, Analysis services and the Management Studio if you will work locally into your machine.
    3. For every service, specify at least, 1 account, otherwise after the installation you will not be able to login into SQL. I know it’s non sense but it’s how works … Also use only Windows Authentication.
    4. Remember to reboot the machine after the installation and verify the server is running by connecting with the Management Studio. Don’t configure the Reporting Services, this step will be done by TFS. Yeah, I tried to configure it like integrated with MOSS and nothing were working properly.
    5. If you receive during the installation process the error for the Power shell you did one of this wrong steps: you already have SQL 2005, you are using a CTP version of SQL which is coming with an old version of the power shell, you are using a wrong setup of SQL, you are using the Express with advanced services.
  3. Install WSS 3.0 on Windows server 2008
    1. If you are here this means that you have done your SQL installation (cool, it takes 3 times for me). Now download WSS 3.0 with SP1 from here: WSS 3.0 SP1. Don’t download the WSS 3.0, it doesn’t work with Windows 2008, no way!!
    2. Run the setup and choose the advanced installation. You have to choose the front-end installation and choose where you want to locate the installation directory.
    3. After the installation is done, choose to build a new server farm, then choose the local server name, leave the database name as is, and with this syntax domain\user provide the credential for the TFS administrator user. Check the box for the port, don’t use the suggested port and take a note of the port you will use, then choose the NTLM authentication.
    4. Check your settings and after you take a note of everything proceed with the configuration of WSS. It will take a while!
    5. Some prompt for WSS 3.0
      1. Now we need to play with the DOS. First open prompt and change directory to : Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\bin\
      2. stsadm.exe -o extendvs -exclusivelyusentlm -url http://WSSServerName:80 -ownerlogin Domain\UserName -owneremail “admin@localhost” -sitetemplate sts -description “Default Web Site”
      3. stsadm.exe -o siteowner -url http://WSSServerName:80 -secondarylogin Domain\TFSSETUP
      4. The first will be the administrator of SharePoint, the second one the admin account for TFS. I use the same account …
  4. Install step by step Team Foundation Server 2008
    1. First of all, let’s install the TFS server. Choose the database server (local machine name) and go ahead.
    2. Warning. I have received the warning that my SQL Server is not working, because I need TFS SP1. Integrate the SP1 of TFS into the DVD is not easy, but you can find in the latest .chm the description.
    3. Configure the account for TFS and SQL Server.
    4. Choose the previous WSS address and site (did you take note of the port??)

The next post will be how to configure TFS 2008, when I will have time to do it.

Write intelligent query with if exist … drop.

Usually when we build a query we start the .sql file, or command in the query editor with a simple command … CREATE PROCEDURE … bla bla bla.

Ok but if I’m building a database from scratch is very probably that my procedure will be modified a lot of time. So every time we have to launch the command DROP PROCEDURE, or ALTER PROCEDURE.

This sounds good if you are building your object. But in case of you want to store as a BACKUP PROCEDURE some scripts in the network, is better to produce this scripts as ATOMIC and AUTOMATIC.

To accomplish this task, we can use a function really useful in SQL SERVER, IF [OBJECT] EXIST, DROP IT!

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[schema].[procedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [schema].[procedure] GO CREATE PROCEDURE [schema].[procedure] AS BEGIN

So now you can run the script as much time as you want.

SQL 2005 and Linked server (Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”)

If you have a SQL 2005 machine with a (x64) installation, some of the system queries have a different name, like [some_command]sys_64. So when you try to connect your powerfull SQL2005 to an old remote SQL2000, probably in x32 version, you can receive a strange error like Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”.

In many forums you can find a link to a microsoft KB that explains to you that you have to install the SP4 in the old SQL and maybe everything will done.
Usually I like to know why something doesn’t run …

So, when you execute from SQL2005 a query like
select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N’mytable’, N’dbo’, NULL

So you can also try to add this stored in the Remote SQL2000 server, in the master database:

create procedure sp_tables_info_rowset_64
     @table_name sysname,
     @table_schema     sysname = null,  
     @table_type nvarchar(255) = null
as
declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

It works and you don’t need to run strange Package on your critical machine.

Microsoft SQL Server 2005 Certifications Roadmap.

This year is a new year for me for everything, why?

New location (Bermuda), new job (IT Manager) and new technologies (no more NET dev but SQL Dev). So why don’t put in my life another new interest in different certifications? I have decided to became a MCITP (Microsoft Certified IT Professional). Here you can find a detailed description of all the seminaries.

For MS SQL 2005 there are 3 ways. All the ways have the same startup. Exam 70-431  TS: Microsoft SQL Server 2005 – Implementation and Maintenance. With this exam you gain the MCTS SQL 2005. Now you can choose one of the three available profiles.

MCTIP Database Developer.
Microsoft Certified IT Professional: Database Developer certification demonstrates that you can design a secure, stable, enterprise database solution by using Microsoft SQL Server 2005.

Exams

70-441 – PRO: Designing Database Solutions by Using Microsoft SQL Server 2005

70-442 – PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

MCTIP Database Administrator.
Microsoft Certified IT Professional: Database Administrator (MCITP: Database Administrator) is the premier certification for database server administrators. This certification demonstrates that you can keep up with your enterprise business solutions 24 hours a day, 7 days a week.

Exams

70-443 – PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

70-444 – PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

MCTIP Business Intelligence.
Microsoft Certified IT Professional: Business Intelligence Developer certification demonstrates that you can design analysis solutions, data transformations, and reports. Business intelligence developers design and implement multi-dimensional database models (logical and physical), data marts, data warehousing, data transforms, data analytics, and reporting solutions. This includes programming and customizing servers that use Multidimensional Expressions (MDX), customer transforms, and custom reporting solutions. Business intelligence developers are typically employed by medium-sized to large organizations.

Exams

70-445 – TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance

70-446 – PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

I would like to get in first step the Database Administrator, that is the most hard to get. And in a second time I will start with the Database Developer. But if I have to honest what I love is the ETL process, so for me the dream is to get also the complete knowledge of how to build a strong Data warehouse.

Starting from now stay tuned because I will post a lot of info about SQL.