Microsoft Dynamics Forums Homepage

Forum Home Forum Home > Microsoft Dynamics GP (Great Plains) > GP - Installation and Administration
  New Posts New Posts RSS Feed - Creating DYNAMICS security database - SQL 2005
  FAQ FAQ  Forum Search   Register Register  Login Login


Creating DYNAMICS security database - SQL 2005

 Post Reply Post Reply
Author
Message
andyl78 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 25 2010
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote andyl78 Quote  Post ReplyReply Direct Link To This Post Topic: Creating DYNAMICS security database - SQL 2005
    Posted: August 25 2010 at 11:32am
Hi,

I'm currently changing our Dynamics GP application to use SQL 2005 instead of SQL 2000.

I have changed the datasource named 'Dynamics' on our application server to point at the new SQL 2005 instance, instead of the existing SQL 2000 one. This worked fine and the data source tested ok with no problems.

Currently the SQL 2005 is a blank instance with no Dynamics databases or logins created on it, which is what we want. On this I want to create a new DYNAMICS security databases and then create all the company databases.

To do this, I'm running 'GP Utilities' from the application server and pointing at the 'Dynamics' data source. I'm logging in with the 'sa' username and password. This is coming up with the error message ' The database setup has not been completed for Microsoft Dynamics GP. Use Microsoft Dynamics GP Utilities to complete the database setup before starting Microsoft Dynamics GP'.

The version of Dynamics GP we are using is 9.00.0259 and the version of SQL is 9.00.4266.00.

Any ideas why this isn't working? Does anything else need to be setup?

Thanks,

Andy
Back to Top
Sponsored Links


Back to Top
Vaidhyanathan M View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: December 28 2006
Location: India
Status: Offline
Points: 63
Post Options Post Options   Thanks (0) Thanks(0)   Quote Vaidhyanathan M Quote  Post ReplyReply Direct Link To This Post Posted: August 25 2010 at 12:45pm
Hi Andy,

I would have preferred an Upgrade from SQL 2000 to SQL 2005, in this scenario. I am not sure about any shortcomings in your case.

The reason because, you are not going to upgrade GP, but going to upgrade SQL. Having said that, SQL Server upgrade would have done the complete job of upgrading GP Databases also for you.

You don't have to do this on Production Server directly. Following would be my preferred method:

1. Create a temporary SQL 2000 Instance on a separate machine.
2. Backup DYNAMICS and GP COMPANY DBs from Production Server (SQL 2000).
3. Restore these DBs on to new temporary SQL 2000 Instance.
4. Run SQL 2005 Upgrade on this new temporary instance.
5. Step #4 would have upgraded all your existing DBs to SQL 2005.
6. Backup DYNAMICS and GP COMPANY DBs from temporary SQL 2005 (upgraded) instance.
7. Perform a fresh installation of SQL Server 2005 for your production server.
8. Restore the DBs backed up from Step #6.
9. Point your ODBC DSN to this SQL 2005.
10. Launch GP Utilities to make sure that it validates and confirms the existing GP Versions.
11. Start using GP on SQL 2005.

This method is often tested and confirmed method.

Hope that helps.
--
Thanks
Vaidy
Visit My Blog: www.vaidy-dyngp.com
Back to Top
andyl78 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 25 2010
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote andyl78 Quote  Post ReplyReply Direct Link To This Post Posted: August 25 2010 at 11:55pm
Thanks for your reply.

The reason why I wanted to create a new DYNAMICS security database is because the old SQL 2000 instance had more disk space and had about 10 company databases but on the new SQL 2005 instance I can only create about 2 company databases at the moment. Am I right in thinking that restoring an existing DYNAMICS database that references company databases that don't exist in the new instance will cause a problem? If so, how do I fix this problem?
Back to Top
Vaidhyanathan M View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: December 28 2006
Location: India
Status: Offline
Points: 63
Post Options Post Options   Thanks (0) Thanks(0)   Quote Vaidhyanathan M Quote  Post ReplyReply Direct Link To This Post Posted: August 26 2010 at 12:24am
Hi Andy,
 
In that case, you have to restore DYNAMICS & only required Company DBs, in my explained method. That means potentially we are talking about 3 DBs will be upgraded to SQL 2005 (DYNAMICS & 2 COMPANIES).
 
This will have only two kinds of impact towards your SQL upgraded GP Environment:
 
1. DYNAMICS will have records of unavailable Company DBs in SQL Server 2005, which quite ignorable. The only visible issue in this case will be, the users would see the list of 10 Companies when they log on to GP, but when they select any one which is not available it will throw an error. To avoid this error, we have a SQL script which will delete those orphaned records from DYNAMICS DB tables.
 
2. Intercompany Transactions (if you have such processes) which links to any of the unavailable Companies, would have drastic issues. You must be doubly confident that those companies which you are not planning to upgrade to SQL Server 2005, do not have any link with retained Companies on any Intercompany Transactions. If you do have Intercompany Processes, you MUST bring in all connected Companies to SQL 2005.
 
Hope the above gave you some ideas. Do keep posted about your queries.
 
--
Thanks
Vaidy
Visit My Blog: www.vaidy-dyngp.com
Back to Top
andyl78 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 25 2010
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote andyl78 Quote  Post ReplyReply Direct Link To This Post Posted: August 26 2010 at 4:16am
Thanks for your help, it's much appreciated.

Do you have a copy of the SQL script to remove all the orphaned records for those company databases that don't exist? Does this actually remove the companies from the list as well?

We don't currently use Intercompany Transactions, so this won't affect us.

Back to Top
Vaidhyanathan M View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: December 28 2006
Location: India
Status: Offline
Points: 63
Post Options Post Options   Thanks (0) Thanks(0)   Quote Vaidhyanathan M Quote  Post ReplyReply Direct Link To This Post Posted: August 26 2010 at 4:26am
 
The SQL Script will remove records from certain tables which contains the list of all companies and several other records.
 
But since this is an unsupported script by Microsoft, it works only to the extent of hiding Unavailable Companies from users to log on.
 
Send me a test email to this ID: vaidyDOTdyngpATgmailDOTcom
 
Will send you the script within a day.
--
Thanks
Vaidy
Visit My Blog: www.vaidy-dyngp.com
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down


Copyright 2013 microsoftdynamicsforums.com. All rights reserved. MicrosoftDynamicsForums.com is an independent non-Microsoft website.
Email: contact AT microsoftdynamicsforums DOT com