Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
awieczorek
Starting Member
4 Posts |
 Posted - 2010-11-30 : 12:20:15
|
| Our company is going to implement a SQL Server. Although I have been tasked with this project, I have litle SQL knowledge.So if these questions are idiotic, I apologize in advance.1. Virtual vs Physical? I have read many blogs, and opinions, this will be implemented for use with our accounting package, Dynamics SL & consolidate the mess of multiple data bases that exist. (I hope) Since its Real Time, I am leaning towards Physical, & the ammount of resources needed would be 64bit.1a. RAID 5 or Raid 1?2. Are there any issues to using a pyshical server for the needed resources, but have the d: drive on a SAN for the database and logs locations? would have its own NIC for communications w/ the SAN3. How do mutliple instances, split resources?4. Are multiple instances really needed, or can most data bases be put in the same instance?Answeres dont need to be overly in depth, I am looking for some basic guidance.Thank you, Aaron |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-30 : 12:40:50
|
| 1. Physical unless theres a strong (economic?) reason for virtual.1a Raid 10?2. You won't know until you try3. seperate exe's are run but they will compete with each other for rsources.4. Multiple instances is more of a usability sort of question. If an instance goes down then the others can keep going. You could move databases between instances maybe. In a clustered environment an instance can be moved between nodes. Instances can have different logins.First segregation should be the database - everything that needs to be recovered together shoud be in the same database.5. You probably won't have any difficulty getting it installed and running - the issue will be a few months time when you find performance/maintainability problems.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 13:01:49
|
1. What's your Disaster Recovery requirement? If you need fail over then that may govern Physical / virtual (or some alternative solution). And/Or you may need some other/additional configuration - warm-standby / log-shipped etc.1a. You want your Data optimised for Random Access and the Logs and Backups for Sequential access. I can never rememberer the RAID numbers but I think we are using RAID-6 and RAID-10 respectively.Unless this is a lightweight application, and the query-burden is modest, you want:Data filesLog filesBackup fileson separate disks. Ideally you will have the TEMPDB database on separate disks too. And you need separate disk for O/SYou need (from memory) 1 GB of RAM per CPU for the O/S and then the rest for SQL. RAM used by SQL limited only by the Operating System (I think) so you will need Enterprise Windows O/S ... and then you can decide on the flavour of SQL according to the depth of your pocket! Most features in Enterprise are not needed by most people (or can be worked around) ... but if you must have a feature that will make your decision for you  FWIW we have had to ditch our SAN because the throughout was disastrous. I expect our experience is NOT typical. Making a backup or copying a file from one drive to another maxed out the SAN controller and network interface such that the database crawled to a halt. Good to hear that you will have dedicated network cards, but don't let the File Access IT boys think they can give you like-for-like SAN space and it work well for SQL! Our SAN was dedicated to the SQL box and cost an arm and a leg ...How much backup are you going to keep online? You probably want at least 4x as much backup space as data/log space. Maybe 10x as much. Depends on your backup file retention policy. Its a LOT easier to restore from a file on disk than to get the tape and restore from that ... particularly if your users are shouting!You can mitigate backup space by getting a version of SQL that has Backup Compression built in - or buying a 3rd party backup compression utility (Quest or Redgate spring to mind) |
|
|
|
awieczorek
Starting Member
4 Posts |
Posted - 2010-11-30 : 13:55:46
|
| Disaster Recovery would be dependant on the type of install, Virtual, or physical. Physical would be Veritas regularly, either nightly, or twice daily.Physical w/ D Drive on the SAN allows for the Host Integration Tools ot be installed, and "Snapshots" can be scheduled for regular intervals. We would have the snapshots stored on the SAN, and a hard back up once a week, removed from the building.All Virtual, would be a back up of the Virtual Machine, and as described above.Im not sure on lightweight, as we only have a total of 67 employees, and of those only 19 people who would access the system, 9 people 9-5 every day, 9 people at any given time if they need access, but are not logged in all day, and 2 people during off hours of regular business.SAN space will not/should not be a issue, as Im also in control of that... :)Backups: Depending on application, some stuff is backed up weekly, or daily, depending on if its on the SAN. SAN data is snapshot backup on a regular schedule, then backed up once a week to the Back up server. and then also placed on tape for removal from the building. anything not on the SAN is backed up to the Back Up server nightly, and then also placed on tape weekly.Thanks Aaron |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 14:11:39
|
"Disaster Recovery would be dependant on the type of install, Virtual, or physical"IME the cost has controlled whether DR was a failover server, or a virtualised server that could do multiple duties - e.g. running both Web and Database for a short time until the original SQL Server was sorted out / upgraded / replaced."we only have a total of 67 employees, and of those only 19 people who would access the system, 9 people 9-5 every day, 9 people at any given time if they need access, but are not logged in all day, and 2 people during off hours of regular business"Dunno if it helps, but I would ask the question "For every hour of the working day you lost how long would it take you to catch up". You might find that some/most people could catch up an hour in 10 minutes ... or you might find that for a few/many it will take an hour  "Backups"Make sure your tape backup copies the backup files and NOT a shadow copy of the database files. I don't know enough about SAN shadow backups, but shadow backups of the database files, themselves, won't work (AFAIK). Our SAN does (did!) shadow copies of the Backup Drive. However, when we repurposed one of the machines it came to light that we needed 250GB of spare disk space to take an initial shadow-copy of the machine ... we really struggled to get 250GB free at that moment in time, and the backup failed for 3 nights in the row - each time we had to move yet more transaction log backups to here-there-and-everywhere to try to make space (and we had to copy at a gentle rate to avoid overloading the network bandwidth). I wonder whether all this new-fangled-stuff is all it is cracked up to be!! |
|
|
|
awieczorek
Starting Member
4 Posts |
Posted - 2010-11-30 : 14:21:26
|
| I will take all this into consideration. Thanks for the insight thus far.Any thoughts or opinions on 32Bit & 64Bit?Thansk Aaron |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 15:48:34
|
| Can't think of a reason to go with 32bit any more |
|
|
|
awieczorek
Starting Member
4 Posts |
Posted - 2010-12-01 : 09:18:45
|
| I will look into all of these, and thank you for the insight. Also in looking into the software, we have SA on the SQL, SO I have the option of SQL 2008, or SQL2008R2 (SQL2010) I belive.If I go with SQL2008R2, would I run into any issues, or problems, with older software needing to connect to the newer version of SQL?What I would like to avoid is the "Not Supported, but may work" scenario.Thank You, Aaron |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-01 : 10:44:55
|
| And given how quickly MS remove support for versions now...==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-01 : 11:41:31
|
"What I would like to avoid is the "Not Supported, but may work" scenario."You need to run a FULL Regression Test before changing SQL versions.There is NO guarantee that what works on SQL2005 (say) will work the same on SQL2008. The guarantees might be better between SQL2008 and SQL2008-R2 ... but ... you might be the first person to find an issue. If you don;t do any regression testing for Service packs then I guess you might not bother for SQL2008/R2 either ... but I think you should for major versions like SQL2005 to SQL2008.There are analysis tools that will tell you if your software will definitely NOT work on the new version, but there is nothing that will tell you if it will work exactly the same as before, or not - except a good regression test  |
|
|
|
|
|
|
|
|
SQL 2008 Consideration's