Please excuse the following question from a newbie to SQL.
I have been asked by our DBA to set-up a new SAN tray to cope with our company data bases.
We have an MSA1000 fibre channel that the Database Servers connect to via a Fibre link.
We have a second SAN tray attached that currently has no disks attached to it, this is the one that we are going to use for the new Database set-up.
We will be purchasing 300 GB 15k disks but the question i have is how many disks and what configuration will i need for the following scenario.
100 GB PRIMARY DATA
50 GB DATABASE LOG
100 GB SECONDARY DATA
300 GB ARCHIVE DATA
50 GB TEMP DATABASE
I have been reading as much as i can about SQL and Disk I/O and it appears on the face of it that Raid 10 would be the fastest (and most fault tolerant) for the this set-up.
If anybody has any ideas they would be gratefully received.
We are going to implement either a replicated database or use log shipping to another database to support our reporting needs. Basically we are moving reporting over data off our transactional database.
With this in mind, I have several questions: Are there any special configurations in terms of disks for a database in this situation?What are the recommended raid types? Also, we currently store our data in one filegroup and indexes in another so obviously I'll want those on 2 separate physical disks other than the C:. What about the log files? Do I care where they go (on C: or another physical drive separate from C:, data filegroup drive and index filegroup drive)?
I have a question concerning where to put certain database files for the followinig RAID configurations. The server has 2 RAID configs: 2 hds in a RAID 1 and 4 hds in a RAID 10. The server will host 4 database instances: A replicated db, a Reporting Services db (which technically constitutes 2 db instances) and an application db. In order to get the best performance, should I put the OS, SQL binary and log files on the RAID 1 config with the data and tempdb on the RAID 10? If not, please explain the best solution. Thank you!
hello,all I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) . is it possible to do it ? if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ? thanks in advanced!
I have been tasked with moving our SQL server estate onto new 64bit SQL 2008 Virtual servers on a VM base. Each Virtual server will be attached to our SAN that i will have no control over. Do i ask for multiple LUNs pretending that there is a COS), Etemp), FData) and Glog) disk structure or do I just present a very big space as a single C: drive and let it go.We are consolidating lots of old physical servers onto fewer (more powerful) virtual servers (according to the VM and SAN administrators)
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
-- Initialize Control Mechanism DECLARE@Drive TINYINT, @SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area DECLARE@Drives TABLE ( Drive CHAR(1), Info VARCHAR(80) )
WHILE @Drive <= 122 BEGIN SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives ( Info ) EXEC(@SQL)
UPDATE@Drives SETDrive = CHAR(@Drive) WHEREDrive IS NULL
SET@Drive = @Drive + 1 END
-- Show the expected output SELECTDrive, SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes, SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes, SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes FROM( SELECTDrive, Info FROM@Drives WHEREInfo LIKE 'Total # of %' ) AS d GROUP BYDrive ORDER BYDrive
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
Hello I need to setup a compaq sever with 300 MB database, and will be adding around 600 records on a daily basis. Can someone help with how much disk space i should have on sqlserver, providing i have c: and d: setup.
I have a server and it has C: D: F: I: Drives and all the system files are on C:Drive and and all the .MDF's and .LDF's(model,temp,master) are on the F: Drive and now I am running out of space on both(C: and F: Drives)
1. Can we add space to the C: and F: drives on the fly?. 2. Can I move the System databases ( MDF's and LDF's to some other drive)and if so, how do I do it?( Moving the databases ) and this is on the production database so when I have to do this.Will there be any impact.
We need a drive cloning app, like Ghost, that will allow us to transfer a production image off of a HDD to other PCs. The problem with this is that our production needs SQL in order to run and transferring the initial image to uniquely named workstations causes DB registration problems. Is there an app that will allow us to configure this transferred image so that SQL will refer to the "new" drive instead in of the "old"?
We are experiencing high disk i/o on one of our RAID disk systems. Can someone tell me how I can identify the query or user or process which is causing this high disk i/o?
I noticed something strange today. I was running a query using query analyzer on a large database (8.8 million records) and the disk space on the c: drive was dropping and eventually went to 0. Availalbe space on the c: drive is 10GB. The query did complete. SQL server and all the databases are on the d: drive. After closing the query results in query analyzer the disk space returned. Is this a concern and is there a way to change it to use the d: for whatever it is doing?
What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?
If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?
Hi all, I have a problem... I use SQL server 2000,all the disk on computer is used to store data file and transaction log file, and now they are full so data can be insert or update because the data file and transaction log file can be add more or increase, please show me. Best regard,
This is my first attempt using SQL 2000 and DTS. I am importing an Access database using the DTS wizard. The process fails with a "Not enough space on temporary disk" error. There is definitely enough space on the physical disk. I don't have any limits on any folder sizes either. What "disk" is the error talking about, and how do I give it enough space. The database is relatively small, about 10MB. I believe the database was created using Access 97. Please help.
I understand the log files (LDF ) and data files (mdf ) should be on a different drives . I believe it leads to greater availabilty and speed . Are there any other reasons for to keep this on a separate drive.
Also what considerations I should take care while creating a database of around 100 GB . (use of filegroups , growth % etc ). Is there any connection of number of users to number of disks SQL data file to be spread to . Also do I need to take care (through hardware / software for a Quad core CPU ) to take full advanage of Quad core CPU.
I am wondering what normal disk I/O should be. i know it verys depending on use but im looking for an average.
here is an idea of what we have
there is about 10 centers doing replication to our primary server. we have about 80 users connecting directoy to our primary server using MS Dynamics through CITRIX. we have a few other apps use the database as well however i am fairly certin its Dynamics generating our disk IO Hardware wise we have a powerful blade connected to a raid 5 SAN with 15000 rpm disks. normaly the disk IO stays fairly low but every so often it goes crazy and im thinking it shouldn't
Below is a sample of our disk IO from perfmon over 2 minutes or so. as you can see everything looks ok untill 04/15/2008 10:12:49.470 when the Disk I/O % goes above 100%
I'm trying to install a server cluster to implement an SQL Server 2005 cluster. No other services (I think this is important).
I've a dual SCSI channel Smart Array with 4 disks configured in a 400Gb RAID 5.
I do not need to move different resource groups from one node to the other, I need only one group with all the resources IP, Network name, MSDTC, and SQL Server..., when a node fails, all services should failover to the other node.
Is it possible to have only one physical disk (RAID 5) for Quorum disk and shared disk?
It would be the following configuration:
[Groups] Cluster Group IP Address Network Name Physical disk (used for quorum and shared storage) Distributed Transaction Coordinator SQL Server SQL Server Agent Generic Service (SQL Server Fulltext)
The other option would be having a 1 physical disk Raid 0 for Quorum (146Gb wasted) and another physical disk Raid 5 (3 disk) for Shared Storage, but this schema will have a a flaw point that if Quorum disk fails, the cluster fails....
I am writing up a general purpose document regarding Disaster Recovery and Backup and Restore, etc. I have a question that I've been unable to find any definitive answer on, and I was hoping that someone might have an answer.
Here is the scenario and what I have so far, and my question:
Scenario - A simple database, with one MDF and one LDF, each on it's own disk, with no mirroring or filegroups etc.
Disaster - the LOG disk fails.
To simulate the disaster, I shut down SQL Server, and delete the LDF manually.
When I restart SQL Server, the database is marked as suspect. As expected :)
So, now the recovery begins..... I've read that in this case there is no data loss, but I find that hard to believe. Can anyone confirm?
Anyway, how to restore the database to usable status? Here is what I've come up with so far:
1) Do a backup of "the tail of the log." This works even though there is no log. I have no idea why really, but I can't proceed otherwise.
2) Detach the database.
3) Reattach the database. This auto-creates a new log file (albeit not in the location I want, which makes for more work after).
At this point the database appears to be fine. I have not seen any lost data or problems, but of course I'm working on a test database, not with live users hitting it constantly. If the database were under heavy use, I expect that there might indeed be data lost?
Is this the recommended way to recover from a disaster of this type? Or, would it be better to just go back to your last good backups and forget about "up to the minute" recovery in this case? Or is there yet another means?
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ??? Thanks so much for any help... Nancy
