SQL Server - Manage Multiple Databases With Same Schema
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.
I'm looking for a tool that can extract only the schema from a databasein a form that can be used to generate that schema in another emptydatabase. This is to facilitate our disaster recovery processes wherewe need the objects only, not the data, and need to replicate this toour disaster recovery site over the WAN. There are plenty of toolsthat can handle a single database, but does anyone know of any toolsthat could handle multiple databases where many of the objects (storedprocs and views) are dependent on objects in other of the databases(tables). This is a home-grown ETL suite so making changes to the codeto remove these dependencies would take way too much effort. I amlooking for something that can either extract the schema for all 3databases and handle the object creation ordering to account for thedependencies (a simple method would be to extract by object type acrossall databases, e.g. tables for all dbs before views before procs), or abackup/restore tool that allows you to restore the objects only withoutdata. Worst case we could write something to generate the DDL or useSQL DMO, but ideally we would prefer to purchase a (relativelyinexpensive) tool to do it.Thanks,Simon
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.
I'm making a system to data acquisition (production) and need more (3) tabels with a heavy load of data.
3 tabels will contain approximately 20-50 milion rows. Every day I need to add 80000 rows to these tabels. Will I get problems with system-performance with such a configuration? Ofcourse the system will contain index in the database and 2 fast 1 Ghz intel CPU. I isn't possible for me to seperate or archive theese tabel, because of researc-functionality.
We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete. Even with this option, if we query a read-only node before the logs have been processed, we can read old data. I would like to know a strategy to ensure that a read-only query will definitely return up to date information. I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.
I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?
I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row. Thanks.
We are in the design phase of a project where it is estimated there will be 150 SQL Server databases - each one housing similar information on each of 150 client sites. The client needs a reporting tool to analyze and make comparisons across databases. We were thinking about a using a data warehouse. Does SQL Server have a native solution to this type of problem ? Or is there another, better way to approach this ?
Hi please could someone give me a explination of how SQLserver deals with multiple databases, We have multiple website that are all run from the same server all from SQLserver. Now we have a couple of databases which are very busy and have a lot of people constantly accessing them and then we have quite a few with much less traffic.
How would these databases effect each other? Would the busy database, mean that the many small databases will all be slowed dramtically?
Everything is okay at the moment but we are considering putting another fairly large database on the server and am worried about it having a sever effect on the smaller databases. Cheers Ed
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server. I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number. The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.: 1: Database A -DSNA_tblMaiin - Serialnumber 2: Database B -DSNB_tblMaiin - Serialnumber 3: Database C -DSNC_tblMaiin - Serialnumber
I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...
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.
hy all..How can I achieve the above ?Moreover can I retrieve data from multiple databases which are lying on different DBMSs ( like retrieving from database A which is on SQL and from database B which is on Oracle ) ?Rgds.
I just need to add multiple catalogs to the same linked server, in Sql Server 2000 or Sql Server 2005. Here i need only to add 3 databases in to a single linked server name.
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)
I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , looking for script to add this user at once to all these databases.
I need a script that will return the mdf & ldf for multiple databases.
I am currently running...
sp_helpdb 'TestDataname'
...and copying the size of the mdf and ldf into an excel spreadsheet.
How can I get the mdf AND ldf file size for all of the databases in an instance? I need the MDF and LDF seperated and I want the actual size of the file as it appears on the file system.
I have a setup/deployment question regarding SQL Server Encryption.
Internal database encrypts data in 3 different tables. This could execute on any one of 6 different servers. The tables with encrypted data are replicated to another database on different servers (3)
How should the keys/certificates be created so that the data in the replicated database can be decrypted?
In my test scenario so far, I have been unable to have the second database decrypt the data that was encrypted on the first database (currently on the same server).
-- Create Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd' GO
-- Create Certificate CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Data Encryption Certificate', EXPIRY_DATE = '10/31/2010'; GO
CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'My Symmetric Key', KEY_SOURCE = 'Unique phrase that will be used to secure the key' ENCRYPTION BY CERTIFICATE MyCertificate; GO
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200) Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */ IF @IS_READ_ONLY= 0 BEGIN Declare @SQL as varchar (200)
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:
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)
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...)
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.
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
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
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.
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.
