Visual Studio 2008 Database Edition

Question!

Managing server logins and database users between developers workstations and other deployment environments using Visual Studio Team System Database Edition

Hello people,

Looking at upgarding from 'Visual Studio Team System 2008 Database Edition' to Visual Studio Team System 2008 Database Edition GDR R2.

Parametrisation in vstsdb is generally weak and we had to apply workarounds to get the DB projects to deploy locally on each developers workstation for them to work on the application database when they deployed it locally conforming to the sandbox principle. Basicly, back at the beginning, when I imported the application database using the vstsdb import 2005 wizard poject type, i simply removed the user scripts it generated, and had to add a logins pre-deployment script that was driven by project variables, the SQLCMD variables can only be used within this special script location :( ...

It dealt with both the logins and the database users, then linked them up.

Looked something like this..

BEGIN TRY
    BEGIN TRANSACTION

        IF UPPER('$(DeployToDev)') = 'FALSE'
        BEGIN
            -- SMRBtsService
            IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(DomainAppUserAccount)')
            DROP LOGIN [$(DomainAppUserAccount)] 
            CREATE LOGIN [$(DomainAppUserAccount)] FROM WINDOWS

            IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'SMRBtsService')
            DROP USER [SMRBtsService]
            CREATE USER [SMRBTSService] FOR LOGIN [$(DomainAppUserAccount)] WITH DEFAULT_SCHEMA=[dbo]

            -- WebUser
            IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(DomainWebUserAccount)')
            DROP LOGIN [$(DomainWebUserAccount)] 
            CREATE LOGIN [$(DomainWebUserAccount)] FROM WINDOWS

            IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'WebUser')
            DROP USER [WebUser]
            CREATE USER [WebUser] FOR LOGIN [$(DomainWebUserAccount)] WITH DEFAULT_SCHEMA=[dbo]


        END
        ELSE IF UPPER('$(DeployToDev)') = 'TRUE'
        BEGIN
            -- BTSSERVICE
            IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(COMPUTERNAME)\BTSSERVICE')
            DROP LOGIN [$(COMPUTERNAME)\BTSSERVICE] 
            CREATE LOGIN [$(COMPUTERNAME)\BTSSERVICE] FROM WINDOWS

            IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'SMRBtsService')
            DROP USER [SMRBtsService]
            CREATE USER [SMRBTSService] FOR LOGIN [$(COMPUTERNAME)\BTSSERVICE] WITH DEFAULT_SCHEMA=[dbo]

            -- WebUser
            IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'$(COMPUTERNAME)\WebUser')
            DROP LOGIN [$(COMPUTERNAME)\WebUser] 
            CREATE LOGIN [$(COMPUTERNAME)\WebUser] FROM WINDOWS

            IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = 'WebUser')
            DROP USER [WebUser]
            CREATE USER [WebUser] FOR LOGIN [$(COMPUTERNAME)\WebUser] WITH DEFAULT_SCHEMA=[dbo]
        END 

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    IF (XACT_STATE()) <> 0
        ROLLBACK TRANSACTION

    DECLARE @ErrorMessage NVARCHAR(4000),
      @ErrorNumber INT,
      @ErrorSeverity INT,
      @ErrorState INT,
      @ErrorLine INT,
      @ErrorProcedure NVARCHAR(200) ;


    SELECT  @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = 20,
            @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;


    SELECT  @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
            'Message: ' + ERROR_MESSAGE() ;

    RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number.
      @ErrorSeverity, -- parameter: 20, we want to stop the entire deploy process.
      @ErrorState, -- parameter: original error state.
      @ErrorProcedure, -- parameter: original error procedure name.
      @ErrorLine-- parameter: original error line number.
            ) ;


END CATCH

Question;

How would one achieve this sort of functionality using the GDR edition, I see that it supports the concept of linking projects in a hierarchy of references. And you have two project types, a Database and a Server project. But yet you still need to create a script like this to achieve the same dynamic ability for the project to deploy on any machine in dev mode?

Creating a server project type just adds server objects in addition to all the objects (scripts) of the Database project type.

Documentation talks of composite project structures... But still i don't see an obvious solution.



Answers

We also got a fair amount of problems with VSDBCMD, Schema Compare, and MSBuild Deployment regarding users and logins, and targeting different servers.

I STRONGLY recommend you to install RC2!

Follow the install instructions (very important)!

Hope this helps... Let me know!

By : Vanof


Options for most filesystems are shown in the "mount" manpage.

The ata driver may also have various tunables. You can probably change these in real time with "hdparm".

Some options might be filesystem create time options, in which case mkfs will document them.

See the relevant man pages.

By : MarkR


This video can help you solving your question :)
By: admin