Does anyone know of any performance considerations when dealing with data that resides in two databases on the same server?
We use one database as a staging area for bulk loaded data. Once we are happy with the data we insert or update the equivalent tables in the "real" database but performance is terrible when the tables are joined across the databases. The execution plan indicates that the clustered key is used on the real database and a covering index on the staging database.
Are there any rules of thumb or gotchas that I should remind myself of when joining between databases?
TIA
=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions: 1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server? 2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected problems when running both 2000 and 2005 SQL Server at the same database server? 3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
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.
While I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --
Which tool can I use for structure editing of SQL Server Compact 3.5 databases? I'm installed SQL Server Compact 3.5. I have SQL Server Management Studio Express which was installed with SQL Server 2005 Express. Unfortunately this SSMS can create and open only 3.1 databases.
This question is about SQL Server 2005: I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.
Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.
I have another question : 1) Restoring SQL Server Databases after uninstalling and reinstalling SQL Server.
Two days ago there was some problem with Norton Antivirus, so Windows 98 restored itself to a previous state. The SQL Server name was not available when I tried to start again, so I had to uninstall and reinstall SQL Server. I started a new Database just a week before this happened (the change from Access to SQL Server as I stated in the other thread http://www.dbforums.com/showthread.php?threadid=987880) and after reinstallation I could not access the Database any more - even while it is in the Data directory. I did not have any backup to restore from - I just started creating tables, so there was only the Log file created and the MDF database file. I tried to import it for a while to the new server, but as the database is not shown in the new server, it was unable to import like I hoped. So how do you restore it back to SQL Server when you have no backup and only the (undamaged) MDF database file if you only have one server? I had to recreate the Database from scratch again - I don't want it to happen again.
I don't have any idea about upgrading databases.I am been given this task. It would be great if you let me know what are all the things to be considered upgrading the sql server 2000 databases to sql server 2005.
We have a server hosting about 150/200 webpages. The webpages content are provided by Access databases. Now we are planning to change to other platform, but we are not sure about the most suitable solution to our business in cost/performance terms.
We work in .NET platform, so in a first sight the most suitable database to work is SQL Server. The licenses for this platform costs nearly 6000 $ per CPU (our server is built with 4), so it's necessary to evaluate if really other cheaper or even free databases (as Firebird, MySQL, Oracle -don't know how cost this platform-) could be suitable for our server.
Which advantages have SQL Server 2005 over the rest of databases?. You think the budget necessary for Microsoft's database could be profitable at long range?
Hi I have been a bit of an idiot. I was using MSDE. I de-installed it and installed SQL Server Developer Ed 2000. I then copied my MSDE databases to the SQL Server data folder, but the SQL SErver cannot see them. Can anybody tell me how to sort this out please.
Hi,We are about to move to SQL Server 2000, as we already have a licencedcopy of it. However, as i understand it you licence by the number ofusers (where else can i find out what the options are with it). The bigquestion is: We want it to run two seperate databases, we dont want theadmin of one to be able to have admin of the other, or be able to usethe other database. Is this possible, if so where might I be able tofind out more about it.Thanks in advance for any helpDavid
I have 2 web applications running on 2 W2K servers. Each server has a local SQL server(2000). We are about to upgrade the servers to Windows 2003. The web applications are running classic ASP and VB COM+ objects. We are going to take the opportunity to seperate SQL from the web server. What will end up with is 2 web servers running the applications and a single database server with 2 databases servering up the data. We will upgrade the SQL2K to SQL2K5.
My question is how will the SQL performance be affected? What's the best place to locate the COM+ objects? What kind of pitfalls do I need to be aware of?
Hi everyone, First of all I'd like to say I'm new to the forums, and in fact new to ASP.net. I've had a couple of applications running in VB.net for the last year or so that I now am looking to move on and into ASP.net (main reason is to do with implementation to remote sites in foreign countries). I'm currently following through the "Working with data and ASP.net 2.0" walkthrough on this site, which I must say has been a great help. I'll start with the background stuff first I have my main database on an SQL server. I also have 2 other databases on there - Customer and Employee. The reason for these are that they are shared amongst my VB applications and I kept them as seperate databases so that one database is all that needs to be administered and maintained, and these changes are then seen on both apps (takes away redundant data as well). In my main database, main table, is a field called CustomerID. This value relates to the CustomerID in the Customer table. In my VB app all I do is have the user select a customer name, and then the customer ID value is copied to the CustomerID field of my main table when inserting / updating a row. This is simply accomplished with 2 SQL connections and 2 dataAdapters. I also use 2 dataSets - one for Customer, one for WorkRequests (the main database)... I use ComponentOne stuff so that my grid will show the CustomerName instead of the CustomerID etc, and the same applies when I use my Employee database - I run many dataAdapters to this for things such as Account Manager, Technologist, Creator, UpdatedBy etc etc. Now, I'm new to VS 2005 - my apps were created in VS 2003 and .net 1.1. Can I easily re-create my apps in ASP.net? My primary goal at the moment is to make sure I can load a row, and display the "lookup" values instead of the "integer" value. As my tables are in seperate databases, I would like to know the best way of accomplishing this, if it is of course possible. Help and advice much appriciated. Kind Regards, Luke
Hi. I would like to enumerate all the databases in a given Sql Server instance. I've searched and found no information. Would somebody post a piece of VB code to do that? Thanks in advance Rafael
Hi, I have several databases on one MSQL Server which I want to transfer to new server. So, basically it is tranferring all contents of sql server databases on one server to another one. I know I can use import Wizard, but it will take long time, is there a more better way to do this.
Hi, I have a problem that I have never considered before. I am developing with C#.Net, Visual Studio.Net ans SQL Server on Windows Server 2003. Recently we have been upgrading our servers and have installed 2 servers (Live and Dev). The live server is Win Server 2003 Standard while the Dev server is 2003 enterprise. Both servers are running their own versions of SQL Servers (both 2002). What I need to know, is there a way to copy or 'sync' the dev database to the live without over-writing the live data. I need to be able to copy the dev db structure (the tables anf fields) but not over-write the live data with the dev data (the live data changes rapidly and none can be lost). These 'syncs' would happen 2-3 times a month so the 2 db's, at the time of sync, will look very different so a simple copy of the live data to the dev db is not possible. Does SQL Server have a wizard or utility? I have not been able to find one. Thanks, Justin
Is it possible to merge/sync 2 "part time connected" Sql Server databases? I basically want to keep one of my in office databases synced with a database on my hosting provider. (Said hosting provider does not offer SQL Server's built in Merge Replication) Can anyone recommend a 3rd party application that does this if one exists?
I have been trying to do this using both DTS and BACKUP/RESTORE. With the former my logins and users get transferred but no one can login. It's as if they are not linked to each other.
With the latter there are no logins to link to the db users. In Sybase we used to bcp the master..syslogins table. Does this work with MSSQL 7.0? Any other suggestions? TIA.
I'd like to hear the "best practices" for moving databases from one server to another with minimal impact (minimum downtime) to users. The database is about 1GB, but growing at an average rate of 5MB/day.
Only 2 databases on that source server are to be moved. There are other databases on that server that shouldn't be affected as much as possible.
Any good suggestions?
I'm sorry, but I'm a complete newbie to SQL Server although I'm very familiar with Exchange/NT stuff. Got thrown this responsibility lately :(
We are going to be moving over about 29 databases from one server to another (space issues). The new database will then be renamed to the old server name. The database sizes range from 100mb to 29gig. I was planning on doing a dump/restore which will take over 3 hours to dump and 12-15 hours to restore. Our NTAdmin suggested shutting down the SQL Servers and just copying over the data/log files to the new box and then bringing up the new server. Had anyone ever tried this? What are the effects on files over 22gig? Should I also copy over the model/msdb/master database files? What other potential problems should I be aware of? My fear is that I start copying them over, run into a problem half way through and then have to revert back to the dump/restore method.
Any help would be appreciated, we are scheduled to do the move this weekend.
I have two databases on my Server - database1 and database2. Database1 has a view on tables on database2 : create view myview as select * from database2.dbo.tablename (because all tables in database2 are owned by dbo) and myview on database1 is owned by dbo. when a user on database1 - lets say user1 tries to select rows from myview, we get an error saying that user1 is not a valid user on database2. it only allows user1 to select rows from myview if i make user1 a user on database2 as well, which is not what i want. Can someone please point me in the right direction. i would like to be able to select rows from a view which is based on tables in another database. thanks simran
how can I easily move my whole SQL Server 7 installation to another different machine as the current one is going down for maintenance during one week? Thanks.
I was hoping to get some input on the best approach for the following situation: I have been tasked with moving several SQL server databases from one SQL server to another to free up the hardware on the first one for another purpose. The SQL server is version 6.5 and has service pack 5 on it.
I have installed SQL server on the server where the data is going to end up with the same code page and sort order as the current server.
For my next step I was going to create devices for the databases on the new server, then add the databases to the devices. I was going to use the "Create database for Load" statement. Then I was going to load a transaction dump from each database on the old server into the new database on the new server. Is there anything else I will need to do? Will I have to do anything with the master database or is it sufficient to do what I have stated above?
Can anyone direct me to a good technet article or white paper on this process? I did find a short page in SQL books on line which helped a bit.
My production server has 8 databases connected to SAN where all the data resides. This also has the system databases. There are two internal drives where I store the log files
We will be buying additional internal drives for the server and want to move all data to these drives and free up SAN for other use.
Do I need to start building this server from scratch or is there an easy way to do this. I can use sp_attach_db and sp_detach_db too but I prefer backup and restore Is this the best option: backup all the databases uninstall sql server (since system databases are on SAN) Reinstall sql server Restore eveything on these new drives
When trying to document the max_size & used_size, I need to link the SYSDATABASES table with the SYSINDEXES. These two tables are linked as follows SYSDATABASES ->(DBID)-> SYSUSAGES ->(bitmap between segmap and segment)-> SYSSEGMENTS ->(segment)-> SYSINDEXES; My question! what exactly is the method used to join these 2 tables (Bitmap)????
We have an NT 4.0 member server in a production domain which has SQL 6.5 loaded with a number of databases. There is a requirement to rename the NT server to another name. Will we have to reinstall SQL and reload any databases after the server is renamed? If not - are there any other considerations/registry changes necessary to ensure SQL will run correctly?
