Automation for several databases

julkifli33 · 2011-09-21

hi all ... i have case
for example i have 3 databases for NAV
then database 1 is the primary database for NAV
what i want is... database 2 and database 3 will follow what is in database 1 (only for GL Account table)
user will not input data in database 2 & 3... only database 1 will be keyed in

is it possible?
how should i do that?
thanks

kriki · 2011-09-21

That is possible, but you have some work to do.

There are a lot of ways how you can do it. Depending if you have SQL DB's or native DB's. If the SQL DB's are on the same server or not. The tables you want to copy.

When you answer those questions, I can focus on giving you methods on how to do it.
Some examples of solutions:
-NAS to export to text file and NAS to import in DB.
-TSQL MERGE-statement to copy records (very fast) (and optionally NAS to post the data in case of G/L Entries) (and optionally creating linked servers).
-Hire a person to copy the data manually (slow, costly and error-prone. I suppose this is what you want to avoid).
-Replication of SQL server (not so easy to implement, maintain and control).

David_Singleton · 2011-09-21

But the first question is "why?"

8)

bbrown · 2011-09-21

David Singleton wrote:

But the first question is "why?"

8)

That was my question also.

julkifli33 · 2011-09-22

kriki wrote:

That is possible, but you have some work to do.

There are a lot of ways how you can do it. Depending if you have SQL DB's or native DB's. If the SQL DB's are on the same server or not. The tables you want to copy.

When you answer those questions, I can focus on giving you methods on how to do it.
Some examples of solutions:
-NAS to export to text file and NAS to import in DB.
-TSQL MERGE-statement to copy records (very fast) (and optionally NAS to post the data in case of G/L Entries) (and optionally creating linked servers).
-Hire a person to copy the data manually (slow, costly and error-prone. I suppose this is what you want to avoid).
-Replication of SQL server (not so easy to implement, maintain and control).

it is in the same server
and it used SQL Server...
i think i will use this --> NAS to export to text file and NAS to import in DB.

bbrown · 2011-09-22

What's the business purpose/justification for this? If these are 3 NAV databases, does the need justify the additonal license cost?

bbrown · 2011-09-22

There's also 1 other possible approach here. Since you state you only need this for the "GL Account table". That is to replace the "GL Account" table in the "read only" databases with a linked table that is connected to a view of the updatable table. Of course this means you would not be able to run any functions that write to this table from the "read only" databases.

julkifli33 · 2011-09-22

bbrown wrote:

What's the business purpose/justification for this? If these are 3 NAV databases, does the need justify the additonal license cost?

i also not sure about this
but this one is request from my user

bbrown · 2011-09-22

julkifli33 wrote:

bbrown wrote:

What's the business purpose/justification for this? If these are 3 NAV databases, does the need justify the additonal license cost?

i also not sure about this
but this one is request from my user

Do they understand that 3 NAV databases means 3 NAV licenses? One NAV license allows you 1 NAV database. Now there are deals on multi-site licensing but they are still more than a single license.

Are we sure they mean 3 databases? and not 3 companies?

bbrown · 2011-09-22

julkifli33 wrote:

bbrown wrote:

What's the business purpose/justification for this? If these are 3 NAV databases, does the need justify the additonal license cost?

i also not sure about this
but this one is request from my user

What is your role with NAV? Are you working for an NSC, an independent consultant, or workign for an end-user?

David_Singleton · 2011-09-22

julkifli33 wrote:

bbrown wrote:

What's the business purpose/justification for this? If these are 3 NAV databases, does the need justify the additonal license cost?

i also not sure about this
but this one is request from my user

](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)

kriki · 2011-09-22

bbrown wrote:

There's also 1 other possible approach here. Since you state you only need this for the "GL Account table". That is to replace the "GL Account" table in the "read only" databases with a linked table that is connected to a view of the updatable table. Of course this means you would not be able to run any functions that write to this table from the "read only" databases.

I always try to avoid tables linked to a view. If you need to make a backup for development or testing, you get some problems because you need to update the view.
And changing a standard NAV table to point to a view is not a good idea. Especially if it is a master table.

bbrown · 2011-09-22

kriki wrote:

bbrown wrote:

There's also 1 other possible approach here. Since you state you only need this for the "GL Account table". That is to replace the "GL Account" table in the "read only" databases with a linked table that is connected to a view of the updatable table. Of course this means you would not be able to run any functions that write to this table from the "read only" databases.

I always try to avoid tables linked to a view. If you need to make a backup for development or testing, you get some problems because you need to update the view.
And changing a standard NAV table to point to a view is not a good idea. Especially if it is a master table.

I'm going to somewhat disagree with you here. In terms of the backup issue, I don't see the minor extra steps as a reason to not use a valid integration method in appropriate situations. With our clients I handle this by providing providing a detailed document on "creating a test DB" with "step by step" instructions including copies of the needed SQL scripts.

In terms of it being the right choice in this situation, I agree that you raise a valid point. Actually I think this whole thing is a bad idea. I question whether the business need is understood and therefore if the proposed solution has been thought thru. Do you really update COA that often where you need to automate the process?

David_Singleton · 2011-09-22

I like views. They are not for every situation, but there are some cases where they do a great job. The backup issue is fine if you are doing SQL backups, you just need to take an extra step to know the Database names and make sure you maintain the links. When you don't need the source data in the NAV database, its a better option than replicating.

Generally when I interface a Navision system to another SQL database, I use views to create "transfer" type tables. And then work direct in Navision to get the data posted into journals or what ever is needed. Its especially good when the other database does lots of silly stuff that you can't control (like Worldship for example).

I used to avoid them for performance reasons, but with experience you find that they can perform pretty well, and compared to the other over heads needed for replicating and storing data twice, the performance is just as good. I do recommend though to have both databases on the same server, it make life a lot easier.

kriki · 2011-09-22

David Singleton wrote:

I used to avoid them for performance reasons, but with experience you find that they can perform pretty well, and compared to the other over heads needed for replicating and storing data twice, the performance is just as good.

Especially if you can put an index on the view.

Automation for several databases

Comments

Categories