Multiple Instances Ready For Production Prime-time?
Jul 20, 2005
Hello,
Is the multiple instances capability of SQL2K mature enough for a
Production system?
We're looking at upgrading our hardware but the proposed solution
calls for consolidating two currently separate SQL Server's onto one
machine with two CPU's.
Of the current two servers, one is for OLTP (~800Mb) and the other for
reporting (some ad-hoc, some canned queries, 2-3Gb). We purge the OLTP
db nightly and archive the purged data in the reporting server where
it stays for a couple of months before getting purged from there.
Maybe I'm being overly cautious here but the OLTP system is especially
time-sensitive. If anything causes it to slow down significantly we
lose $$$.
I must release a new Db on 4/1 into an isolated small network (pier workgroup) and my choices are MSDE or Express. I'm new to both (MSDE yesterday, Express today). I am familiar with SQL Server and prefer using the tools with Express, but is it stable enough to use in this type of production environment?
I will be hosting my website in the next few months and doing more testing and I was having trouble finding a place to host the site that allows enough space for my sqlserver2005 database. I am estimating that the database will be around 40-50GB. Most hosting companies only give about 500MB for the database. Does anyone know of a company that provides more space? Also, is SQL Express production ready, or is it just for developing applications? If it is production ready, is there any "major" performace impact in using it over the full version. The reason I am asking is because hosting companies only give 500MB for a database, but alot more space for the web page. If I can put the SQLExpress file in the App_Data folder, then I will have more space. Does this even sound like it will work? Opinions are appreciated!
Hello, Is SQL Server 2005 mirroring production ready yet. We have two servers and plan to set up mirroring between them. We have the Standard Editon installed on them. Is Standard Edition sufficient or does it need Enterprise Edition?
Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?
We will soon have 200 + production instances. In an effort to be morepro active, I am looking for a way to monitor jobs, both failed andlong running, Disk space, and other such things like CPU load etc.I would appreicate any suggestions you might have on accomplishingthis. For instance, is there anything in reporting services that mighthelp accomplish this ? Most of the instances are 2k, but we also havesome 2k5.Best RegardsSGB
I think this is a question for the specialists among us.
Can I use one general reporting server (installed on instance MAINREPORTING) for multiple customers who all have their own sql instance (CUST1, CUST2, CUST3, ..) I would use UserAuthenciation on the reportserver url to display the specific reports customers can use.
Is this possible, and what do i have to take care off concerning installation and/or configuration (especially on the reporting side) ?
create function dbo.F_NTH_PRIME(@n bigint) returns bigint as begin declare @MaxNumber bigint, @i bigint set @MaxNumber = case when @n <=6 then 20 else @n * log(@n) + @n * log(log(@n)) end
declare @t table (n bigint identity(1, 1), i bigint) insert @t select i from dbo.F_TABLE_PRIME(@MaxNumber) select @i = i from @t where n = @n
return @i end
--calculation (ran in 2 seconds for me) select n, dbo.F_NTH_PRIME(n) as 'nth prime' from ( select 1 n union select 2 union select 3 union select 10 union select 25 union select 100 union select 1000 union select 9593 union select 10000) a
/*results n nth prime ----------- -------------------- 1 2 2 3 3 5 10 29 25 97 100 541 1000 7919 9593 100003 10000 104729 */For dbo.F_TABLE_PRIME, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69646
Ryan Randall www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part.
Creates a table of prime numbers, starting at 2 up to a maximum number.
Makes use of dbo.F_TABLE_NUMBER_RANGE, which is found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
create function dbo.F_TABLE_PRIME(@MaxNumber bigint) returns @t table (i bigint primary key) as begin insert @t select NUMBER from dbo.F_TABLE_NUMBER_RANGE(2, @MaxNumber)
declare @i bigint set @i = 1 while 1 = 1 begin select @i = min(i) from @t where i > @i if @i is null or @i * @i > @MaxNumber break delete @t where i > @i and i % @i = 0 end return end go
select * from dbo.F_TABLE_PRIME(100000) order by iCan we improve on the speed?
Ryan Randall www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part.
i have sql server 2005 express running. during install, i had issues with a system dll that i found answers online and resolved. i am still very green, but i know much more now than i did at the time. i believe that as a result of the partial then failed installs i have multiple instances either fully or partially installed. when i am in the sql server mgmt express environment, it lists two sql engines. i only ever use one, and would like to safely remove the second without damaging or corrupting anything done with the other instance. my reasons for wanting to do this are twofold. first, i would like to free up the drive space taken up by the second instance. (i am a stickler for clean harddrive mgmt and maintenance). second, now that i have used the software and am somewhat more familiar with it, i would consider installing a second, intentional instance so that i may attempt to better customize some of the install-time settings and configurations. can anybody give me any pointers/ guide me in the direction of information on how to SAFELY go about this? thank you in advance for any help.
I created a second SQL2000 instance on a Windows 2000 server. The strange thing is that I can create an ODBC connection to the default instance but cannot for the new named instance. If I try from a client that has SQL installed on it, I can see both instances. Any idea why this is happening?
We're trying to decide if we should use a single large instance of SQL Server or multiple instances on the same server. This is for a dev environment.Have to limit max RAM per instance so no instance grabs it all.There will be I/O contention and CPU contention between instances since the different instances won't coordinate with each other.It limits the available RAM for In-Memory tables..It sets lower limits to the amount of RAM available for any given query, causing artificial "contention" even when there's nothing else running at the time.
According to the Task Manager, on my SBS 2003 Server, I'm running multiple instances of SQL. How do I determine which instance(s) are required to maintain server stability, and which are not. My Server resource use continues to be a perennial problem. Please advise.
Is it possible to have multiple instances attach the same database?
I have a scenario where I want to have on one node, a clustered instance and a non clustered instance and both instances attach the same singular DB on a filer.
If I want to install 2 instances of SQL2000, will it install the SQL2K executable files (inn folder) onto 2 different folders, or all instances in one folder but the databases are in different folders.
Please Help me on this. I installed the two instances SQL on one box. ONe is default instance called ACSQL1(actually machine name) and the other one is called ACSQL1/DEVELOPMENT. THrought the my client site of SQL 2000 I can access both instances. When I try to get connection from VB. I can access the default instance but I can not access the second instance. Any ideas about this??? Qun Lu
I am running SQL 7.0 as the default instance and SQL 2K as a named instance. Each is using a different port....7.0 is using port 1433 and 2K is using 1024. Everything appears to be up and running fine on the server. However, I am not able to access the 2K instance from a client. I have tried to use both TCP IP and named pipes setting them up using the server network utility and the client network utility. No luck anywhere. I continue to get connection errors...check network documentation...
Currently, I am running SQL Server 2000 with the default instance. I know SQL2K can run up to 16 instances simultaneously. I would like to add another instance but am unable to find documentation on how to add it. Books online tells me "about" multiple instances, but not how to set it up. I've obviously missed something somewhere. Can someone please direct me to a document that will help explain "how" to add another instance?
We have a DTS package that is submitted every minute by a SQL Agent job (2000). We see instances where before the first job is completed, the second one starts running.
Has anyone encountered this type of behavior before?
I have a need to add a second Sql instance to an existing cluster. However I read in this article http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
that this is not recommended. Does anyone know why this is or what problems I may encounter?
Its is running Sql 2005 on a 64 bit windows Server 2003.
Is there any limit on SQL server Multiple Instances? if we require to run 60+ Instances on single machine what will be the minimum hardware requirements.
Has anyone run SQL 2005 Standard Edition, 2 or more instances on one server... where you got the extended memory up to 2.8 GB on EACH instance of SQL Server? I've done the 2.8GB game on boxes with ONE instance, but don't think I tried it on a multi-instance box, to get the 2.8GB RAM each. Thanks, Bruce
I have a need to add a second Sql instance to an existing cluster. However I read in this article http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
that this is not recommended. Does anyone know why this is or what problems I may encounter?
Its is running Sql 2005 on a 64 bit windows Server 2003.
Himy first post in the data access forums, I really hope, someone can enlighten me here...I have a windows server 2003 running with 15 MSDE instances. No I would like to upgrade to SQL Server 2000 without crashing the existing instances... My perfect scenario woudl be- install sql server as new instance- move existing databases via detach / attach to the new instanceWill that work?
I need to put sql server 2000 enterprise and sql server 7 on the same box. For multiple instancing is it better to run with Win 2k Pro Server or Advanced Server. Please Elaborate.
Hi everybody. One of department want to place db application from one server somewhere else on corparation network for 3 month period. Problem is they want to keep it with max security. Company does not want set up another server , so we want to create another Instance and place database on it. 1. Is safe to keep 2 instances on same server ? 2. How remove access to this instance from members of local and domain admin role(If we deny acces to BuildInAdministrators how it is going to affect security?) Thank you
1.) Is it possible to install Analysis Services on multiple SQL Server 2000 instances? If it is, then can you instruct me as to how to perform the install. If it is not possible, then does one restrict access to cube data via the Database Role Manager?
ie. The host box is - DOILAB30 with Analysis Services & SP2 Instance - DOILAB30/DOILAB30_FIN (Can AS be installed here?)
Do I severely restrict the membership of OLAP Admins?
2). Re. AS maintenance. - How do I backup and restore OLAP dbs/cubes etc. Following a restore, what issues, such as cube users/permissions should I be aware of? (if any).
I am using sql2012 with partitiondb custom installation over 6 dbs on 4 servers, ~200GB per db. I am looking for the perfect graphic tool (similar to Perfmon+Activity Monitor) where I can monitor a wide-scope SQL environment. I am interested in data / log / table / index growth, buffer cache hit ratio, average wait time, physical/logical reads/writes and such. I am interested in real-time / time-range metrics. I know I can issue immediate queries against dm_os_performance_counters / dm_os_buffer_descriptors / dm_exec_query_stats and get some of the relevant data, but is this the only way?Also, for your opinnion, what are the most crucial metrics to monitor when dealing with multiple dbs?
I have installed two mssql 2000 MSDE sp4 instances on WinXP SP2. I can connect both instances from local machine. But when I try from remote PC, I can only connect to one with default instance name and can't connect to another one ComputerNameInstanceName.
I set the windows firewall off in WinXPSP2. But still can't connect.
I also try with mssql2000 MSDE sp3 instances. It is same ask sp4. Please help!!!!!!
