I have a SQL 2000 server that is installed on a Dual Xeon server running win2k. The server has two raid 5 hard drives, a C drive and an E drive.
The C drive is currently where the operating system files are stored as well as the SQL program files. As things stand there are SQL DB and transaction logs strewn between these two drives with no particular logic.
My question is, with two drives as it stands how should I move things around to gain the best performance? For example, should I keep all my data on the E drives and all my transaction logs on the C drives with the OS and the program files?
There are about 10 Databases in use. One database run's the configuration for proprietary predictive dialing software. The other databases are calling information for each campaign we run within the dialing software.
I have enough space on both drives to accommodate the data, its performance I would like to see a difference in.
I'm building a database that has maybe four unique tables Student, Advertiser, Employee, maybe Account. Three of the four table (Student, Advertiser, Employee) have something in common in which they all contain fields such as emailAddress, password, role, isAccountActive, etc. which allow them to access their respected data. However, is it best practice to build a fourth table which contain Account information or should I just include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can manage all accounts (Student, Advertiser, Employee) from one table, but as the database gets more in-depth you have to build more and more complex stored procedure to do simply task such as update, delete, select, etc.
I'm currently building a database that is going to have at least 6 different types of users accessing it via a web application.
A user will have different information collected about them. A few of of the users will have the same or similiar information collected about them.
I will be using role based authentication.
Now my query is this;
Scenario 1: Would it be a good idea to keep all the users common information in one table. Ie. thier username,email,password,name. Then create a tblDetails for each type of user that would contain the different data. That way i can just check one table to verify thier login credentials.
OR Scenario 2: Would it be best to create a seperate table for each type of user and then log them in based on the credentials stored in each type of users respective table.
Hope this is clear. I'm leaning towards scenario 1, although I've used scenario 2 before. Just wondering which would be preferred.
I am new to SSIS. I need to transfer data from SQL Server 2005 Operational Database to SQL Server 2005 Report Database. The upload needs to work every night. There are few master tables and remaining are transactions tables.
I am planning to create 2 packages one for master tables and other for transactions tables. Is it the good approach?
Also few of transaction tables are heavy in terms of number of records. Will it better if i further break them in many packages?
I am using book "Microsoft SQL Server 2005 Integration Services Step by Step". Can you suggest any other good book?
I am trying to run a program called NVivo7, which - when I install - also requires installation of SQL Server. Because my C drive is small (and nearly full), I am trying to run NVivo7 off my D drive, though SQL seems to install on C. Is it possible, do you think, to use 2 different drives in this way, or do both the program and the Server need to be on the same one? If so, is there any way to get them both on D?
So i'm not really new, but got a question. i've recently been looking to to the Western Digital Raptor Drives.
as far as performance, it's always been my understanding that the speed of the hard drive is just about always the bottle-neck of a computer. i'm currently running 2 stripped WD 500gb SATA drives for my SQL server (dual Xeon 2.8 with 2GB memory).
i'm thinking of upgarding to 4 WD Raptors (10k RPM) drives, the new 150GB models. anyone have an opionion? do you think i'll get a large performance increase?
the database that i run queries off of now is about 125 Million names, with about 80 fields in width. so it's rather large, and usally takes a fair abount of time to get my results back (we're talking anywhere from a minute, to half the day.)
do you think the raptors will slim that down significantly?
We recently installed SQL Server 2005. The server has 3 drives. When I try to restore a database I can only access the C: drive. How do I make the D: and E: drives visible in the "locate folder" window?
Can someone help me, I installed SQL 2005 Enterpirse Editon on windows clustered servers. Then after the installation I want to change the path of my DB logs but the problem was, I can not see the other drives. I can see only the drive where the DB was located. Is there any special configurations that should be done.
Ideally we'd like to configure our SQL cluster w/ the databases on one drive and the logs on another. Is this feasable in a cluster solution.. Will it basically just be 2 drives that are failed over vs. 1?
Hi , We are having 4 sql7 servers. All are in network only. serverA: drives c,d serverB: drives c,e serverC: drives c,d serverD: drives c,d,f Now my question i want map all the drives to each other. So that i can use the space wherever available, because in serverA i dont have space but in ServerC have lot of free space. Can anyone pls tell me in detail how we have to map the drives. Thanks
Hi, I'm looking for a way to check the free space left on the hard drives and then if needed send an alert to notify when we need to free up some space. I played around with the performance monitor and realized I could do it that way but I think you would have to leave the performance monitor running all the time and I'm not sure if I want to do that. I also read about the xp_fixeddrives proc that displays how much free space is available but then I don't know what to do from there? Does anyone have any recommendations for the best way to do this.
I mapped a drive on to my SQL Server box. It points to another server from the same domain. When I try to backup or restore a database, I can't see this mapped drive through my SQL Server. Even if I type the entire path, SQL Server wouldn't take it. I don't have a clue about why it is not working. Can anyone throw some light on this. Your help is grately appreciated.
Can someone tell me if it is possible to see the drives on the server using Perfomance Monitor? I so, where are tehy hiding because i struggled the wholed day!
Problem: I have following drives on this server, Is there a query that I can run daily which will show us the % full and remaining storage capacity on these drives?
Logs(L) Data(M) Tempdb(T)
I would like to run that query on all of my servers to get a daily report.
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
I have a 75 GB hard drive and a 300 GB. I want to mirror the 75 to the 300 and use the extra space as data storage. Is this possible if I partition the 300 and then mirror the hard drives.
Is this possible? I seem to remember doing it with SQL Server 7 a long time ago. The Microsoft Knowledgebase says it's not possible with 4.2 through 6.5, but nothing about 7.0 and up.
hi all we have some servers connected in the same LAN, most of them with sql server. our production database is on a server with some several mapped drives (network drives). we have a maintenance plan which makes a database backup on one of this mapped drives, and also we make a backup of the transaction logs in another mapped drive.
recently we install another sql server on a new server and we are trying to create a maintenance plan in the same way as our production server. so we are trying to make backups in mapped drives. the server options are the same but the new server don't recognize the mapped drives...
is there an option or something that makes server recognize mapped drives or simply this server is not-well installed?
Basically what I am trying to do is run bcp from within a stored procedure in SQL 6.5. It allows me to dynamically assign the destination filename (the legacy application expects to see a file for each picking slip) I want to store the file on another server/shared directory on the LAN
Here is the code DECLARE @ship_no varchar(16) SELECT @ship_no = '1234567' DECLARE @SQL_SYNT varchar(255) SELECT @SQL_SYNT = "master.dbo.xp_cmdshell " + '"BCP AUSTCHAR..armaster out servernameBECpsqlout'+@ship_no+'.cba /c /t , /r /Sservername /Uuser /Ppassword"' EXEC( @SQL_SYNT)
However I get the following output:
(1 row(s) affected)
(1 row(s) affected)
output --------------------------------------------------------------------------------------------------------------------------------------------- DB-LIBRARY error: Bcp: Unable to open host data-file.
The destination file is not created. I have tried: making the path the local HDD that works: Using a mapped drive, that does not work Running the same bcp command from the command line. That works. Using UNC's (as above) You can see the result. I have also checked security and made SQLexecutive ran as the domain admin. Did not work.
Are they're any real disadvantages or advantages in having 1 massive disk partioned to create 2 logical drives (not including the C drive) and separating the SQL Database File & Transaction Log so that it doesnt reside on the same logical drive?
Hello! I hope that I have posted in the right spot. We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
I have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
For single instance installations I try to follow a standard configuration where our log files go to E, data files to F, tempdb to H and backups to G. With a new project we have decided to combine our Test and Dev environments on one server with Test being a default instance of SQL Server and Dev being a named instance. My plan was to stick with the above convention, but I was asked if it would be better for each instance to have dedicated data, log and tempdb drives. The problem is we would not have a dedicated controller for each drive. We would have one controller for the default and named instance data drives, one for the log drives and one for tempdb. The other issue is having to modify some of my admin scripts/procedures to work with different drive letters. That's now a real big deal, but does add more work to my plate.
Do you think it's worth having separate drives for a Test/QA server or is it typically sufficeint to use shared?
Can't find anything on how to install to drives other than sys. I'm brand new to Servers and Databases in general and trying to learn. How do I get databases to install on drive partitions other than my main system drive?
I am having difficulty getting both xp_cmdshell and the Scripting.FileSystem to work via sp_OACreate/sp_OAMethod with files residing on network drives . I am able to create/move/copy/delete files on local drives through both of these methods, but when I specify a network drive as the target an operation requiring write access I get an "Access is Denied" error. I recognize that the SQLServer service account is being used to perform these operations, and I have granted Full control permissions to this user on the directory I'm trying to perform the operation on. Is there some other permission either on the SQLServer or Windows account that is required to enable write access from T-SQL?
Every time a new database is created on my server,my log files and database files automatically default to the following to the C drive in the following path: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData. Is there a way to get them to default to different drives?
For example, right now I have my data files on our F Drive, which is a RAID 5 and my log files on our G Drive which is a RAID 1. Every time an out of the box application installs a database, it does so on the C Drive. I want data files to default to my F Drive and log files to default to my G drive? Is this possible?
I have created a new user in my database called "acm", I also created a new schema called "acm" as well, the "acm" user is its owner, and the default schema for "acm" user is the "acm" schema.
I also created a login called "acm" (surprise surprise) and set its default database to "mydb" database.
In "mydb" database I have a table "mytable" that is owned by "acm". In my connection string I am using "acm" as the user, but when I do: "select * from mytable" I get error ("bla bla object bla bla"), when I use "acm" as a prefix for the table name it works fine... What do I miss??
My connection string is:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAccountManagerDb.mdf";Persist Security Info=True;User ID=acm;Password=acm;Connect Timeout=30;User Instance=False
I am working on deploying my production DB's to a new server and am looking for some advice. The new server is running RAID-5 (the old one is RAID-0) but it only has one controller therefore I can not create another array group. So I was wondering since the disk setup is C: and D: (Virtual Drive) if I would see any benefits from placing my log files on C: and my Data files on D:?
The other question is related to the size of my log files. Some of the DB's were not created by me and there are no constraints in log file growth causing some very large TRANS logs. For example, there is a 100 MB DB with a 1 GB trans log. I restored the DB on my new server and truncated the log file (manually, the Ent Mgr Shrink DB tool doesn't work worth a hoot) to half the size of the DB and it functions properly. I was just wondering if this would cause any problems down the road if I did all of my DB's like this?
I am restricting log file growth to approximately half the size of the DB.
There is one DB that is 1.5 GB and the log file was set restricted to 2mb and it works fine, but I feel that I should bump this up a bit.
Hi, I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I didn't see any options on the MSDE install that implements seperate data/log paths.
I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.
