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.

View 3 Replies


ADVERTISEMENT

Schema Extraction For Multiple Interdependent Databases

Jul 23, 2005

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

View 3 Replies View Related

Integration Services :: Schema Separation - Accessing Multiple Databases

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

Can SQL-server Manage Databases With 50 Milion Row/tabel

Sep 24, 2001

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.

Michael

View 1 Replies View Related

Recovery :: How To Manage Data Latency On Read-only AlwaysOn Databases

Aug 27, 2015

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.

View 2 Replies View Related

Manage SQL Servers On Multiple Domains

Aug 24, 2000

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?

Thanks!

Lia

View 1 Replies View Related

How To Manage Concurrency Between Multiple, Disconnected Clients

Sep 25, 2007

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.   

View 1 Replies View Related

DB Engine :: Multiple Partition Schema On Single Table In Server

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

Reporting Across Multiple SQL Server Databases

Jan 29, 2001

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 ?

View 1 Replies View Related

How Does SQL Server Deal With Multiple Databases (was Please Help)

Nov 9, 2004

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

View 1 Replies View Related

Queries From Multiple Databases On The Same Server

Feb 13, 2008

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 believe that this is a dot notation question.

Thanks in advance,

Gene

View 8 Replies View Related

Managing Multiple SQL Server Databases

Oct 9, 2006

Hi,

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...

View 6 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

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

Retrieving Data From Multiple Databases In SQL Server

Jul 21, 2005

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.

View 2 Replies View Related

Multiple Databases In Single Linked Server ?

Apr 25, 2008

HI Guys,

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.


Thanks In Advance,
Krishna

View 1 Replies View Related

Merge Replication Between Same Schema Databases But Different Data

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

DB Engine :: Get Email Alerts When Schema Changes In All Databases

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

SQL 2012 :: Script To Add Server Login To Multiple Databases?

Feb 9, 2015

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.

View 9 Replies View Related

SQL Server 2012 :: MDF And LDF Size For Spreadsheet - Multiple Databases

Aug 28, 2015

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.

View 7 Replies View Related

Setting Up SQL Server Encryption Between Multiple Servers And Databases

Feb 23, 2007

Greetings.

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

View 3 Replies View Related

SQL 2012 :: Schema Changes On High Availability Group Databases

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

Table Schema/Data Type Boolean In SQL Databases

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

SQL Server Admin 2014 :: Separate Transaction Log Files For Multiple Databases?

May 15, 2015

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?).

View 3 Replies View Related

SQL Server Admin 2014 :: Delete Orphans Users From Multiple Databases

Oct 21, 2015

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)

[Code] .....

View 4 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

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

Creating A Database From Multiple Databases Accross Multiple Servers

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

Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)

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

SQL Tools :: How To Restore Multiple Databases From Multiple BAK Files At Once

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

JOIN Multiple Tables From Multiple Databases

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

Altering Multiple Objects Schema

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

Transfering Multiple Objects To Another Schema

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

Specifying Schema/Owner Via Configuration File, For Multiple Packages

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

Schema-level Security For Multiple Users In One Database

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







Copyrights 2005-15 www.BigResource.com, All rights reserved