September 8, 2008 at 1:17 am
Hi
i want to create the two database.In one database its only for the old information and another is current information.But i need operated on the two database from the UI.so that can u help instead of the creating the two database their provision to interacted with the data or how to create the two database and interactive with UI.
September 8, 2008 at 1:33 am
Sorry, I didn't understand that. Could you please explain a bit more what you're trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2008 at 2:18 am
sbk.net (9/8/2008)
Hii want to create the two database.In one database its only for the old information and another is current information.But i need operated on the two database from the UI.so that can u help instead of the creating the two database their provision to interacted with the data or how to create the two database and interactive with UI.
My eyes! my eyes! :w00t:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2008 at 8:27 am
Hi Mr Gail,
My Client want two SQLServer Database. In the Frist Database it will have old information and Second Database having Current Information Ok.
Suppose in the Old Information Database having data from 2000 - 2007 Information and Second Database on having 2008 Year Information.
When ever user want to retrieve the information from 2000 - 2008 information it should be fetch from the two Database.
Then the thing is developers are using only Current Database ConnectionString and also fetch the data on the Current Database.
How can i will help to the Developers.
September 8, 2008 at 8:36 am
You can write your stored procedures so that they reference tables in both databases. Or, if the developers are beiong lazy and writing ad-hoc SQL code, they can use 3-part naming to refer to objects in the other database.
SELECT ... FROM DBName.SchemaName.TableName ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2008 at 8:38 am
You could also create views in the current db that union tables across dbs (assuming they have the same structure).
create view1
as
select * from db1.dbo.tblname
union
select * from db2.dbo.tblname
_____________________________________________________________________
- Nate
September 8, 2008 at 8:41 am
If there's no chance of overlap (which sound like there isn't in this case), that should be union all. Union means that SQL will do a distinct sort of the data after the union.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2008 at 9:31 am
I'm not sure whether this will help you... Just give a try..
For example take ASP as front end..
Declare two connection strings with one pointing to Old database and other to new database. Based of the frequency of which database you will be using mainly, Open connection to that database first and fetch the records...
You will be navigating through records like
do while not rs.EOF
so do a check for recordcount and if it is -1 then open connection to next database inside that loop... and still there is no record then return "No Matching Found" else return the matching records...
Feedbacks on this method are welcome...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 8, 2008 at 9:17 pm
Hi sbk.net,
It is cool that you can create two database and getting connected with the front end let it be ASP or anything and you can do such things by adding 3-part naming etc...etc...
But when you face 2009 what is your plan about old database and new database are you going to create database dynamically for each year or you'll add the data in 2008 to old database and hold 2009 in new database??
Regards
viji
September 9, 2008 at 4:20 am
:w00t: OMG!
Don't you see all this exchange can be traced down to a basic design concept which is the good-and-old production-environment/archiving-environment?
Even cavemen where able to do it after applying SP2 to MyStoneSQL v7000BC 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 10, 2008 at 10:11 am
PaulB (9/9/2008)
Don't you see all this exchange can be traced down to a basic design concept which is the good-and-old production-environment/archiving-environment? 😎
This is a simple architecture question about how and where to keep archived data. There are a few considerations about how much data is being accessed, how it is accessed, and are there times when both old and current data is required in one recordset. This is driven by the business needs the developers are trying to solve.
You might want to ask if you can be involved in the design systems when these things are set up to help decide how best to architect the data solution to the business requirements.
Too often we now are being asked to leave the technical solution out till after everything is decided. This is an error in the SDLC as we use to call it. Data people need to be involved when data is being discussed. That starts early and continues through projects.
You have options as to how you segment and store the data:
1. You could store all the data in one set of tables in one database and rely upon date selection to pull from the one tableset.
2. You could have both current and historical data in two sets of data within the same database
3. You could have two databases and use views with one to provide access to the other.
4. You could do it as you have it, and have two distinct collections and two distinct connection strings within the application.
Your developers for 1-3 could use one connection string to access the data.
For option 4 you could have two connection strings in the config file and allow them to select the one they want for processing.
No matter how you choose the developers should not have a hard time of it if they know how use the data in the config files.
Not all gray hairs are Dinosaurs!
September 11, 2008 at 4:15 am
Hi Everybody,
Thankz for the available Answers.
I will work on it. So that i will definitely get the Solution.
Once again Thankz for ur Answers.
regds
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply