June 7, 2011 at 3:38 pm
If a database is in full recovery mode is it technically transacational? After all data is sent to transaction logs.
Or does the application code need to specify transactional locking levels for it to be considered transacational?
I ask this because one of the prerequisites for implementing replication is that the database is transactional. I am confused by the context of the terminology. Can anyone clear that up for me? Thanks.
June 7, 2011 at 4:08 pm
A transactional database is one that participates in updates, inserts, deletes of records. This can be in any of the recovery models.
There is a different topic of replication that involves a type of replication called transactional replication. Transactional replication copies each transaction (update, insert, delete) to a secondary database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 7, 2011 at 4:39 pm
kwoznica (6/7/2011)
If a database is in full recovery mode is it technically transacational? After all data is sent to transaction logs.Or does the application code need to specify transactional locking levels for it to be considered transacational?
I ask this because one of the prerequisites for implementing replication is that the database is transactional. I am confused by the context of the terminology. Can anyone clear that up for me? Thanks.
Let me try to clarify.
All databases should be transactional in the sense that data updates e.g. insert, delete and updates - is handled in transactions where either the whole transaction is commited or the whole transaction is rolled back.
In a different sense, some authors talk about "transactional databases" when referring to OLTP (On Line Transaction Processing) databases as opposed to DSS (Desicion Support Systems) which include but are not limited to Data Warehousing.
Reading your post it is clear the first, correct definition is the one you are looking for. All well behaved RDBMS handle transactions either implicitely or explicitely - actually most RDBMS allow for both approaches meaning that if you do not define a transaction explicitely the RDBMS will do it for you.
As a side note, backup strategy has nothing to do with transactions. On the other hand, I do prefer the application defining transaction properly, explicitely - this is what I consider best practice.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 7, 2011 at 4:51 pm
PaulB-TheOneAndOnly (6/7/2011)
kwoznica (6/7/2011)
If a database is in full recovery mode is it technically transacational? After all data is sent to transaction logs.Or does the application code need to specify transactional locking levels for it to be considered transacational?
I ask this because one of the prerequisites for implementing replication is that the database is transactional. I am confused by the context of the terminology. Can anyone clear that up for me? Thanks.
Let me try to clarify.
All databases should be transactional in the sense that data updates e.g. insert, delete and updates - is handled in transactions where either the whole transaction is commited or the whole transaction is rolled back.
In a different sense, some authors talk about "transactional databases" when referring to OLTP (On Line Transaction Processing) databases as opposed to DSS (Desicion Support Systems) which include but are not limited to Data Warehousing.
Reading your post it is clear the first, correct definition is the one you are looking for. All well behaved RDBMS handle transactions either implicitely or explicitely - actually most RDBMS allow for both approaches meaning that if you do not define a transaction explicitely the RDBMS will do it for you.
As a side note, backup strategy has nothing to do with transactions. On the other hand, I do prefer the application defining transaction properly, explicitely - this is what I consider best practice.
Hope this helps.
Thanks Paul. That does help alot.
Would it then be safe to say that as long as a database is in full recovery mode it can be used for replication?
June 7, 2011 at 4:55 pm
Transactions are individual items of work in a database. Generally RDMS implementations use transaction support to achieve the ACID principle. That is, every unit of work must be Atomic, Consistent, Isolated and Durable. every batch of SQL submitted is "all or nothing" the transaction either succeeds and gets written to disk or it fails then rolls back and leaves the database in the state is was before the execution started.
With regards to SQL replication setting up a transactional replication requires the database to be in Full recovery mode so that the transaction log can hold onto "transactions" until the replication engine has ensured the work has completed successfully on all the subscribers. Merge and Snapshot replication have different requirements.
June 7, 2011 at 5:24 pm
kwoznica (6/7/2011)
PaulB-TheOneAndOnly (6/7/2011)
kwoznica (6/7/2011)
If a database is in full recovery mode is it technically transacational? After all data is sent to transaction logs.Or does the application code need to specify transactional locking levels for it to be considered transacational?
I ask this because one of the prerequisites for implementing replication is that the database is transactional. I am confused by the context of the terminology. Can anyone clear that up for me? Thanks.
Let me try to clarify.
All databases should be transactional in the sense that data updates e.g. insert, delete and updates - is handled in transactions where either the whole transaction is commited or the whole transaction is rolled back.
In a different sense, some authors talk about "transactional databases" when referring to OLTP (On Line Transaction Processing) databases as opposed to DSS (Desicion Support Systems) which include but are not limited to Data Warehousing.
Reading your post it is clear the first, correct definition is the one you are looking for. All well behaved RDBMS handle transactions either implicitely or explicitely - actually most RDBMS allow for both approaches meaning that if you do not define a transaction explicitely the RDBMS will do it for you.
As a side note, backup strategy has nothing to do with transactions. On the other hand, I do prefer the application defining transaction properly, explicitely - this is what I consider best practice.
Hope this helps.
Thanks Paul. That does help alot.
Would it then be safe to say that as long as a database is in full recovery mode it can be used for replication?
Yes
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 7, 2011 at 11:47 pm
kwoznica (6/7/2011)
Would it then be safe to say that as long as a database is in full recovery mode it can be used for replication?
No.
Recovery model and replication have nothing to do with one another. There's no recovery model requirement for any form of replication.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2011 at 7:08 am
GilaMonster (6/7/2011)
kwoznica (6/7/2011)
Would it then be safe to say that as long as a database is in full recovery mode it can be used for replication?No.
Recovery model and replication have nothing to do with one another. There's no recovery model requirement for any form of replication.
Gail,
thanks for dropping in on my thread.
Let me rephrase my question since there are a couple of different thoughts.
How do I know if a database can be used in a replication scenario for any level of replication?
June 8, 2011 at 7:22 am
andersg98 (6/7/2011)
Transactions are individual items of work in a database. Generally RDMS implementations use transaction support to achieve the ACID principle. That is, every unit of work must be Atomic, Consistent, Isolated and Durable. every batch of SQL submitted is "all or nothing" the transaction either succeeds and gets written to disk or it fails then rolls back and leaves the database in the state is was before the execution started.With regards to SQL replication setting up a transactional replication requires the database to be in Full recovery mode so that the transaction log can hold onto "transactions" until the replication engine has ensured the work has completed successfully on all the subscribers. Merge and Snapshot replication have different requirements.
Merge replication is the ideal type. However I have read schema modifications are necessary and I have not seen anything that really explains what schema changes are necessary.
If merge replication is not possible because of schema changes then transactional replication would be the next ideal scenario. The confusion I have with transactional replication is whether or not the entire database can be replicated and can it be read only?
June 8, 2011 at 7:33 am
andersg98 (6/7/2011)
With regards to SQL replication setting up a transactional replication requires the database to be in Full recovery mode so that the transaction log can hold onto "transactions" until the replication engine has ensured the work has completed successfully on all the subscribers. Merge and Snapshot replication have different requirements.
That is not true. Transactional replication works in all recovery models. The log records won't be removed from the log until they have been marked as replicated, doesn't require a specific recovery model for that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 12:48 pm
GilaMonster (6/8/2011)
Transactional replication works in all recovery models.
I suppose the best thing to do would be to test it in a test environment and see how it goes. We use Dynamics Navision as our ERP and I see so many issue with it.
I was hoping I would find something during research which would tell me if there are any structural hurdles to overcome marking whether or not it is possible before I spend time on setting up a test environment.
June 9, 2011 at 2:35 pm
you definitely want to do any changes like setting up replication in a test environment. I can tell you that I use transactional replication with my very large Dynamics GP system and the biggest issue I've had to date is that with the way GP and ISV's do schema mods I have to drop articles prior to the change and add them back afterwards.
Meredith
June 15, 2011 at 8:30 am
Meredith Ryan-Smith (6/9/2011)
you definitely want to do any changes like setting up replication in a test environment. I can tell you that I use transactional replication with my very large Dynamics GP system and the biggest issue I've had to date is that with the way GP and ISV's do schema mods I have to drop articles prior to the change and add them back afterwards.Meredith
Thanks for your insight Meredith. I'm hoping to get a test environment up this week.
In regards to your replication do you replicate the entire database or just specific tables for your publications?
June 20, 2011 at 1:06 pm
Another question I have is on what actually gets replicated. Are just tables typically replicated in either transaction or merge replication or do entire databases get replicated?
What have some of you done in your scenario's?
June 20, 2011 at 1:17 pm
I've done both in the past - what you replicate is going to depend on what your needs are and why you are replicating in the first place. If it's for a HA\DR scenario you will likely want entire databases, if it's just to get data to another instance then you might just want select tables.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply