SSMA and SYSDB

Question

SSMA and SYSDB

five_ten_fiftyfold

Ten Centuries

Points: 1161
More actions
January 19, 2012 at 3:17 am

#249842

We have a system that has been ported from Oracle using SSMA. I can find very limited info about permissions for the SYSDB database. I've noticed that there is a role called ssma_oracle which has execute permissions on all of the stored_procs and functions in the ssma_oracle schema. My hunch is that adding my product database users to this role will be all I need to do to provide the necessary functionality. I wondered if anyone else has experience of this or can point me in the direction of any documentation as I feel a bit like I'm working in the dark at the moment.
Thanks in advance...

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

five_ten_fiftyfold (1/19/2012)
We have a system that has been ported from Oracle using SSMA. I can find very limited info about permissions for the SYSDB database. I've noticed that there is a role called ssma_oracle which has execute permissions on all of the stored_procs and functions in the ssma_oracle schema. My hunch is that adding my product database users to this role will be all I need to do to provide the necessary functionality. I wondered if anyone else has experience of this or can point me in the direction of any documentation as I feel a bit like I'm working in the dark at the moment.
Thanks in advance...

The SQL Server Migration Assistant uses that database and the role when it's doing the migration;

you can remove both the database and the role when you are done, of course. I would think that once you've migrated the data, you don't need them anymore, so I'm not sure why you think you need to add anyone to that role.

We have an app that supports both oracle and SQL, but sometimes the data in Oracle is unique (business wise) and vice versa, so we want to test that data in both platforms and look for business bugs.

So for me, Oracle migrations are more of a one time thing, or converting a database to SQL so we can test the same data. Noone else needs to run the migration assistant but myself.

Are you doing one time migrations, or converting the same database over time, say once a week over and over again?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

five_ten_fiftyfold Ten Centuries Points: 1161 More actions · Answer 2

Thanks for response.

We still have many SYSDB dependencies in the application SQL profiler tells me this, also removing user's rights from SYSDB causes the application to fall over in a heap. I understand the development team have a task to recode the database to remove these dependencies but I imagine we're looming at years rather than months here.

Lowell SSC Guru Points: 323507 More actions · Answer 3

did you migrate your data into the [sysdb] instead of a different database?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

five_ten_fiftyfold Ten Centuries Points: 1161 More actions · Answer 4

Data definitely in the correct database. When setting up new installs where no migration is taking place we still need the SSMA extension pack and SYSDb to perform the conversions...

five_ten_fiftyfold Ten Centuries Points: 1161 More actions · Answer 5

five_ten_fiftyfold

Ten Centuries

Points: 1161

January 19, 2012 at 9:31 am

#1435997

anybody?

Baggy SSChasing Mays Points: 649 More actions · Answer 6

Similar to the above, I have a supplier who wants to install an app but requires the Oracle extension pack installed on the SQL server for compatibility between their app and the SQL server database. I'm assuming their application is still primarily aimed at Oracle but able to connect to a SQL database with the 'extensions' applied to the SQL server. I'm trying to work out if I install the extensions app onto a test server and then copy the sysdb to the production SQL server if that'll be enough as I'm opposed to installing an App onto my production SQL server. They say they won't be doing any actual migration type work as part of the installation, it is just for compatibility. I'm waiting on their response but thought I'd add my post here in case someone has a better handle on how this package works post migration.

five_ten_fiftyfold Ten Centuries Points: 1161 More actions · Answer 7

collinsr (3/8/2012)
Similar to the above, I have a supplier who wants to install an app but requires the Oracle extension pack installed on the SQL server for compatibility between their app and the SQL server database. I'm assuming their application is still primarily aimed at Oracle but able to connect to a SQL database with the 'extensions' applied to the SQL server. I'm trying to work out if I install the extensions app onto a test server and then copy the sysdb to the production SQL server if that'll be enough as I'm opposed to installing an App onto my production SQL server. They say they won't be doing any actual migration type work as part of the installation, it is just for compatibility. I'm waiting on their response but thought I'd add my post here in case someone has a better handle on how this package works post migration.

I don't have a set up like yours. We're a software house who traditionally made our apps in Oracle flavour. To meet customer demand they've been ported to SQL Server. I'm not sure if the extensions app does anything other than create the SYSDB database, there's no app as such and it doesn't run any services but there may be a dll or two deployed. There seems to be a vanishingly small amount of documentation online WRT SSMA so a lot of what I / we've done has been trial and error. Sorry I can't tell you definitively but good luck.

guy.jackman SSC Journeyman Points: 75 More actions · Answer 8

I think it probably depends on the Oracle features that you had in the source database. In my case, and I am just testing this for the first time this week, the sysdb is most definitely not optional. There are a ton of compatibility features included in the sysdb database. My first example, that I just ran into, is the Oracle sequence emulation. If you migrate the database schema from Oracle and it includes sequences, I think it creates tables in your migrated database that track the current sequence values. It also has a function and sp that are used to generate and manage the sequences. The function and stored proc are both located in the sysdb database.

Because of this, I would wholeheartedly disagree that you can drop the sysdb database after the migration is complete. That is, unless "migration complete" means that you have manually modified the schema to remove any dependancies that it has created. I would guess that the schema would have had to be quite basic for this to not be required.

I haven't figured out yet what exactly are the right privileges required.

Baggy SSChasing Mays Points: 649 More actions · Answer 9

It was a while ago, but in my case it was just a case of dropping the database onto the server. I generated the database by installing the software on a test server. With the database in place the supplier app was happy.