View Replication

I have a SQL database where I may be only given access to a View and all the other data is restricted from me.

Can I replicate that view so that it becomes a table (or set of tables) on the second SQL server?

If not, what's the best way to get all the data from the view to the remote SQL server regularly?
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yes you just load the data into a table on the secondary server.

There are a number of methods to do this, the easiest one being DTS, just go through the Import data wizard and choose your view as the source, and your new server as the destination. Of course you will need a database to put it into.

YOu can then schedule your DTS to run every ten minutes.
slamhoundAuthor Commented:
Will the DTS update the data or just copy everything across again?
Bit unclear what you mean by 'replicate'.  Do you want to have live access to up-to-date data, have a copy you can alter, or what?  If you're not going to make ad-hoc changes to the data, remember a view is treated just like a table in most respects.

If you want to have a current version of the view, you have a couple of options, one of which is to add a linked server using sp_addlinkedserver from the second machine.

See Books Online or the following:

You can then create a view in the form

SELECT * FROM otherserver.dbase.dbo.view

You can then create further views on MyView, or if you want to make a copy of the data to make changes to, do something like

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

If you use the wizard tp create the DTS then I think it clears it and loads the data everytime.

Its pretty simple to alter the DTS to only refresh a given time window, say the last few days.

The question is, why do you need to do this? You can just use the original vie wlike a table if need be.
slamhoundAuthor Commented:
The setup is that I have a database with several hundered thousand products, not to mention the number of clients, at the head office database. We want to allow people (clients) to order stuff (products) online. This means that we need a copy of a view of the data at the web host end as the author of the software doesn't want to give us full read/write rights. At the same time, we don't want to copy all the data every time because of speed/bandwidth and we don't want the website to try to access the head office data because of security.

Any more solutions or ideas?
Can you clarify how the clients come into this? Are they something else that needs to be copied over and if so are they in the same view as the products or are they in a seperate view.

It makes it MUCH easier if there is an 'Updated/Inserted' audit field on the product table. Then you could just always copy over anything that changed in the last few days. But I doubt very much that you have this field in your view.

Therefore an alternative method is outlined below:

Assuming that:
-ServerA is your source database (with the view)
-ServerB is the database server that your web server accesses (incidentally its not recommended to put the SQL Server on the web server)
-there are bandwidth limitations between ServerA and ServerB
-you can't make any changes to the database on ServerA.

Then I suggest a solution like this:

1. Create a new db on ServerA. This database will be used to 'stage' the data before it is sent over to ServerB.
2. the new db contains three tables:
     TableA is simply a dump of your source view
     TableB is a list of product keys and/or attributes that already exist in ServerB
     TableC is a a 'differential' table - it holds any new or updated records - effectively what needs to be sent to ServerB
3. Every day, you do the following:
    1. Clear and Repopulate TableA from the view.
    2. Find the difference between TableA and TableB - for the first run this will actually be everything
    3. populate the differential table (TableB) with the difference between these two tables
    4. Send the differential table to ServerB which appends or updates accordingly.
    5. Ensure that TableB is also updated according to the differential.. alternatively just copy TableA over TableB

Its all very abstract at this stage but I just want to run it by you before I go into specifics.
slamhoundAuthor Commented:
I don't know what form the customer data will take at the moment but if we can get things running for products I'm sure I can work the customers out.

That's a very nice idea! That would limit the big bandwith to within server A and give us a non-read only database that we could flag and modify as we saw fit.

Replicating Table B to the remote (web) SQL server should be easy. But what would be the best way to check for changes in the records between Table A and B?
The problem is to find the difference between TableA TableB

So really we want to find:

1. Any changed records between TableA and TableB (based on some kind of product key)
2. Any new records in TableA that aren't in TableB

Note that for these comparisons we need a unique product key. That is the only thing that is guaranteed to remain constant.

This query will find this list of products which have changed attributes:

TableA.ProductKey = TableB.ProductKey
TableA.ProductName <> TableB.ProductName
TableA.Attribute2 <> TableB.Attribute2
TableA.Attribute3 <> TableB.Attribute3

ie these are the products which match between the source trable and reference table on product key, but where one or more product attributes don't match.

So this is the list of products whose descriptions have changed.

This lists all the new products:

WHERE TableA.ProductKey NOT IN (
    SELECT ProductKey FROM TableB )

Put it all together:

-- Clear the differential table

-- Load any changed products into the differential table
INSERT INTO TableC (ProductKey, ProductName, Attribute1, Attribute2)
SELECT TableA.ProductKey, TableA.ProductName, TableA.Attribute1, TableA.Attribute2
ON TableA.ProductKey = TableB.ProductKey
  TableA.ProductName <> TableB.ProductName
  TableA.Attribute2 <> TableB.Attribute2
  TableA.Attribute3 <> TableB.Attribute3

-- Load any new products into the differential table
INSERT INTO TableC (ProductKey, ProductName, Attribute1, Attribute2)
SELECT ProductKey, ProductName, Attribute1, Attribute2
WHERE TableA.ProductKey NOT IN (
    SELECT ProductKey FROM TableB )

-- We now have all changed or new products listed in TableC. This can be sent to ServerB where we perform the reverse process
-- You may wish to also put an Inserted/Updated flag on this differential table or you can rely on the the logic at ServerB to work this out.

And here is some example logic to load this table back in at the ServerB End:

-- Update changed products
UPDATE ProductTable
ProductName = DifferentialLoadTable.ProductName,
Attribute1 = DifferentialLoadTable.Attribute1,
FROM DifferentialLoadTable
WHERE DifferentialLoadTable.ProductKey = ProductTable.ProductKey

-- Insert new products
INSERT INTO ProductTable (ProductKey, ProductName, Attribute1)
SELECT ProductKey, ProductName, Attribute1
FROM DifferentialLoadTable
WHERE ProductKey NOT IN (
  SELECT ProductKey FROM ProductTable)

Now that I think of it there is probably a native replication solution within SQL Server that may also do this for you as well, but I have never used replication in SQL Server so I am not the one to ask!!!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.