Hello,
We are researching whether the following scenario would be possible:
In an upcoming application release, we have to move some tables (Log
tables, look up tables, and a couple of secure tables) from database A
to database B.
Rather than wait and do everything all at once, and have no roll-back
plan should it fail, we'd like to create database B now, and start
moving those tables one by one over to it.
To ensure compatibility with the existing code-base, we'd like to
determine whether we can use updatable views to allow the current code
to continue to run against the existing DB.
Essentially, we would do this:
Given a table named LogTable In database A, we'd copy all of
LogTable's data to database B. (We'd look at the transaction log to
copy any changes made on rows modified after copying started.)
Then, we'd turn off the site for a few moments, and:
In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.L ogTable.
When we turn the site back on, updates and selects to LogTable would
physically pull from database B from now on.
I have already verified that performing selects and updates against a
view that refers to another physical database actually does work in
SQL 2K5.
My question is are there any pitfalls or things we should be aware of
that anyone else has experienced trying to do something like this?
Does it sound feasible?
Thank you,
Josh 3 1755
JoshG (js*****@gmail. com) writes:
To ensure compatibility with the existing code-base, we'd like to
determine whether we can use updatable views to allow the current code
to continue to run against the existing DB.
Since you apparently are on SQL 2005, I have a better idea: synonyms.
CREATE SYNONYM LogTable FOR databaseB.dbo.L ogTable
and you are done.
In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.L ogTable.
Using the transaction log would require that you have a log reader
like Lumigent's Log Explorer to read the log. But since would be a one-
off, it seems to me that you could use a trigger to pick up the changes
while the copy is running.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland,
Thank you for this response. I appreciate it.
There is one complicating factor I neglected to mention.
In several tables, we have columns currently poorly named "ID_ref" or
"CustomerID_ref ". In the new version of the database, we have simply
renamed these to ID or CustomerID.
Using a view, we can easily keep using the ID_ref but map it to the
new physical ID column in the new database.
From looking at synonyms so far, I don't think you can do this kind of
mapping. Do you know if that is the case for sure?
best regards,
Josh
On Dec 3, 5:52 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
JoshG (jsgo...@gmail. com) writes:
To ensure compatibility with the existing code-base, we'd like to
determine whether we can use updatable views to allow the current code
to continue to run against the existing DB.
Since you apparently are on SQL 2005, I have a better idea: synonyms.
CREATE SYNONYM LogTable FOR databaseB.dbo.L ogTable
and you are done.
In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.L ogTable.
Using the transaction log would require that you have a log reader
like Lumigent's Log Explorer to read the log. But since would be a one-
off, it seems to me that you could use a trigger to pick up the changes
while the copy is running.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
JoshG (js*****@gmail. com) writes:
There is one complicating factor I neglected to mention.
In several tables, we have columns currently poorly named "ID_ref" or
"CustomerID_ref ". In the new version of the database, we have simply
renamed these to ID or CustomerID.
Using a view, we can easily keep using the ID_ref but map it to the
new physical ID column in the new database.
From looking at synonyms so far, I don't think you can do this kind of
mapping. Do you know if that is the case for sure?
Right, if you are also changing column names or anything else, synonyms
are not for you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Marko Poutiainen |
last post by:
Situation:
We had to make our SQLServer 2000 database multi-lingual. That is, certain
things (such as product names) in the database should be shown in the
language the user is using (Finnish, Swedish or English). There are about
a dozen tables with columns that need localization.
Doing this in the application level was a no-goer. It would have taken far
too much time (there is a *lot* of code and unfortunately most of the...
|
by: KemperR |
last post by:
Hello Experts outhere,
may be someone can tell me whats going wrong with my ADOX trial.
I have an Access 2002 database with some tables and queries (views)
The code listed below works well up to the point where I want to add
the new view to the views collection. I get Runtime error 3001 which
is telling me
"Arguments are of wrong type,are out of acceptable range or conflict
with one another"
|
by: Johnny M |
last post by:
I have been using Access since office 4.3. I have just upgraded to Office
2003 and am having issues with creating updatable queries. If I link one
table to a selection query based on the same table, the resulting query is
not updatable. OR If I link 2 tables together that don't have a primary key
the result is not updatable.
I know I was able to do this in previous versions since I usually don't use
a primary key.
Thanks
|
by: Matt Alanzo |
last post by:
On another newsgroup an Access knowledgable party posted:
>You should be able to connect an Access ADP to an existing SQLExpress
>database running in SQLS 2000 compatibility mode. The only thing you
>won't be able to do is access any of the design surfaces for SQLS
>objects in the ADP. I checked with the Access team on this :-)
>
I appealed for clarification but without reply. Maybe somone on this
newsgroup can provide insight.
|
by: adolph |
last post by:
I created 2 tables, each with an autonumber primary key. Fields are:
ID (autonumber Primary key)
Number (single)
Color (Text)
FName (text)in one table and LName (text)in the other
What I'm trying to do is make a select query with not one but two
fields in the join, that will allow me to update the LName field . IE
SELECT FirstName.FName, LastName.LName, FirstName.ID, FirstName.Number,
| |
by: angellian |
last post by:
Sorry to raise a stupid question but I tried many methods which did
work.
how can I conserve the initial zero when I try to convert STR(06) into
string in SQL statment?
It always gives me 6 instead of 06.
Thanks a lot.
|
by: AP |
last post by:
Hello
I have a department full of Access databases. I am starting to think
about moving some of the larger ones to use a sql server backend. I do
not want to go throught the technical upsizing. I would just like to
have the SQL backend. Is there anything wrong with moving the tables to
SQL and simply linking to them in Access?
Thanks
|
by: ml_sauls |
last post by:
I've built a system to enter and manage purchase orders. This is in
use by >10 clients. Some use it in Access 97, most are in A2k. About
half use it through a Citrix implementation. It is separated into a
front end and 3 back ends (1 network for permanent storage, 1 local for
temp storage, and 1 local for storage for particular user).
One particular form is failing for a single client (Access 2000, using
Citrix). This form is a form...
|
by: Gary |
last post by:
Hello guys!
Bear with me, I am a newbie.
She is the Data Warehouse manager. She has about 50 users to use the
Oracle database from M$ Access via ODBC connection. All those users
have only SELECT privileges on certain tables. I built all the roles
and users for them and they work fine.
Then she asked "Why do YOU let them see all those system tables?",
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |