CODEWITHSUNDEEP
sql-servertriggersazure-sql-databasedbmigrate
sqlandmore.com
sqlandmore.com

Reputation: 163

What to do with triggers on SQL Server database during migration to Azure DB?

I have a SQL Server database to be migrated to Azure.

Can I replace triggers with something else on Azure? Service?

Or is the only option to remove the triggers and redesign their logic?

Thanks.

Upvotes: 2

Views: 855

Answers (2)

sqlandmore.com
sqlandmore.com

Reputation: 163

Today I tried to create triggers on my Azure DB and it worked!!!! Very surprised - but happy! :)

create table testingTrigger (c1 int , c2 datetime default getdate())
go
create table testingTrigger2 (c1 int , c2 datetime default getdate())
go
create trigger testingInsertTrigger on testingTrigger
for insert
as begin
    insert into testingTrigger2 select * from inserted
    print 'this is triggered by insert on testingTrigger'
end
go

insert into testingTrigger(c1) select 1
insert into testingTrigger(c1) select 2
insert into testingTrigger(c1) select 3
go
select * from testingTrigger2
go

Conclusion - AS A TEMPORARY SOLUTION - I'll use it for now, no urgent need to remove the triggers in order to move MSSQL DB to Azure BUT my guess it, it can be unsupported/deprecated any time, so i'll prepare myself.

For anyone who is having the same troubles - enjoy it while it lasts!!!

Upvotes: 1

Jason Haley
Jason Haley

Reputation: 3800

It sounds like what your are looking for are options to occasionally run logic in Azure. By this, I'm assuming that your triggers logic doesn't have to be run right after a db call is made. This means you'll first need the following:

  1. Some way to tell if the 'create' trigger logic should be processed
  2. Some way to tell if the 'delete' trigger logic should be processed

If you can do this with queries on your data without changing/rewriting your logic (as requested) then you have a few options to get that logic run.

Option 1 WebJob - if you already have a WebApp, you can create a scheduled webjob to run ever so often to check if the create/delete trigger logic needs run and then run that sql in another manner besides a trigger.

Option 2 Automation Runbook - if you don't currently have a site or compute running in Azure, then you can use an Automation Runbook to do the checking for the create/delete trigger logic and do what needs done.

Option 3 WorkerRole or VM - if you already have a WorkerRole or VM running in Azure, you could just add the trigger checking logic to be run using the machine's taskscheduler.

Upvotes: 1

Related Questions