August 16, 2010 at 7:19 am
Hi, I have a scenario that I'm not sure will fully work, but I'm hoping to get some of your feedback to see if it's realistic.
We have a piece of software that uses SQL Server 2008. However, per our licensing agreement, we can't make any changes to the database schema. We have plans to put the database on our database server (physical), which will eventually be clustered with another server so that all of our databases across applications are covered (btw, should this other one be the same exact specs, or can it even be virtualized?). Anyway, once they're clustered, I know we can have load balancing and failover. Is it possible to have transactional replication so that data from this particular application's database on the cluster be sent to a third database (possibly on a different server)? The reason I ask is because, since we can't place triggers on the delivered database, I was hoping we could replicate it (aside from the cluster) and define the triggers on a separate database altogether. Data will only be flowing in one direction from this database. The triggers update our ERP system (which is not SQL, but is connected via OLEDB). To sum it up, here are my questions:
1) In a SQL cluster, can the second server be virtualized, or must it match the other server in all ways?
2) Once a cluster has been built and load balancing and failover implemented, can transactional replication take data from the cluster and send it to a separate database?
3) If #2 is possible, will the data being sent over then execute the triggers defined in that third database? Is sp_check_for_sync_trigger at all applicable here?
4) We have no wiggle room in the licensing, so is there a better way to have this whole thing set up? The clustering of our database server is going to happen either way and I was thinking that sending the data from this one application's database to a separate one, where all of our triggers are defined, would be the best route.
Does that make any sense? Any feedback is greatly appreciated.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
August 16, 2010 at 4:00 pm
mikes84 (8/16/2010)
Hi, I have a scenario that I'm not sure will fully work, but I'm hoping to get some of your feedback to see if it's realistic.We have a piece of software that uses SQL Server 2008. However, per our licensing agreement, we can't make any changes to the database schema. We have plans to put the database on our database server (physical), which will eventually be clustered with another server so that all of our databases across applications are covered (btw, should this other one be the same exact specs, or can it even be virtualized?). Anyway, once they're clustered, I know we can have load balancing and failover.
SQL Server does not provide load balancing!!
mikes84 (8/16/2010)
Is it possible to have transactional replication so that data from this particular application's database on the cluster be sent to a third database (possibly on a different server)? The reason I ask is because, since we can't place triggers on the delivered database, I was hoping we could replicate it (aside from the cluster) and define the triggers on a separate database altogether.
OK, the thing with transactional replication is that published tables are required to have a primary key. If your table does not then it is not available for replication. Any objects (SP's, views, functions, etc) that depend on this table will not function correctly in the subscription database!
mikes84 (8/16/2010)
Data will only be flowing in one direction from this database. The triggers update our ERP system (which is not SQL, but is connected via OLEDB). To sum it up, here are my questions:1) In a SQL cluster, can the second server be virtualized, or must it match the other server in all ways?
you can cluster a physical and a virtual machine. Its important to make sure the RAM and OS version are identical!
mikes84 (8/16/2010)
2) Once a cluster has been built and load balancing and failover implemented, can transactional replication take data from the cluster and send it to a separate database?
SQL Server does not load balance. You may want to look at database mirroring instead of replication!
mikes84 (8/16/2010)
3) If #2 is possible, will the data being sent over then execute the triggers defined in that third database? Is sp_check_for_sync_trigger at all applicable here?4) We have no wiggle room in the licensing, so is there a better way to have this whole thing set up? The clustering of our database server is going to happen either way and I was thinking that sending the data from this one application's database to a separate one, where all of our triggers are defined, would be the best route.
Does that make any sense? Any feedback is greatly appreciated.
Mike
Without knowing more about your system detail I couldnt sensibly advise!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2010 at 10:26 am
Thanks for the response Perry!
I'm not sure I understand the difference between transactional replication and database mirroring, but I don't mind using mirroring on the cluster if that will keep the databases on the two servers in sync.
Once we have that in place, can there be a third server that is sent changes from either of the two databases in the cluster?
1) Cluster with database mirroring (server 1 + server 2)
2) 3rd server (with triggers on tables that will update the erp system)
3) erp system
Having the third server set up to receive data and execute triggers is the only way I can think of to have real-time updates to our erp system. We're not allowed to place triggers on servers 1 or 2. Is there a better way?
Also, thanks for the information about the RAM and OS for the cluster.
Mike
Perry Whittle (8/16/2010)
mikes84 (8/16/2010)
Hi, I have a scenario that I'm not sure will fully work, but I'm hoping to get some of your feedback to see if it's realistic.We have a piece of software that uses SQL Server 2008. However, per our licensing agreement, we can't make any changes to the database schema. We have plans to put the database on our database server (physical), which will eventually be clustered with another server so that all of our databases across applications are covered (btw, should this other one be the same exact specs, or can it even be virtualized?). Anyway, once they're clustered, I know we can have load balancing and failover.
SQL Server does not provide load balancing!!
mikes84 (8/16/2010)
Is it possible to have transactional replication so that data from this particular application's database on the cluster be sent to a third database (possibly on a different server)? The reason I ask is because, since we can't place triggers on the delivered database, I was hoping we could replicate it (aside from the cluster) and define the triggers on a separate database altogether.OK, the thing with transactional replication is that published tables are required to have a primary key. If your table does not then it is not available for replication. Any objects (SP's, views, functions, etc) that depend on this table will not function correctly in the subscription database!
mikes84 (8/16/2010)
Data will only be flowing in one direction from this database. The triggers update our ERP system (which is not SQL, but is connected via OLEDB). To sum it up, here are my questions:1) In a SQL cluster, can the second server be virtualized, or must it match the other server in all ways?
you can cluster a physical and a virtual machine. Its important to make sure the RAM and OS version are identical!
mikes84 (8/16/2010)
2) Once a cluster has been built and load balancing and failover implemented, can transactional replication take data from the cluster and send it to a separate database?SQL Server does not load balance. You may want to look at database mirroring instead of replication!
mikes84 (8/16/2010)
3) If #2 is possible, will the data being sent over then execute the triggers defined in that third database? Is sp_check_for_sync_trigger at all applicable here?4) We have no wiggle room in the licensing, so is there a better way to have this whole thing set up? The clustering of our database server is going to happen either way and I was thinking that sending the data from this one application's database to a separate one, where all of our triggers are defined, would be the best route.
Does that make any sense? Any feedback is greatly appreciated.
Mike
Without knowing more about your system detail I couldnt sensibly advise!
Mike Scalise, PMP
https://www.michaelscalise.com
August 17, 2010 at 10:43 am
mikes84 (8/17/2010)
Thanks for the response Perry!
you're welcome 😉
mikes84 (8/17/2010)
I'm not sure I understand the difference between transactional replication and database mirroring,
The 2 are vastly different. keeping it simple (forgetting distributors and witnesses),
With Replication you create publications consisting of articles (could be a table, view, etc), you then create a subscription to this article and depending on the replication type, changes to the Publication reflect on the subscription. The subscription database is in a state that allows it to be accessed by users.
With database mirroring you have a principal database and a mirror database. Before you implement mirroring, you take a backup of the principal and restore it to the mirror server. Once you have configured and started mirroring, changes are fed to the mirror database but it is not in a state where users may access it, unless you create a database snapshot.
Check BOL for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2010 at 9:40 am
This may already have been answered, but you can use database mirroring in conjunction with transactional replication, correct?
Mike Scalise, PMP
https://www.michaelscalise.com
August 18, 2010 at 10:04 am
yes, but only for publication database. BOL details this more!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply