Automation for several databases

julkifli33

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

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

But the first question is "why?"

8)

bbrown

David Singleton wrote:

But the first question is "why?"

8)

That was my question also.

julkifli33

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

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

bbrown

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

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

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

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

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

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

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

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

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.