We are currently looking at consolidating 10 servers into one cluster server.
Some servers may be busier than others. Is there any reason to split them up and give the busy databases specific CPUs or is it always better to have them on one instance?
Hi everyone,
Primary platform is 2005 under 64-bit.
I'm trying to figure out what does 'Processor Affinity' and 'I/O Affinity' means when you're viewing server properties. We've got eight processors.
Thanks for your comments,
On NumaNode1 and NumaNode3. I can see all of the CPU's there but the checkboxes are disabled.
What could be disabling these?
I am currently having publisher(database A), subscriber (database B) and distributor on the same instance for a test environment that is using a uni directional transactional replication. Now I need to setup another unidirectional transactional replication in the same test instance but for a different database. Publisher database is D and subcsirber database is E.
View 4 Replies View RelatedHaving a SQL Server 2012 Enterprise (x64) on a Windows 2012 R2. We need to know, a reliable way, the number of processor sql server is using at a give time. We already know how many total processor are available to sql by getting info from sys.dm_os_sys_info.
For instance, a server has 40 processors, we want to know how many of those are being used at a given time. Since the load on the server may not be that high, we would like to know how many processors we can eliminate and the load will still be unaffected.
After watching the server performance for a while, we are predicting we may only need 16. But we would like to get some statistics before we reduce it to this number.
I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
Hi,
If I have two applications which have several databases for each application, do you create one instance for two applications or you create one instance for each application? Which way is better on pricing, performance, maintanence etc?
Thanks for your input
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
View 12 Replies View RelatedHi everybody,
When we execute some stored procedure with the
multiprocessing enable, the stored procedure isn't
working correctly. The stored procedure is starting
but won't finish it's execution.
We start the same stored procedure with just a
single processor enable and it's working.
I check to code into the stored procedure and everything
seems ok.
We run a SQL Server 7.0 with Service Pack 2 on a WinNT 4 Service Pack
5. Someone have an idea for this problem or a point to
check.
Mathieu Noël
CIQ
Hi,
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) ?
Greetings
Vinnie
Do you need to enable Always on Availabilty on all the instances or only the instance you need to ?. We have 4 instances on a 5 node cluster and we only need one instance for the Always on to be enabled. Do we need to enable on all 4 instances ?
View 4 Replies View RelatedWill installing another instance restart other instances?Server is Windows Server 2012 R2, SQL Server is 2012.
View 3 Replies View RelatedThis is probably easy for some who have done it, but I have an installed Instance of SQL Server 2008 Express or SQL Server 2008 R2 Express I'll call Instance CCC and need to remove it. Â I have two other Express instances I'll call Instance AAA and Instance BBB. Â I want to uninstall Instance CCC without affecting the other two instances or the software that uses them. Â Instance CCC should have been installed as a SQL Server 2012 Express instance, so I need to remove the 2008 Instance of CCC and reinstall it as 2012. Â In all cases, I'm talking about SQL Server Express. Â Full SQL Server is not installed. Â
The Instances are all installed on a Windows Server 2008 R2 Standard server. Â Â
I have read that I can do this through Control Panel  >  Programs and Features, but that I can corrupt or mess up the other instances (AAA and BBB) if I don't do it right.  I'm uneasy about this, not having done it before, and do not want to affect the users of the AAA and BBB instances! Â
I'm experiencing some problem with the affinity mask and affinity I/O mask configuration.
We are running SQL Server Enterprise Edition 2005 64bit on Windows 2003 Server 64bit with 4 dual core CPU. The actuelly configuration is:
Affinity Mask Affinity I/O Mask
CPU0 0 0
CPU1 0 0
CPU2 1 1
CPU3 1 1
CPU4 1 1
CPU5 1 1
CPU6 1 1
CPU7 1 1
We did it so, because we are running LiteSpeed Tool for the backup. LiteSpeed is configured to use CPU0 and CPU1.
Now, we know that the settings are wrong. If Affinity mask is 1 affinity I/O mask should be 0 and vice-versa.
My question is: how many CPU should I enable for affinity mask and how many for affinity I/O mask. Which are the correct criterias.
Thx in advence.
Laurent
Hello all,
We've had a problem for a few months now that has completely stumped us. We are running a heavily cursored massive data manipulation process on a 32 bit SQL Server instance running on a virtual machine, running ontop of VMWare, with the following specs
Processors: 2x2674MHz processors
Memory: 4GB
RAID 10 disk config
When we run our process on this machine, in total it runs in 30 hours.
When this process is run on another 32 bit server with the following specs
Processors: 8x3658MHx processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 25% slower
But here is the real kicker. When this process is run on a 64 bit server with the following specs
Processors: 8x3658MHz processors
Memory: 8 GB
SAN w/ RAID 5 disk config
It runs 75% slower.
This process consists solely of stored procedures written in TSQL. The weird thing is that on our smaller server, the CPUs' % utilization are evenly balanced (at 20-30%) when this large data manipulation process is running. However on the bigger servers, SQL Server latches onto a single processor and doesn't load balance across other processors. Such that what we're seeing is that only one processor out of the eight will be utilized and it will be throttled at 90% while the other 7 are at zero.
The default configuration settings in all three places.
Has anyone ever seen any behavior like this, where only one processor gets used by SQL Server during processing? Granted our processes are single threaded b/c they are using cursors but, it seems that the single thread shouldn't be restricted to one processor.
Any thoughts?
Is it possible to install 2 SSAS instance (one default and one named) and have them access a single sql server instance (default) all on the same server?
View 3 Replies View RelatedI work for a mid size company with the usual collection of enterprise applications. We are trying to consolidate our SQL environment consisting of around 100 databases none of which are over 100GB with the average database size around 5GB. Recently a large server was purchased as the "Big Daddy" SQL box. Obviously not all the databases will be moved to this one box but within reason a number can.
My question: What is the best way to configure the box?
1 OS & 1 Default Instance - How do we isolate loops memory leaks etc...
1 OS & Multiple instances, ie:Dedicated instance for each application. - How do you allocate CPU & memory, allow for optimum performance without putting others at risk from a rouge process?
Virtualized OS for each application with a default instance - Too much overhead impacts performance?
Future plans include offsite replication, perhaps mirroring with failover.
I'd appreciate anyone else's thoughts ideas? How have you tackled this problem in your environment?
Thanks
Scott
If I install a new named instance of SQL Server 2008 R2 on a machine that already has 3 named instances that are upgraded to service pack 3, will the install of the new instance overwrite any of the current SP3 files? Will I have to re-apply SP 3 to the existing instances?
View 4 Replies View RelatedHello,
I installed SQL 2005 Standard on the same server (2K3) with SQL 2000. My named instance I installed is coming up with "Version 8.00.2039" in the Server Management Studio and will not let me restore a database back-up I made on a 2005 development workstation (I'm assuming it's because it's trying to use the 2000 engine instead of the 2005). Is there a way I can force this instance to use the 2005 engine rather than the 2000?
Thanks,
Steve
I have three instances running on my sql 2008 r2 enterprise.I have taken full backup of one database from default instance.I login both the instances thru sa account.Now when I try to restore that backup on another instance it gives me the following error....
Â
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'WRCP2F1CWS315TEJA'. Â (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATAfcms.mdf'. (Microsoft.SqlServer.Smo)
what can be done to restore bakup in another instance ...
why we will go to install named instance as we having default instance already installed. Is there any advantage of named instance.
View 6 Replies View RelatedI have installed a default installation of a named instance of a 2008 R2 server and I want to restore a server from the main system and application files. I have a bak file for the latest master and model database and the four application databases and transaction log files. If I restore the master database in single - user mode I will get an error when I start-up the SQL service as the other databases are not attached to the SQL instance.
Is it restore databases and then master/model? best way to restore a SQL server environment?
Is there any query/script to find out if SQL Server 2012 instance is set to hidden or not instead of going to SQL Server configuring manager?
View 2 Replies View RelatedI recently installed SQL Server and Visual Studio. When I went into SQL Server, I could only connect to a SQL EXPRESS Database engine. I need to access the full Database. I tried disconnecting it, uninstalling/reinstalling. Now I cant connect to the SQL EXPRESS DB Object. There are no SQL Server services running.
Please help.
Thanks
I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
View 3 Replies View Relatedi 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?
Thanks,
Ken Nicholson
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.
View 2 Replies View RelatedAccording 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.
View 3 Replies View RelatedSQL Server 2005 Enterprise
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.
Is this possible?
Can SQL Server be setup such that one instance on a server is using SSL and another instance is not? Or is SSL a server-wide all-or-nothing issue?
Dave
Does SQL Server express support running multiple instances of database ?
View 1 Replies View RelatedI've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamics FINANCE) using SQL authentication from local SSMS, I get below error message.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)I assigned a static port number to the named instance [dynamicsFINANCE]Â 1450. I also setup the firewall rule to allow access to Port 1450.
View 4 Replies View Related