UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reasons Not To Replicate Indexes In Replica, Any Versions    
 
   
detka123
post Feb 7 2020, 09:30 AM
Post#1



Posts: 251
Joined: 16-October 03
From: Chicago


Hi floks,
I'm very new to the subject, so finding answers online is difficult - you have to know what you're asking smile.gif
The story is that the company that's hosting our database is creating a Replica of that database for us.
I've recently found that the replicated tables don't have any indexes except for the primary keys.
I tried to create our replica's Diagram and it wouldn't do without FKye's .
I'm just wondering, is it something we could request to include in the replication or there is a good reason for not replicating indexes?
Thank you in advance

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
Jeff B.
post Feb 7 2020, 09:43 AM
Post#2


UtterAccess VIP
Posts: 10,379
Joined: 30-April 10
From: Pacific NorthWet


"Replica" has different meanings in different contexts. What db/system is being used to store the back-end data?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
detka123
post Feb 7 2020, 09:57 AM
Post#3



Posts: 251
Joined: 16-October 03
From: Chicago


It's replicating SQLServer database into SQLServer database, if I correctly understood your question.
The process, as I understand it, is completely overwriting tables, not synchronizing data.
This post has been edited by detka123: Feb 7 2020, 09:59 AM

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
FrankRuperto
post Feb 7 2020, 10:05 AM
Post#4



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi Detka,

Replica, as in an exact copy of the local database, or is the local db being converted, for example, Access to SQL-Server hosted in the cloud?
This post has been edited by FrankRuperto: Feb 7 2020, 10:06 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
detka123
post Feb 7 2020, 10:10 AM
Post#5



Posts: 251
Joined: 16-October 03
From: Chicago


Hi FrankRuperto,
My understanding, it's a copy of the local database. Only difference - it contains a fewer number of tables.
In that sense it's not a total copy of the database, but only tables that we've requested to replicate.
Actually, as I'm thinking of it, that could be the reason why they didn't replicate the indexes - because some of them wouldn't be found in the replica database because not all of the tables are present...
Am I thinking in the right direction?
This post has been edited by detka123: Feb 7 2020, 10:14 AM

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
FrankRuperto
post Feb 7 2020, 10:24 AM
Post#6



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Not having all the tables could be the reason, but what about the tables that are being hosted, don't they need to be related to any other hosted tables?
Will the local tables sync the hosted subset?
Is the purpose for hosting a subset of the tables is so they can be shared in a Wide Area Network, or to be used for a web app?
This post has been edited by FrankRuperto: Feb 7 2020, 10:25 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
cheekybuddha
post Feb 7 2020, 10:30 AM
Post#7


UtterAccess Moderator
Posts: 12,315
Joined: 6-December 03
From: Telegraph Hill


The only advantage I can think of not to re-create the indexes on the replica is to keep replication lag to a minimum, since inserting/updating indexed fields is more expensive.

I guess the idea is that since all the operations are replicated from the master then they can only be 'legal' operations and so you don't need to worry about referential integrity - it will have been taken care of on the master, and you're unlikely to end up with orphan records

However, you normally have a replica for read-only operations, and since you will be searching on the data I would imagine you would benefit from having indexes.

If the replica is for a point-in-time backup (eg runs 10 minutes behind master) in case anything disastrous happens on the master (eg 'DELETE FROM Contacts;' instead of 'DELETE FROM Contacts WHERE ContactID = 123;' blush.gif ) then I suppose having indexes are less important, since you will only be using it to restore to the master. This would be an unusual situation, because often in such cases of disaster you would just want to promote the replica to being master and make it read/write - in which case you would definitely want all the indexes!

What is the purpose of the replica?

--------------------


Regards,

David Marten
Go to the top of the page
 
detka123
post Feb 7 2020, 02:43 PM
Post#8



Posts: 251
Joined: 16-October 03
From: Chicago


The purpose of replica is basically for us to be able to create our own custom reports and processes that wouldn't affect the master's performance.
Yes, replica is Read Only database and it's being replicated several times a day.
I would love to have indexes in these tables for better performance and to be able see relations.
It's not always easy to follow the data dictionary.
I did recreate some tables and was able to recreate some indexes (with some modifications to the SQL) in Access database... but it's not the same.

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
detka123
post Feb 7 2020, 02:44 PM
Post#9



