• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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?
0
slamhound
Asked:
slamhound
  • 4
  • 3
1 Solution
 
nmcdermaidCommented:
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.
0
 
slamhoundAuthor Commented:
Will the DTS update the data or just copy everything across again?
0
 
johnclarke123Commented:
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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

You can then create a view in the form

CREATE VIEW MyView
AS
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

SELECT * INTO MyCopyTable FROM MyView
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nmcdermaidCommented:
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.
0
 
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?
0
 
nmcdermaidCommented:
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.
0
 
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?
0
 
nmcdermaidCommented:
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:


SELECT TableA.*
FROM
TableA
INNER JOIN
TableB
ON
TableA.ProductKey = TableB.ProductKey
AND (
TableA.ProductName <> TableB.ProductName
OR
TableA.Attribute2 <> TableB.Attribute2
OR
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:


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



Put it all together:


-- Clear the differential table
TRUNCATE TABLE TableC

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


-- Load any new products into the differential table
INSERT INTO TableC (ProductKey, ProductName, Attribute1, Attribute2)
SELECT ProductKey, ProductName, Attribute1, Attribute2
FROM TableA
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
SET
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!!!!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now