Create ASP Temp session tables & stored procs.

Create-AspSessionDatabase.ps1

function Create-AspSessionDatabase ($dataSource, $database) {

. C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe -S $dataSource -d $database -ssadd -E -sstype c

}

CreateRole.sql

--You need to add this role to the user accessing the Session DB. This is the only role the user needs.

CREATE ROLE [aspnet_TempSession]
GO
GRANT Execute ON [dbo].[CreateTempTables] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[DeleteExpiredSessions] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[GetHashCode] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[GetMajorVersion] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetAppID] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItem] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItem2] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItem3] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItemExclusive] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItemExclusive2] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetStateItemExclusive3] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempGetVersion] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempInsertStateItemLong] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempInsertStateItemShort] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempInsertUninitializedItem] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempReleaseStateItemExclusive] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempRemoveStateItem] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempResetTimeout] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempUpdateStateItemLong] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempUpdateStateItemLongNullShort] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempUpdateStateItemShort] TO [aspnet_TempSession];
GRANT Execute ON [dbo].[TempUpdateStateItemShortNullLong] TO [aspnet_TempSession];

GO

MigrateExistingSessions.ps1

#NOTE this script requires my Invoke-Query module.
#Delete rows from destination DB:
Invoke-SqlServerQuery -Server "MsSql02" -Database "ASPSession" -Query "delete from dbo.ASPStateTempApplications;"
Invoke-SqlServerQuery -Server "MsSql02" -Database "ASPSession" -Query "delete from dbo.ASPStateTempSessions;"

#Retrieve data from current session DB.
$appResults = Invoke-SqlServerQuery -Server "MsSql01" -Database ASPState -Query "select * from dbo.ASPStateTempApplications;"
$sessionResults = Invoke-SqlServerQuery -Server "MsSql01" -Database ASPState -Query "select * from dbo.ASPStateTempSessions;"

$appResults | %{ "Insert into dbo.ASPStateTempApplications values ($($_.AppId), '$($_.AppName.Trim())')" } | Invoke-SqlServerQuery -Server "MsSql02" -Database "ASPSession" -NonQuery

$sessionResults | %{ 
  ##Format the binary data correctly....
$bin =  "0x$($_.SessionItemShort|%{$_.ToString("x2")})"
$bin = $bin.Replace(" ","")
"Insert into dbo.ASPStateTempSessions values ('$($_.SessionId)', '$($_.Created.ToString('yyyy-MM-dd HH:mm:ss'))', '$($_.Expires.ToString('yyyy-MM-dd HH:mm:ss'))', '$($_.LockDate.ToString('yyyy-MM-dd HH:mm:ss'))', '$($_.LockDateLocal.ToString('yyyy-MM-dd HH:mm:ss'))', $($_.LockCookie), $($_.Timeout), '$($_.Locked)', CONVERT(varbinary(7000),'$($bin)',1) ,NULL,$($_.Flags))" | Invoke-SqlServerQuery -Server "MsSql02" -Database "ASPSession" -NonQuery
}