Posts: 251
Joined: 16-October 03
From: Chicago


Frank,
By "hosted" do you mean the tables in the Master database?

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
cheekybuddha
post Feb 7 2020, 03:04 PM
Post#10


UtterAccess Moderator
Posts: 12,315
Joined: 6-December 03
From: Telegraph Hill


If you are using the replica for querying against I would suggest you tell your DBA's that the indexes are necessary. pullhair.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
detka123
post Feb 7 2020, 03:11 PM
Post#11



Posts: 251
Joined: 16-October 03
From: Chicago


Thanks cheekybuddha smile.gifsmile.gif
I would prefer to have them replicated, just didn't know if it's something that I can request with replication.

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
FrankRuperto
post Feb 7 2020, 03:27 PM
Post#12



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi Detka,

I mean hosted, as in the replica db hosted in the cloud. So if for some reason indexes and primary key to foreign key relationships are not possible in the replica, perhaps you could create views on the replica data you need to compensate for the performance loss in having to do full table scans. What are you using as a frontend to the replica db? If its Access, then use AccessSQL to populate local temp tables that have indexing and relational joins with just the data you need in order to generate the reports?

EDIT: For all you know, there might be Primary to Foreign Key relationship constraints, and you are just not able to see them locally?
This post has been edited by FrankRuperto: Feb 7 2020, 03:33 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
detka123
post Feb 7 2020, 04:09 PM
Post#13



Posts: 251
Joined: 16-October 03
From: Chicago


Frank,
I looked at the INFORMATION schema, then I asked the db-guy whether or not they are replicating indexes (that's because I didn't see them) and he said "no".
As far as the front end, we were thinking to use Crystal Reports, but I'm also very experienced in Access.
Our providers told me that the views or procedures built by us in replica would only be available until they run "snapshot" replication, which will overwrite the entire database and I can kiss goodbye to all of my precious customizations...

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
cheekybuddha
post Feb 7 2020, 04:55 PM
Post#14


UtterAccess Moderator
Posts: 12,315
Joined: 6-December 03
From: Telegraph Hill


@Detka,

Script all you SP's and db modifications. You can then re-run them every time a new replica is created.

I got a different idea of what you meant by a replica - I thought you meant a real-time replica that updates with the master, rather than a point-in-time snapshot.

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Feb 7 2020, 05:02 PM
Post#15



Posts: 652
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Why would the providers zap the entire db image and not just re-image the tables? So if you are not able to create persistent views and procs on the replicated db, then your best bet is to populate local temp tables with the data you need for your reports, unless we're talking about lots of data. I would think they have a script for bulk loading or restoring a backup of the tables to the repli db and they could include in that script creating the indexes, relationships, views and procs you need? If that's not possible, then why not just grab the data you need from the source instead of using the repli db? Are other users going to be running different reports on these snapshots, or are you the only user?
This post has been edited by FrankRuperto: Feb 7 2020, 05:12 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
detka123
post Feb 13 2020, 09:17 AM
Post#16



Posts: 251
Joined: 16-October 03
From: Chicago


Unfortunately I wasn't involved in the technical site of the project of replicating the database. I can't really say too much of the scripting or the replicating plans.
I only have access to what's already done and not much info about how...
It has some very large tables - that I can say.
There probably will be just a link to this database from another db.... and as I've been just told, nobody else will be using it for reports.


--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
detka123
post Feb 13 2020, 09:18 AM
Post#17



Posts: 251
Joined: 16-October 03
From: Chicago


Thank you so much for your help!
I think, the best way to go is to create a script somewhere safe and then to rerun it.
Thank you for the idea!

--------------------
Detka

“It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.” - M. Twain
Go to the top of the page
 
cheekybuddha
post Feb 13 2020, 09:24 AM
Post#18


UtterAccess Moderator
Posts: 12,315
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Glad we could help!

thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2020 - 09:08 PM