0 Comments

This post has been ported from http://www.sqlservercentral.com/scripts/MS+SQL+2012/93043/ which I then repeated on https://gbeeming.wordpress.com/2013/02/12/failover-or-restart-results-in-reseed-of-identity-fix/.

There existed an "issue" for us when we moved to SQL 2012 where every time your SQL instance restarted for any reason be it manually or server unexpectedly turned off your identity columns would have jumped numbers on the next record being inserted. It was also raised on the connect site http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity.

We found out that this was not actually an issue and was by design for performance to optimize the amount of disk IOs that were required to generate sequence numbers. A post on SQL Server Training describes how to change the cache size http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-.

Before seeing that post however I developed another solution that basically reseeded every table on instance startup so the issue didn't exist. To use this solution you can just run the script below on your SQL instance.

USE master; 
GO
CREATE PROCEDURE sp_FixSeeds2012
AS
BEGIN

--foreach database
DECLARE @DatabaseName varchar(255)

DECLARE DatabasesCursor CURSOR READ_ONLY
FOR
SELECT name
FROM sys.databases
where name not in ('master','tempdb','model','msdb') and
sys.databases.state_desc = 'online'

OPEN DatabasesCursor

FETCH NEXT FROM DatabasesCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC ('USE '+@DatabaseName + '

--foreach identity column
DECLARE @tableName varchar(255)
DECLARE @columnName varchar(255)
DECLARE @schemaName varchar(255)

DECLARE IdentityColumnCursor CURSOR READ_ONLY
FOR

select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME,
'
'IsIdentity'') = 1

OPEN IdentityColumnCursor

FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName

WHILE @@FETCH_STATUS = 0
BEGIN

print '
'['+@DatabaseName+'].[''+@tableName+''].[''+
@schemaName+'
'].[''+@columnName+'']''
EXEC ('
'declare @MAX int = 0
select @MAX = max('
'+@columnName+'')
from ['
+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
if (@MAX IS NULL)
BEGIN
SET @MAX = 0
END
DBCC CHECKIDENT(['
+@DatabaseName+'.''+
@schemaName+'
'.''+@tableName+''],RESEED,@MAX)'')

FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName

END

CLOSE IdentityColumnCursor
DEALLOCATE IdentityColumnCursor'
)

FETCH NEXT FROM DatabasesCursor
INTO @DatabaseName

END

CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor
END
GO

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO

EXEC sp_procoption @ProcName = 'sp_FixSeeds2012'
, @OptionName = 'startup'
, @OptionValue = 'true'
GO

Hope this helps someone as I see the connect issue on it is still active.

0 Comments

This post has been ported from http://gbeeming.wordpress.com/2013/10/08/how-to-get-your-identity-tokens/.

I’ve always found that it’s slightly harder than it needs to be to get the keys for apps to authenticate against Twitter, Facebook, Google and Microsoft Account. Today I’ll show you where to go to get all these keys.

Microsoft Account

Go to the Windows Live application management site and sign in if you aren’t already. Click on create application, when the new page loads type an application name, select a language and click I accept. If your app is a web application enter the redirect domain otherwise select Yes under the mobile client app and then click Save.

At this point you should see a message on the top of the form that reads Your changes were saved. You can now use the Client ID and Client Secret that are displayed on screen.

Facebook

Go to the Facebook developers apps page and sign in if you aren’t already, click on + Create new App and enter an application name into the popup, optionally enter an application namespace and then select a category and optionally a sub category and the press continue. Enter the Captcha that is displayed and click continue.

At the top of the page you will see your App ID and App Secret, customize all the settings of your app as you feel needed and then take the app out of Sandbox mode to make it live.

Twitter

Go to Twitter Dev Site and sign in if you aren’t already. Hover over your profile in the top right corner and then click on My applications.

image

Click on Create a new application. Enter a name, description and web address for your application, check the Yes, I agree checkbox, enter the Captha and then click on Create your Twitter application.

If you scroll down a bit you’ll see your Consumer Key and Consumer Secret.

Google

Go to Google+ Sign-In, all instructions are under Step 1: Create a client ID and client secret, but I will place them here as well Smile.

Go to Google APIs Console, select create from the drop down menu on the left and then enter a project and click Create Project. In the all services list switch the Google+ API on and then when the page is redirect check I agree to these terms and click Agree.

Next go to the APIs & auth tab, then the Credentials tab on the left and click on Create new Client ID under the OAuth section

image

Enter your product name and click Next. Select the application type that is applicable to you and then click Create client ID.

After the popup closes you will see your Client ID and Client secret on the page.

Redirect Urls

You might be required to set the Redirect for your app in which case you can use the urls below for the different OAuth providers.

Provider Url
Google http://<your_domain>/signin-google
Facebook http://<your_domain>/signin-facebook
Twitter http://<your_domain>/signin-twitter
Microsoft Account http://<your_domain>/signin-microsoft

Enjoy

The steps above will give you all you need to setup Windows Azure Mobile Services Identity providers. Option 3 on the Get started with authentication in Mobile Services tutorial.

0 Comments

This post was originally found on my old blog at http://gbeeming.wordpress.com/2013/06/28/tf400324-team-foundation-services-are-not-available-from-server/.

Full Error

TF400324: Team Foundation services are not available from server <Server/CollectionName>. Technical information (for administrator):   Page not found.

Problem

This error could occur if you have moved a collection from one server to another and a client application connects to both servers. In my case this is because I’m running TFS 2012 on one server for production use and have backed up and restored a collection to a newly installed TFS 2013 Preview so that I can configure the process template (Upgrading to Visual Studio Scrum 3.0 process template in TFS 2013) to take advantage of the new features in 2013 and test that before moving/upgrading the production server.

Quick Fix

As a quick fix you can

  1. Close Visual Studio and related apps
  2. Browse to %LocalAppData%\Microsoft\Team Foundation\5.0\Cache and delete all the contents of that folder

This isn’t ideal as you will need to do this for each client and every time you switch between the 2.

Better Fix

As a better and more user friendly fix, you can

  1. Do the Quick Fix
  2. TS to the server where you have The new TFS server with a copy of your collection attached (my TFS 2013 instance)
  3. Open a command line window in Administrator mode
  4. Run cd "C:\Program Files\Microsoft Team Foundation Server 12.0\Tools"
  5. Run tfsconfig ChangeServerID /sqlinstance:<Your Sql Instance Name> /databasename:<Your configuration Database Name> /ProjectCollectionsOnly
  6. This should bring up a message that says "The command ChangeServerId should only be run against a set of Team Foundation Server databases that have no application tiers configured. Do you want to continue with this operation? (Yes/No)"
  7. Type Yes and hit enter

Now you should be fine to use both instances of TFS with the same collection name without issues