Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kcarbone1970
Yak Posting Veteran
52 Posts |
 Posted - 2015-01-14 : 13:25:26
|
| Hello, we've been working on a .Net application that queries a preexisting database. In our test environment i was using a backup of this database to create all the necessary queries. We even created a virtual machine to test the app in their network works beautifully. Now we plan on moving the app into a hosted environment. The client db is huge and has A LOT of tables, our ap only uses a small portion of those tables. So my thinking is that i recreate the database only using the necessary tables and indexes in our hosted environment then perform some scheduled nightly replication/synchronization of those tables from the two databases. The app doesnt create data only displays it, so the data would only be going one direction from the client db to our app db. So what are best methods to go about doing this? I was looking into Redgate SQL compare but not sure. I would appreciate some insight on the matter. ThanksCartesian Yak |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-14 : 13:40:51
|
| I would use Transactional Replication or Snapshot Replication.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2015-01-14 : 13:51:39
|
| what about permissions, ports, or anything else the clients needs to do on their endCartesian Yak |
|
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2015-01-14 : 13:55:31
|
| do the databases need to be identical?Cartesian Yak |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-14 : 14:12:24
|
| Replication allows for the databases to be different. Typically the tables being replicated are the same in the publisher and subscriber databases, but you can select a set of columns instead or even transform the tables.Regarding communication/permissions, the publisher needs to be able to connect to the distributor and the distributor needs to be able to connect to the subscriber. Often times, the distributor is also the subscriber or the publisher, just depends on your architecture, size of database, frequency of DML changes, etc. It uses the engine's port.For permissions: http://msdn.microsoft.com/en-us/library/ms151868.aspxTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2015-01-14 : 15:06:49
|
| I appreciate your input, thanks so much!Cartesian Yak |
|
|
|
|
|
|
|
|
Need advice on a SQL synchronization of two databa