Schema Extraction For Multiple Interdependent Databases
Jul 23, 2005
I'm looking for a tool that can extract only the schema from a database
in a form that can be used to generate that schema in another empty
database. This is to facilitate our disaster recovery processes where
we need the objects only, not the data, and need to replicate this to
our disaster recovery site over the WAN. There are plenty of tools
that can handle a single database, but does anyone know of any tools
that could handle multiple databases where many of the objects (stored
procs and views) are dependent on objects in other of the databases
(tables). This is a home-grown ETL suite so making changes to the code
to remove these dependencies would take way too much effort. I am
looking for something that can either extract the schema for all 3
databases and handle the object creation ordering to account for the
dependencies (a simple method would be to extract by object type across
all databases, e.g. tables for all dbs before views before procs), or a
backup/restore tool that allows you to restore the objects only without
data. Worst case we could write something to generate the DDL or use
SQL DMO, but ideally we would prefer to purchase a (relatively
inexpensive) tool to do it.
Thanks,
Simon
View 3 Replies
ADVERTISEMENT
May 10, 2005
I am using SQL Server 2000.
I am working on a project where there will be multiple databases on a single instance of SQL Server. Each database will have the exact same schema but will be accessed by different groups of users.
What is the easiest way to sychronize changes between the databases, so that if I add a column to one database, it will be reflected in the other databases. If I add, remove, or alter a stored procedure, I want the change to be made in the other databases. I want the data in each database to remain isolated. In other words, I do not want replication of the data, only the schema of the databases. I would like to have a single "master" database that I use to make any schema changes and all the other databases be schema mirrors of this database each with their own data.
I have looked into SQL Server replication, but this didn't seem to work the way I wanted and I wasn't able to publish column changes etc.
View 3 Replies
View Related
Nov 8, 2015
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
View 2 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Jan 25, 2007
Hi all.
I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later time and replication was paused, but users continout to insert data indepentedly at the two servers.)
I need to get them up and running.
I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.
When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .
I so far managed to have merge replication running on two identical databases (schema and data).
Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes. I set the identity seed and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at puplisher and subscriber.) Is that ok? is that the way to do it?
Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.
Thanks allot for any help
Version.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
View 5 Replies
View Related
Sep 11, 2015
I need to create an alert for sending email alerts when schema changed in any databases.At my side: Server 2012 R2 enterprise.37 databases are there.
View 6 Replies
View Related
Jul 23, 2014
So we have our HA group servers and databases, now we want to deploy schema changes to the HA group databases.
1.) Can we deploy the changes to Server2.mydatabase whilst still having Server1.mydatabase available to users?
2.) If yes, what is involved in doing so
3.) If no, What is the best suggestions to apply schema changes to HA databases.
View 1 Replies
View Related
Feb 16, 2008
Ok so i've got a database containing a table called Quote.
I need one of the field's datatype to be Boolean?
which option do i choose?
and also is there a way to make the Key Field auto increment?
And is the datatype: ntext, the correct option for a text only field?
thanks
View 6 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Aug 14, 2012
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies
View Related
May 23, 2008
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View 3 Replies
View Related
Aug 17, 2006
Hi,
I need to change the schema of the stored procedures of several databases.
Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?
thank you
View 4 Replies
View Related
May 10, 2006
I want to delete a user on local but in order to do that I apparently need to delete the schema. The schema has many objects in it. Can I and what is the syntax to move multiple objects from let's say XXX to dbo so I can delete the schema XXX
Thanks!
View 3 Replies
View Related
Apr 16, 2007
Hi,
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
Thank you,
The Captain
View 3 Replies
View Related
Apr 17, 2007
My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.
View 3 Replies
View Related
Apr 22, 2015
I want to create multiple partition schema on a single table.
For example - i need to create partition base on region id and Territory Id.
View 2 Replies
View Related
Apr 15, 2015
How to read multiple excel sheets in same excel file with different table schema.
Basically need to load data into tables from these excel sheet.
So I know how to dynamically read multiple excel sheets in same excel file with same table schema and load into one table.
But how to do this dynamically for multiple excel sheet with different table schema and load into different tables?
View 7 Replies
View Related
Jul 8, 2004
Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
View 12 Replies
View Related
May 18, 2007
Hi All
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
regards
sasan
View 5 Replies
View Related
Feb 7, 2007
Hey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
View 1 Replies
View Related
Jul 16, 2007
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies
View Related
Nov 11, 2007
How can I include tables and views from database A when building a view in database B, if possible?
Same for stored procedures.
View 1 Replies
View Related
Dec 23, 2005
so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
View 1 Replies
View Related
Jun 14, 2001
We have a number of databases(on sqlserver 7.0) which has the same structure but with data for different users. We would like to change the structure of the databases with minimum effort and with minimum down time.
Is there any method to automate the change so that the change is reflected in all the databases when a single database structure is changed?
Any suggestions in this regard are welcome and urgent
View 1 Replies
View Related
Nov 1, 2000
Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?
Thanks!
View 1 Replies
View Related
Apr 5, 2004
Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
View 2 Replies
View Related
Feb 5, 2002
I need some guidance! We are running SQL Server 2000. We have 4 or 5 databases with the same schema, and I need to make sure that their structures all stay synched. We will have more soon. I also need certain tables to have the same data across all the databases.
I need a way to manage changes on the multiple databases! Right now I generate a script in Enterprise Manager, then run it in Query Analyzer on each database, manually. There's too much room for error here.
Any suggestions are appreciated! I am currently evaluating Embarcadero's products, but they are complicated and confusing - I'm not sure yet which of their tools is meant for this purpose.
Thanks,
Avi
View 4 Replies
View Related
Jun 25, 2002
I have a problem like this. I have an application which shud be internet hosted. All transactions should be updated to the central server of the net as well as an intranet DB server. (I'll have a DB server - SQL Server 2000 in each intranet). Is this possible.. i'm planning to use ASP to develop the net based application.
Thank you in advance..
Geetha R
View 2 Replies
View Related
Nov 9, 2007
Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
hope that makes sense. - Thanks
View 6 Replies
View Related
Aug 14, 2007
Could anyone possibly help me out with an issue I am having...
I need to copy all my SQL2005 databases from Server1 to Server2. How can I, using SSIS, copy all the databases and not just 1 specific one (database transfer task)???
My actual task is much deeper than this, but this is the main problem I seem to be encountering and I cannot find this anywhere on the web...
Over and above the what I have mentioned, is there a way to also specify a name of the target DB the copy will go through to?
e.g. Server 1 has 3 instances, each one with a different copy of the same DB.
I would like to copy all 3 copies to Server 2 running 1 SQL instance, but to target DB's like DB_dev, DB_prod and DB_test.
The latter section is not so crucial at the moment, but the first part is really an issues I am struggling with at the moment...
Any help would be greatly appreciated.
Thanking You in advance!!!!!
View 1 Replies
View Related
Jan 21, 2008
I'm very new to SQL, so you'll have to excuse the nubishness of this question. I've got my trusty O'Reilly book here!
We're using Microsoft SQL Server 2005 on our web server. I'm currently working on a project to get a few MS Access Databases available on the web page.
The web part is actually working fine, with the DBs exported into SQL for performance reasons. (From what I've read pulling data from the .mdb file could cause me problems in the future, so I'm tossing it all onto the SQL server from the start.)
Before I get into the project full-tilt, though, I'm curious about how best to organize all the data?
I'm using the Upsizing Wizard in Access for the conversions, and every new DB gets added under Databases. If this gets approved I may be looking at well over a hundred DBs which will eventually be converted and made web accessible.
I can keep everything neat and tidy on the web server by just tossing things in a few directories, but the menu on the SQL Server Management Studio is going to become a mess really quickly if I let it just keep adding to +Databases.
Is there a way to sort everything into a series of folder/groups, like how System Databases has it?
Thanks in advance,
-Lamune
View 2 Replies
View Related