5

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 Staging.

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

The procedure 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 migration_handler outputs

The activated proc '[dbo].[migration_handler]' running on queue 'Staging.dbo.My_MigrationQueue' output the following: 'The service queue "My_MigrationQueue" is currently disabled.'

Why?

Why does 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

3

The easiest (quick and dirty solution) but risky in terms of security approach would be to mark the database as TRUSTWORTHY using ALTER DATABASE [your_db_name] SET TRUSTWORTHY ON.

Remember that marking the database trustworthy elevates the database dbo to a de-facto sysadmin

You are getting below error :

'The server principal "sa" is not able to access the database "Production" under the current security context.

because the activation execution context is trusted only in the database, not in the whole server. Anything related to the whole server, like a server level view or a dynamic management view or a linked server, acts as if you logged in as [Public].

Refer thoroughly blog post by Remus Rusanu :

Also, you should have process in-place to Handle Poison Messages to prevent disabling the queue.

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?

You can look into approach like incremental load using SSIS from staging to PROD database. You can apply different techniques when loading the data as well.

  • TRUSTWORTHY is pretty much never needed as module signing handles nearly all scenarios (if not all), and isn't really that hard to set up. Thanks for the link to Remus's post (+1 for that alone) as that contained the one minor detail I was missing from getting it working. I now have a working example of this, in the "Ideal Solution (T-SQL)" section, in my answer to a similar question. – Solomon Rutzky Sep 25 '17 at 20:42
  • Thats valid and is reflected in my answer risky in terms of security approach. I liked your linked answer +1 to that. – Kin Shah Sep 26 '17 at 1:39
  • Kin, sorry, I wasn't implying that you neglected to mention it. I was just trying to emphasize the point since many people don't mind the risk if the alternative is too difficult or just won't work. And since the perception is that certificates / module signing is difficult / complex, a lot of people opt for just going with TRUSTWORTHY ON. So, I've been hoping that presenting it as an unnecessary risk, as opposed to merely "risky", will allow people to focus more on the better solution, or at least not automatically dismiss it without even taking a closer look. – Solomon Rutzky Sep 26 '17 at 1:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.