I've been trying to get this Service Broker and timers thing to work for over a week straight now, so bear with me if this comes off as.. uh.. ragey. I'm also fairly inexperienced when it comes to SQL Server.
I've set up two databases to handle a very large amount of data. The first database is used for staging, the tables have no referential integrity and my application will pound these tables just to get the data into the database. That works great. The second database is for production-ready data, has the integrity constraints etc... I've set up synonyms that point to
Production's tables from
I've set up a Service Broker timer queue/service to execute every few seconds to migrate data from completed staging tables into production. The SQL for that is as follows:
IF EXISTS (SELECT * FROM sys.services WHERE name = 'My_MigrationService') BEGIN DROP SERVICE My_MigrationService END GO IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'My_MigrationQueue') BEGIN DROP QUEUE My_MigrationQueue END GO CREATE QUEUE My_MigrationQueue GO CREATE SERVICE My_MigrationService ON QUEUE My_MigrationQueue ([DEFAULT]) GO ALTER QUEUE My_MigrationQueue WITH ACTIVATION ( STATUS = ON , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER , PROCEDURE_NAME = migration_handler ); GO IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'restart_migration_conversation') BEGIN DROP PROCEDURE [dbo].[restart_migration_conversation] END GO CREATE PROCEDURE restart_migration_conversation AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @conversationHandle UNIQUEIDENTIFIER = (SELECT TOP 1 [conversation_handle] FROM sys.conversation_endpoints WHERE [far_service] = 'My_MigrationService') IF @conversationHandle IS NOT NULL BEGIN END CONVERSATION (@conversationHandle) END BEGIN DIALOG CONVERSATION @conversationHandle FROM SERVICE [My_MigrationService] TO SERVICE N'My_MigrationService', N'CURRENT DATABASE' WITH ENCRYPTION = OFF; BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 1; END GO CREATE PROCEDURE migration_handler AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT On; DECLARE @messageType SYSNAME DECLARE @conversationHandle UNIQUEIDENTIFIER BEGIN TRANSACTION; RECEIVE TOP (1) @messageType = [message_type_name] , @conversationHandle = [conversation_handle] FROM My_MigrationQueue IF @conversationHandle IS NOT NULL AND @messageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer' BEGIN EXEC migrate_staging_data BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 2; END COMMIT TRANSACTION; END GO
migrate_staging_data is the one that contains the synonym usage to the
Production database. I'm excluding it because it's very large and generating a MCVE will be time consuming for not much gain.
The queue executes properly, on time etc... Now, when I execute
migrate_staging_data by using SSMS everything goes according to plan, the data is migrated properly and everything is great. As soon as I execute it with the Service Broker timer I get an entry in my SQL Server logs that says
The activated proc '[dbo].[migration_handler]' running on queue 'Staging.dbo.My_MigrationQueue' output the following: 'The server principal "sa" is not able to access the database "Production" under the current security context.
And subsequent attempts through the Broker to call
The activated proc '[dbo].[migration_handler]' running on queue 'Staging.dbo.My_MigrationQueue' output the following: 'The service queue "My_MigrationQueue" is currently disabled.'
sa not have permissions under the Broker like it would while executing from SSMS or another context? How can I give
sa permission to do this?
I've tried adding
WITH EXECUTE AS SELF TO the
migrate_staging_data procedure based on some research, to no avail. I've also tried changing the
ALTER QUEUE My_MigrationQueue statement to include
EXECUTE AS SELF
Basically all I want to do is execute my migration procedure every
x seconds from within SQL Server. Is there a better way without the Broker to just execute a procedure every once in a while?
Just to note: I don't plan on using the
sa login in actual production, I will create new logins for the databases when the time comes.
I'm using SQL Server Enterprise x64, version 12.0.4100.1