Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Actually I got stuck while searching a materialized view approach in SQL Server. My problem was that I have two databases and I have created some views whose functionality is to read data from one DB to another.

For instance, a view in DB "A" is picking up the data from DB "B". It worked perfectly, but suddenly today I got Null pointer exception. When I traced the problem, I found that My DB "B" has crashed. I searched and found that in Oracle, there is support of this problem by Materialized views whose functionality is to restore the old view state in case of crashing of one DB. But I didn't find anything like this in SQL Server.

I am really stuck in this issue. Any help would be greatly appreciated.

share|improve this question
1  
What do you mean that the database crashed? Is it on a different SQL Server instance that became offline? – Martin Smith Jan 14 at 10:21
they are on different servers – despicable Jan 14 at 10:52
1  
So what are you trying to do? Avoid relying on B? Having a copy of B? Something else? – Aaron Bertrand Jan 14 at 13:13
@AaronBertrand I already told in question that I am finding a way same as materialized view of Oracle.In simple words , in sql I want to restore old state in case of crash(B). Right now SQL server default behavior is throughing NullPointerException in case of crash. – despicable Jan 14 at 14:12
2  
Keep in mind that it is not necessarily the case that your entire audience knows how materialized views in Oracle work, and it is unlikely they will go and become experts in a feature in another RDBMS in order to answer your question. – Aaron Bertrand Jan 14 at 14:18
show 1 more comment

migrated from stackoverflow.com Jan 14 at 9:43

1 Answer

up vote 1 down vote accepted

You could set up transactional replication from your main server to the server on which the views are materialized, and then, use a non materialized or materialized view based on their independent merits

More info:

You have server A and Server B, with A hosting DB A and B Hosting DB B

Now, you can configure transactional replication between A and B in such a way that Server A will have 2 Databases: A and B_Repl , similarly B will have B and A_Repl

The REPL databases are essentially an updated copy of the source databases. You can find many guides on how to configure replication on SQL Server

Once you have done that, you can create views on server A, and when you need to refer to database B, you refer to B_Repl instead. Similarly for server B

share|improve this answer
apprecaite your answer but i need detail w.r.t this. actually i need to set it up in MSSQL.Can you please further clear about the things you said? would they applicable in msSQl? if yes then how? – despicable Jan 15 at 4:01
@despicable updated answer with more info – Akash Jan 15 at 6:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.