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 |
|
mattboy_slim
Yak Posting Veteran
72 Posts |
 Posted - 2012-06-05 : 14:40:09
|
| *If this belongs in a different forum, please move the topic. Since this is a bit of an open question I didn't want to presume that it would be better in another category*We have a database consisting of a large amount of public and private data. We have developers wanting access to the public data, but I would rather not have them even access the server. What I would like to accomplish is the periodic (hourly, instantly, whatever) copying of the data from specific tables to a new database on a new server. I will just set up another SQL server on the LAN to make this possible.So my question is: what is the best way to accomplish this? Mirroring doesn't seem appropriate after about 3 minutes of research, but I found others using dtswizard.exe to accomplish similar things.A couple of notes: The target database can be read-only from the standpoint of the developers, and UPDATES to the target database would be allowed (in other words, the exact content of the tables would always be exactly the same). Finally, I think, the target database could never update the source database. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2012-06-06 : 15:12:18
|
| Thanks, I'll head down that path. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-06 : 15:28:01
|
quote:
Originally posted by mattboy_slim
... Mirroring doesn't seem appropriate after about 3 minutes of research , but I found others using dtswizard.exe to accomplish similar things....
3 minutes of research ???After Monday and Tuesday even the calendar says W T F .... |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-06 : 15:31:51
|
| For transactional replication you should need primary keys in all tables which you want to replicate as articles.If those are not there then you cannot those tables as part of replication.You could try Log shipping.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-06-06 : 17:45:59
|
| MattboyThere are multiple ways to accomplish.. One option is to use replication to copy the data. However, it may take more administration than you're planning. Replication is great for managing a consistent and timely copy of the data.Another option is to setup a SQL Server job that will run a SQL script to insert into your target table using a select from your linked server.You could also use SQL Server Integration Services (SSIS). You would create a SSIS package where you would build a data flow that transfers your data from the source table to the target table. You wouldn't need a linked server for this approach, because your data sources are defined within the SSIS package. And, you can use a SQL Server job to schedule the package run times. |
|
|
|
|
|
|
|
|
Copying specific tables hourly to another server?
Tara Kizer