April 8, 2008 at 5:20 am
Morning,
I'm sure there are going to be dozens of right answers for this...I have a 7 gb database I'm moving to a new VM I have to have 2 instances one for test and one for prod of the same 7gb database. How should I go about sizing that? It grows about a GB every six months. Is there a good place to go to that would give me a good idea of where to benchmark it?
Thanks in advance
Laura
April 8, 2008 at 5:41 am
I'm not sure about what your question is. 7gb database is relatively small, so you can set it to autogrow 10-20%:
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'Mydb', FILEGROWTH = 20%)
You can't have the same database file on two instances.
What do you want to benchmark?
Regarding storage, more spindles means less conflicts, so for faster access you can put database file on one disk, log file on another and tempdb on third disk.
April 8, 2008 at 5:45 am
Really? I have an application that does not allow you to choose the database. So I thought that if I put it on 2 instances I would be able to have test and prod on the same machine. Thats not true?
Laura
April 8, 2008 at 7:11 am
Well, 'can' and 'should' is a little different.
Yes, you can have two instances on the same box, where you have two databases with the same name (one in each instance), and the may also have the same filenames since they live in separate directories. Each SQL Server instance has it's own private set of binaries, directories, registrykeys etc....
If you 'should' do it is another question...
Personally, I wouldn't really recommend anyone to share a production and test environment.
They should be separate, so that one doesn't interfere with the other.
This is mostly a matter of security and stability.
If by WM you mean WM-ware or similar, this is generally not recommended for production uses.
Though it does indeed depend on the requirements of the system in question, it may be good enough.
Just be aware that for any 'high-traffic' the risk of poor performance may become greater.
For one thing, disk access is usually much slower in a virtual environment.
/Kenneth
April 8, 2008 at 7:21 am
Thanks Kenneth. Unfortunately thats all I have. Thanks for the information though I appreciate it.
Laura
April 8, 2008 at 7:41 am
Not sure what WMware is but I am running VMware and ESX server here. We have some smaller production databases (less than 3 Gb each) running on it without issue (actually faster than old server). There has been a lot written (mostly against but some for) about VMware and production SQL server. Google it or search on this site. Lots of good information. As long as you can put enough resources on it (memory, CPU, disks), you should be fine. It's the wave of the future for some of us, whether we like it or not!
-- You can't be late until you show up.
April 8, 2008 at 8:42 am
As long as you can throw enough hardware (resources) at just about anything, mostly things are fine from that perspective. (assuming coding is proper as well)
As I said, it depends on the particular requirements.
Anyway you turn, though, a virtual environment is slower than a 'pure physical' environ.
For SQL Server, the most sensitive area is I/O capabilities.
It doesn't say 'never use', it says 'know the limitations'
/Kenneth
April 8, 2008 at 8:54 am
If the growth is a GB per 6 months, you should not be using the autogrow at all, add 3 or 4 of GBs for an year and sit back! Regarding multiple instances, yes, as being said, try avoiding mixing the prod and dev on the same image. if not avoidable, ensure that the memory is capped for each instance. Since, I guess this is a small application, I can guess that the edition will be standard. Dedicate 1 GB for each instance and set the max server memory, otherwise the instances will compete with each other. Ensure enough cpu as well while you create the vm image.
April 8, 2008 at 8:57 am
If you use VMs, why use instances?
VMs can work for SQL Server, remember that you take a hit in performance in a VM v the same sizes physical machine. IO seems to be a bigger problem than anything else, so be sure that you have enough IO, preferably a SAN to support the SQL Server load.
April 8, 2008 at 9:03 am
I have to use 2 instances because I want to have one database server and the application won't let me change the name of the database it points to so the only way I can have the same server with 2 databases the same name was this way.
Thats right correct?
April 9, 2008 at 1:50 am
Not exactly. It's not entirely correct.
This is because the full name of a database is servername.dbname
Even when you have two instances installed on the same server, the servername part is different for the two (hence it's called 'named instance') They also listen on different ports.
So, in essence you still have different names, even when using two instances on the same box
(be it physical or virtual)
Now, if this matters depends on what 'dbname' regarding to your app really means..
If it's just the database name within the instance, then you can have the same name in prod and test, as long as they both belong to two different SQL Server installations. Two separate boxes or two different instances doesn't matter in the logical sense. They are different and the servers have different names.
What you need is a way to 'point' you app to different servernames, and this most certainly you can do. (otherwise all users of this app would be forced to access some hardcoded one-for-all servename)
So, following this logic, you're not absolutely forced to cram prod and test together on the 'same server', you should very well be able to separate them, even though the app by it's default wants to connect to myDb.
/Kenneth
April 9, 2008 at 5:06 am
Thanks Kenneth for taking the time to explain it. Much appreciated.
Laura
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply