SSISDB

Question

SSISDB

Admingod

SSCertifiable

Points: 5997
More actions
August 8, 2019 at 6:38 pm

#3670008

I am looking to move SSISDB from old server to SQL 2016. Would the new server needs Integration services installed? I have the restored the database and everything looks good to me. There is any additional things would be needed to done?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Did you also recreate the Master Key? Without it, you won't get very far, as some of that db is encrypted.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Admingod SSCertifiable Points: 5997 More actions · Answer 2

I did not create master key.I restored the SSISDB database to new server. However, i noticed there is no environments is created on the original server. Does it mean that it's not working. I would have to check on other servers may be prod would have the environments created.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Are you sure about that?

When you create SSISDB, you are presented with this screen:

2019-08-08_16-04-50

After you complete this screen, the master key is created for you.

If you still don't believe it, try running this query:

SELECT name
      ,is_master_key_encrypted_by_server
FROM sys.databases;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Admingod SSCertifiable Points: 5997 More actions · Answer 4

So restoring the ssisb did not create the master key. Is the jobs run using sql agent for SSISDB? If yes, then i restore the SSISB to new server then the jobs would be running from the new server as well right and that could cause duplicates right. Did i understood right?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

So restoring the ssisb did not create the master key.

No, you have to do a backup/restore of the master key as a separate task.

Is the jobs run using sql agent for SSISDB?

SSIS ETL jobs are usually scheduled through SQL Agent, but that does not mean that this is the only way. SSIS packages can be called in many other ways (see here, for example).

If yes, then i restore the SSISB to new server then the jobs would be running from the new server as well right and that could cause duplicates right. Did i understood right?

Duplicate jobs? Yes, of course.

Duplicate data? That depends on what the SSIS packages do.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin