Hi,
Microsoft listed 32,767 as the max # of databases for a SQL 2000 instance but I am curious what is the comfortable # of databases we should have before running into issues such as performance degrading etc.
Thank you very much!
-thinh
This is the scenario. Is it possible to create views or something (like Oracle DB Links) in a database in Instance 1 which can show data from another database in 2nd Instance ?
I want to do this to create reports.
Hi,We are in the process of selecting a database for a data warehouse typeapplication. I want to get a feel for how big can a SQL Server databaseget. As per Microsoft, it can be multiple terabytes. Can you tell meWhat is the biggest size SQL server database you manage? (I understandbig is a relative term. I consider 500+ Gig as big).Do you see any major performance problems due to size of the database?(Given that the database is designed optimally).Really appreciate your help.Thanks,Joseph
View 3 Replies View RelatedI am trying to set up a Named Instance of SQL 2000 on the same machine that has a default instance of SQL 7.0. The setup always completes and I am able to register the Named Instance of the SQL Server with which it was installed on. However, when I try to connect the users to the database, with both windows and SQL authentication, I receive a SQL server not found error. I have tried an alias setup as well as physically specifying the port number in settup up an ODBC connection.
Has anyone ran into similar problems?
Also, has anyone been able to successfully complete the process as mentioned above?
The box I am trying to connect to is running two instances of SQL Server.
There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.
I can connect to the SQL Server 2000 instance no problem:<add key="ConnectionString" value="server=MYPCSQLSERVER;database=mydatabase;user id=****;password=****" />
However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:
<add key="ConnectionString" value="server=MYPC;database=mydatabase;user id=****;password=****" />
but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:
<add key="ConnectionString" value="server=MYPC,1434;database=mydatabase;user id=****;password=****" />
but this doesn't work either.
Am I mssing something here? Any help much appreciated
Thanks...
When uninstalling an instance, is there a parameter on the uninstall (setup.exe /qb REMOVE=ALL INSTANCENAME=XXXXXXXX), to remove all the databases associated with that instance?
Thanks
Jim
I have a problem in that a database I have created required splitting into three databases. The records were similar but reporting and maintenance differed enough that splitting the data seemed the best option. So now I have three databases that are needing to be updated from one file and I am not sure how to do that. See the illustration below. The Machine table is a one to many to the Job table, the Job table is a one to one with the Run Data.
The first idea was to query the first database if the record was not found go to the next. If no match was found in any table drop it in a reject bucket. Then what about perhaps a lookup table with starting and ending Job ranges. I would query it to find the correct database then do another query to update the record. But then I could have some type of joining table. Not sure how that would look across databases.
The tables I am updating or adding new records to has the same fields, unique key across all the databases. I could make one big table which gets rid of the query to find the correct database to update but I still would have to connect this back to the Job table which brings me back to a join table or index table.
Scenario: a stored procedure is executed in database ABC. The procedure includes a MERGE statement with objects in the ABC and DEF databases. A Windows login is executing the procedure. Contained users is not enabled anywhere on the instance. And the user is not orphaned in either database, i.e. the login is associated to the user correctly by-sid in each one.
Of course, the Windows login must be authenticated to access database ABC, and authorized to execute the procedure. That is happening. Most of the time, so is the MERGE statement. But every once in a while we're seeing an odd "Network error code 0x2746 occurred while establishing a connection" message. The procedure has a TRY/CATCH retry loop around the MERGE statement, so it enters that loop. Re-read: the procedure is still running, the network error wasn't about the client connection executing the procedure. So what was it about?
Does that MERGE statement, accessing database DEF, require SQL to authenticate the Windows login again, because it's a different database (even though it's on the same instance, and was already authenticated for ABC)? There shouldn't be any other network activity involved in that phase of the execution. Plus, when the process had been using a SQL Server-authenticated login, this never happened.
But before I go blaming our DCs or network... I need to know, are they in fact involved here, or not? I believe they are, but... never had to care enough before to really know. I'm trying to stave off the "let's switch it back to a SQL login" demand that's sure to come soon, if I can't find an appropriate resolution to this.
Is there a way of limiting the "Databases list" to just the databases which I have permission to?
My databases is in a hosted environment, and the list of databases on that server is HUGE.
Can someone explain what does this mean? This is from http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
<quote>However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.</quote>
Also I read somewhere that if one mirrored DB fails, all other mirrored DB should also be transferred to the mirror.
For example: Let's say Srv1 (principle) and Srv2 (mirror) and Srv3(witness) are SQL servers with only default instances. The SQL instance has 4 DBs (DB1, DB2, DB3, DB4) and all of them are mirrored to Srv2.
An ASP.NET application has four seperate connection strings like:
objConn1 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB1;Integrated Security=True;
objConn1.connect();
objConn2 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB2;Integrated Security=True;
objConn2.connect();
objConn3 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB3;Integrated Security=True;
objConn3.connect();
objConn4 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB4;Integrated Security=True;
objConn4.connect();
If DB2 failsover to Srv2 (mirror), why should all other DBs be failedover?
Thanks
I recently had to reinstall a new instance of SQLServer 2000, but was unable to use the previous server name. As a result, my Access2000 front end is not happy with it's linked tables. I can't seem to find anyplace within Access to universally change the address of the SQLServer used as the back-end for all linked tables.
When I do try to access the linked tables through Access, I get an error, and the option to change the server location. When I try to type-in the new SQLServer location, there is an attempt to reconnect to SQLServer, but a whole lot of errors are generated, and none of the data is transferred into the Access table.
I really don't want to have to re-do my Access front end, so it seems it would be easiest to somehow reinstall SQLServer to have the same server location it used to. Is there a good way to completely erase all traces of SQLServer so that I can have better luck reinstalling it to the same location it used to be in? Just using the uninstall program from SQLServer doesn't seem to be cutting it.
Thanks!
Find unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.
know if i can use the same endpoint for the databases on principal instance ?
Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DASAlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.
The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.I was expecting a clean instance with only the system databases..Is there something that can be set to keep each instance's databases private into itself?
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.
I can select The counter, but it does not let me select an instance database to monitor.
see image:
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
if i type LOCALHOST or 127.0.0.1 in the "Select counters from computer" text box i can see all databases under my sql 2005 instances, but not the ones under the 2008 instances. I have disabled all sql server 2005 services and can confirm none of the sql 2008 instance databases are showing up.
this is a huge problem, especially for when i start stress testing sql server 2008 in preperation for upgrading.
anyone any ideas on why i cannot see sql server 2008 instances in perfmon? I have tried from a remote pc also with the same results.
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
View 7 Replies View RelatedI want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set like this:
DatabaseName db_Owner
master sa
pubs valia
pubs eli
pubs maryam
AdentureWork eli
this script get all owners of the database that is in use:
Use DatabaseName
GO
SELECT members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc
Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????
In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name?
2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever?
3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database?
4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
Looking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
View 6 Replies View RelatedI just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Below query tells us if guest user is enabled or disabled in a particular database
SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions
WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';
Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
2. Audit logins off/on is also not good solution.
3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
Trying to transfer databases from MSDE to SQL. When I use SQLEnterprise Manager - DTS - import or export, it works for the firstdatabase, but then fails for the others. I select the to and fromdatabases using DTS - Import - 'MS OLE DB Provider for SQL Server',then select 'Copy objects and data between SQL Servers'. It failswith the generic error message: 'Copy objects failed', nothingfurther. I tried to just copy data, and it doesn't work totally. Ontwo databases, it said that it copied everything, but when I go inunder Enterprise manager, some of the tables are incomplete, i.e. nodata, table shows up in the list, but if you try to look at it, itsays that it is missing or empty. Also, one table was not evencopied. In the other two cases, I get errors, does not copy alltables. I get errors, like insert fails, but the column it referencesfor the table does not even exist in that table. Any suggestionswould be appreciated.
View 2 Replies View RelatedHi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
I started with a SQL 7.0 installation and installed an instance of SQL 2000. The default instance is SQL 7.0 as expected and both run fine. However, now I'd like to upgrade the SQL 7.0 instance to SQL 2000, but the setup procedure on the install CD only allows you to upgrade/modify the SQL 2000 instance. It seems as though once you've chosen to install SQL 2000 as an instance, you do not have the opportunity again to upgrade the SQL 7.0 instance in place. I tried to remove the SQL 2000 instance and reinstall 2000, but the procedure does not give you the opportunity to upgrade the SQL 7.0 instance -- only to modify the programs installed. Any suggestions?
(I could use the Copy Database Wizard to move all databases along with associated stored procedures, logins, etc., but then I'd have to mess with DTS packages, etc.)
I cannot register, from my W98 workstation, an instance of SQL 2000. I just installed SQL2000 (evaluation edition) on my server. Previous to my install I had MSDE installed on the server and successfully registered it from my W98 workstation. When I installed the SQL2000 it recognized there was already a version of SQL Server (MSDE) on the server and said that was the default. Hense, I installed the SQL 2000 as an instance using the format servernameinstance. I can see both of them on the server EM with no problem--I just can't register the SQL 2000 instance from my workstation. When asked for the server name I use the format servernameinstance as it appears in the EM on the server. However, I get the error that the server does not exist or access is denied.? What could I be overlooking? Any help would be much appreciated.
Thanks,
Eugene Jenkins
I installed SQL 2000 server and I didn't install an instance of it (at least I don't think so). How would I install an instance now?
View 2 Replies View RelatedHi All,
Hopefully someone can help me. I am trying to upgrade SQL 2000 on a Win 2000 SP4 server to SQL SP4. The server has 2 instances. The one instance upgrades fine with no issues, however when I try to upgrade the 2nd instance I get "<instancename>" is not a SQL 2000 instance. I am a bit stumped so any help in this regard will be much appreciated
Thanks in advance
Iain
Hi everybody,
I have an instance VSdotNet for SQL Server 2000 on my machine. Now I want to create another instance called NetSDK, so that the NetSDK and VSdotNet exist side by side.
Please let me know how I can do this.
Thanks
Sunil
We have a client running a 3rd party app on sql 2000 Enterprise on Windows 2003 Advanced attached to a new SAN. They are looking at adding another SQL server and clustering for failover. Anyone done this before. Do you have to reninstall sql server on the original box to do this. This a a heavily used app, so downtime is always an issue.
View 3 Replies View RelatedWe have a two node activepassive SQL Server 2000 cluster SP2 running on Window 2000 advanced server SP2. Everything is working fine including failover. Today we migrated over our 7.0 databases to the SQL cluster and they attached fine and upgraded fine but we would like to shutdown the 7.0 server and bring up the clustered SQL Server under the old name. We will be running 3 instances of SQL Server 2000 on the Cluster so at least 2 of these will be named instances like this: "Virtual serverinstance". Is it possible to create an alias for this SQL Server so that we could access the server under the original name of the server???
Thanks!!!
Bruce