TFS security model in configuration database

by XDK 20. December 2013 06:42

 

Following is the sample query to get the user details associated with the team projects

SELECT TOP (10) PERCENT 
    login.display_name  AS user_name, 
    proj.scope_name     AS project, 
    login_grp.display_name AS user_group

FROM dbo.tbl_security_membership_cache AS membership INNER JOIN
  dbo.tbl_security_identity_cache AS login ON membership.member_id = login.tf_id INNER JOIN
  dbo.tbl_security_projects AS proj INNER JOIN
     dbo.tbl_security_domain AS domain INNER JOIN
       dbo.tbl_security_domain_groups AS grp ON domain.domain_number = grp.domain_number 
       ON proj.domain_number = domain.domain_number
     ON membership.container_id = grp.group_id INNER JOIN
  dbo.tbl_security_identity_cache AS login_grp ON grp.group_id = login_grp.tf_id

ORDER BY user_name, project

 

Tags:

Azure DevOps on-premises | SQL Server

How to determine the version and edition of SQL Server and its components

by XDK 10. December 2013 08:15

Solution:

Connect to the instance of SQL Server, and then run the following query:

  • Select @@version

An example of the output of this query is as follows:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009

10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express 

Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

  •  SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

For example, the results resemble the following.

10.0.1600.22 RTM Enterprise Edition

Tags:

SQL Server

Restore TFS SQL Server Enterprise edition databases to SQL Server standard edition.

by XDK 4. December 2013 12:07

Exception:

Database 'Tfs_DefaultCollection' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

 

TFS Collection Databases :-

 

Database 'Tfs_DefaultCollection' cannot be started in this edition of SQL Server because part or all of object <tables> is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

 

Explanation:

 

TFS will enable and use the following Enterprise Edition features.

 

Online index operations

Page compression

Table and index partitioning

Larger read - ahead buffering & Cube perspectives 

 

Data compression and vardecimal storage format will cause problems when moving to different versions of SQL editions. Data compression and vardecimal storage format are enabled on the Tfs_Configuration and Tfs_<Collection> databases by default. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Hence the databases backed up from Enterprise Editions cannot be restored on other Editions.

 

Solution 1: 

The solution is to remove the objects which have compression on the TFS databases.

 

  • Detach the Collection from TFS through TFS Administration console.
  • Executed the following query on Tfs_<Collection> databases deployed on Enterprise Edition to identify the tables with Data compression and vardecimal storage format enabled.

 

SELECT SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] ,[rows] ,[data_compression_desc] ,[index_id] as [IndexID_on_Table] 

FROM sys.partitions INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id WHERE data_compression > 0 

AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' ORDER BY SchemaName, ObjectName;

 

SELECT OBJECTPROPERTY(OBJECT_ID('<table name(s) from above command output>'),'TableHasVarDecimalStorageFormat'

 

Following are the Tfs_<Collection> database tables enabled with Data compression and vardecimal storage format.

 

CodeSense.tbl_AggregateMap

CodeSense.tbl_AggregatorInputQueue

dbo.LinkTreesLatest

dbo.tbl_AuthorizationObject

dbo.tbl_Branch

dbo.tbl_BranchMapping

dbo.tbl_LocalVersion

dbo.tbl_nodes

dbo.tbl_PendingChange

dbo.tbl_PendingChangeRecursive

dbo.tbl_PendingMerge

dbo.tbl_PendingRollback

dbo.tbl_PropertyValue

dbo.tbl_RegistryItems

dbo.tbl_SecurityAccessControlEntry

dbo.tbl_Version

dbo.tbl_WorkingFolder

dbo.tbl_WorkingFolderHistory

dbo.tbl_WorkspaceMapping

 

 

  • Execute the following SQL command to disable the compression on the Tfs_<Collection> database table listed above one by one

 

ALTER INDEX ALL ON '<table name(s) from above command output>' REBUILD WITH (DATA_COMPRESSION = None);

 

ALTER INDEX ALL ON CodeSense.tbl_AggregateMap REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON CodeSense.tbl_AggregatorInputQueue REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.LinkTreesLatest REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_AuthorizationObject REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_Branch REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_BranchMapping REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_LocalVersion REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_nodes REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_PendingChange REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_PendingChangeRecursive REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_PendingMerge REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_PendingRollback REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_PropertyValue REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_RegistryItems REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_SecurityAccessControlEntry REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_Version REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_WorkingFolder REBUILD WITH (DATA_COMPRESSION = None);

ALTER INDEX ALL ON dbo.tbl_WorkingFolderHistory REBUILD WITH (DATA_COMPRESSION = None);

 

ALTER INDEX ALL ON dbo.tbl_WorkspaceMapping REBUILD WITH (DATA_COMPRESSION = None);

 

  • Backup the Tfs_<Collection> database on SQL Server Enterprise Edition and restored it to SQL Server other Editions (both Express and Standard Editions)
  • Attach the Tfs_<Collection> to the TFS instance deployed on SQL Server other Editions (Express and Standard Editions)

Solution 2:

  • Detach the Collection from TFS through TFS Administration console.
  • Executed the following stored procedure on Tfs_<Collection> databases deployed on Enterprise Edition to disable the compression on the relevant tables.

EXEC [dbo].[prc_EnablePrefixCompression] @online = 1, @disable = 1

  • Backup the Tfs_<Collection> database on SQL Server Enterprise Edition and restored it to SQL Server other Editions (both Express and Standard Editions)
  • Attach the Tfs_<Collection> to the TFS instance deployed on SQL Server other Editions (Express and Standard Editions)

Tags: ,

SQL Server | TFS 2010 | TFS 2012

TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server.

by XDK 22. June 2012 15:07

Check whether SQL Server Serices is running.

Tags: ,

SQL Server

About the author

My name is Xavier Dilip Kumar Jayaraj having 16+ years of IT experience which includes solid experience and depth Knowledge in Application Life Cycle Management, Configuration Management, Implementation and Support using TFS on-premises and Azure DevOps. I have invested in gaining DevOps knowledege to expertise with Cloud Computing providers namely Microsoft Azure and Amazon Web Services in recent years. I am very positive to learn and adapt emerging technologies to client’s environment.

Microsoft Certified: Azure Administrator Associate

Microsoft Certified: Azure DevOps Engineer Expert

DevOps Engineer Certificate Program | Transcript 

OTP-AWSD5: AWS IoT: Developing and Deploying an Internet of Things

[PCEP-30-01] PCEP – Certified Entry-Level Python Programmer

Quotes I Like

"Failure will never overtake me if my determination to succeed is strong enough."  - Dr. APJ. Abdul Kalam

"Always be yourself, express yourself, have faith in yourself, do not go out and look for a successful personality and duplicate it." - Bruce Lee

"Technology is just a tool. In terms of getting the kids working together and motivating them, the teacher is the most important." - Bill Gates

"Innovation distinguishes between a leader and a follower." - Steve Jobs

CategoryList

Disclaimer

The information provided here is based on my expreriences, troubleshooting and online/offline findings. It can be used as is on your own risk without any warranties and I impose no rights.