January 19, 2012 at 3:17 am
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...
This is a high-class Bureau-de-Change.
January 19, 2012 at 3:49 am
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
January 19, 2012 at 3:58 am
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.
This is a high-class Bureau-de-Change.
January 19, 2012 at 4:10 am
did you migrate your data into the [sysdb] instead of a different database?
Lowell
January 19, 2012 at 4:15 am
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...
This is a high-class Bureau-de-Change.
January 19, 2012 at 9:31 am
anybody?
This is a high-class Bureau-de-Change.
March 8, 2012 at 7:41 am
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.
March 8, 2012 at 8:13 am
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.
This is a high-class Bureau-de-Change.
March 5, 2014 at 3:40 pm
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.
March 7, 2014 at 10:50 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply