Just ran the first Live to Test refresh since converting to v11. Do I have to re-run TIM against Test also? I’m getting those nasty services errors logging in to Tessitura which I believe are TIM related.
Thanks everyone. Definitely have to update my doc on this! Found several “things” I did not expect. All your recommendations were very useful!
…Dave
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Frederick Sent: Wednesday, December 05, 2012 1:45 PM To: Vivino, David Subject: RE: [Tessitura Technical Forum] Re-run TIM after Live to Test Refresh?
I wanted to add a tip that can significantly cut down on the code needed in the refresh. When the database restore occurs, the association between the SQL login and user in the database no longer exists. One method to fix that is to drop and recreate the user. Another method is to simply run a command like this in both impresario and impresario_cci databases:
EXEC sp_change_users_login 'Auto_Fix', 'tessAPI';
This repairs the link between the SQL login and database user, so to speak, while retaining the user's configuration. We do this for our services user, as well as our security manager accounts. David, that missing association between your services login and database users is likely the cause of your errors.
Thanks, David
From: Heather Kraft Sent: December 5, 2012 9:45 AM To: David Frederick Subject: Re: [Tessitura Technical Forum] Re-run TIM after Live to Test Refresh?
David - there's new SQL stuff you have to do in a v11 copy from live to test. I think it is in the v10 to v11 copy documentation and I swear I saw it somewhere else... But this is what I've been using just after the securecheck step.
Hope this makes sense. If I can find the official doc I'll put a link later!
use impresario
GO
declare @schema_name varchar(100)
select@schema_name = 'SqlDependencySchema'
declare @objects table (
id int identity(1,1) not null,
name varchar(255) not null,
obj_id int not null,
xtype varchar(10) not null)
insert@objects
selectb.name, b.id, b.xtype
fromsys.schemas a
join sysobjects b on a.schema_id = b.uid
wherea.name = @schema_name
and b.name like 'SqlQueryNotification%'
declare@sql varchar(255)
declare@obj_name varchar(255)
declare@obj_id int
declare@obj_type varchar(10)
declare@svc_name varchar(255)
declare@ct int
select@ct = 1
while@ct <= (select MAX(id) from @objects)
begin
select@obj_name = name,
@obj_id = obj_id,
@obj_type = xtype
from@objects
whereid = @ct
if @obj_type = 'SQ'
select@svc_name = name
fromsys.services
whereservice_queue_id = @obj_id
if coalesce(@svc_name,'') <> ''
select@sql = 'IF EXISTS (SELECT * FROM sys.services WHERE name = ''' + @svc_name + ''')'
select@sql = @sql + ' BEGIN '
select@sql = @sql + 'DROP SERVICE [' + @svc_name + ']'
select@sql = @sql + ' END'
exec(@sql)
end
if coalesce(@obj_name,'') <> ''
select@sql = 'DROP QUEUE [' + @schema_name + '].[' + @obj_name + ']'
if @obj_type = 'P'
select@sql = 'DROP PROCEDURE [' + @schema_name + '].[' + @obj_name + ']'
select@ct = @ct + 1
-- Drop Services User
USE [impresario]
/****** Object: Schema [tessApi] Script Date: 12/18/2011 21:38:09 ******/
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')
DROP SCHEMA [tessApi]
/****** Object: Schema [SqlDependencySchema] Script Date: 12/18/2011 21:38:21 ******/
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')
DROP SCHEMA [SqlDependencySchema]
/****** Object: User [tessApi] Script Date: 12/18/2011 21:38:43 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')
DROP USER [tessApi]
USE [impresario_cci]
--/****** Object: Login [tessApi] Script Date: 12/18/2011 21:40:07 ******/
--IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'tessApi')
--DROP LOGIN [tessApi]
--GO
-- Create Services User
USE IMPRESARIO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')
CREATE USER [tessApi] FOR LOGIN [tessApi]
WITH DEFAULT_SCHEMA = [SqlDependencySchema]
ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]
CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]
EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'
EXEC sp_addrolemember 'impusers', 'tessApi'
USE impresario_cci
-- Enable Service Broker
declare @query nvarchar(255)
If NOT EXISTS (select * From sys.databases Where is_broker_enabled = 1 and name = 'impresario')
Begin
SET @query = N'ALTER DATABASE impresario SET ENABLE_BROKER with rollback immediate'
Exec sp_executesql @query
End
From: David Vivino <bounce-davidvivino4732@tessituranetwork.com> Sent: 12/5/2012 10:56:27 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!