| Author |
Topic |
|
marko198
Starting Member
4 Posts |
 Posted - 2014-04-18 : 05:42:10
|
| I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization.I have identified the main databases that are mission critical to the organisation. We also have a virtualised infrastructure so plenty of VM's. We currently run SQL Server 2008R2.Fundraising Database (off the shelf package - 30GB in size with about 100 users)Fundraising Data warehouse (off the shelf package - 30GB in size with about 5 users)SQL Reporting Services Database (1GB - reports used across the whole organisation so about 400 users)Case Load Services Database (Developed in house - 4GB in size with about 300 users)Finance Database (off the shelf package - 2GB in size with about 150 users)GFI Exchange archive databases - (off the shelf database - 30GB in size with about 300 users)Then we have about 10 other smaller databases for system monitoring etc... that are important but not mission critical. Now the question I have is (bearing in mind limited budget and resources) is whether I should put all of these databases on 1 single instance on 1 single Windows Server 2008 VM. Or would it be better to have them on a single box with separate named instances? Or possibly even separate VM Servers splitting out certain databases each?My thoughts was to have 2 VM's. SQL01 for the missions critical databases. SQL02 for the other databases that are non mission critical. I was even toying with the idea of separate named instances on SQL01 for security and the ability to manage services separately without affecting other databases? For example if we need to restart a service during working hours we would not have to take out the whole organisation but only users on 1 database.I know there are many factors that affect this decision but from your experiences and knowledge what is the general rule of thumb when redesigning the architecture for a SQL database landscape like we have? Obviously each of the system requirements of the off the self packages say a single database ideally not contending with other database but I take it that its not really what happens in the real world as licensing SQL and hardware resources would be significantly wasted that way. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-18 : 15:45:45
|
| My decision would be based on hardware resources, contention between the databases (if any) and how those applications handle security. Some 3rd party packages have very weak security, insisting on using the sa account for instance. Given the information you have provided, assuming sufficient hardware for said databases and assuming strict security for each of the databases, I would use a single instance of SQL Server.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
marko198
Starting Member
4 Posts |
Posted - 2014-05-24 : 11:01:34
|
| Thank you all for your expert views on this matter. Thanks again guys for all your input onto this. This has been such a learning curve for me and my fellow charity colleagues.I have now established that we are going to go with 2 SQL Servers with the number of databases and types of systems we have with their specifications. What my next question is that I need some basic device on is how to structure the storage for this config. So we have NetApp SANS I believe and my understanding is that there is some kind of best practice that can be followed to setting up the LUNS and volumes etc... with SQL databases So for example one of my boxes will have 4 databases on there. We will call it SQL01. The second server will be called SQL02 and will also have around 4 databases. What is the best way to setup the storage aspect for this arrangement? Again I know this is one of these questions where there isn't a simple answer too but in terms of best practice what should we be doing?I was told by one of my colleagues that we should split out the drives so that you have C drive for the OS, D drive for SQL binaries, E drive for Data, F drive for System dbs and G drive for log files or something on those lines. Does it matter if both SQL01 and SQL02 share the same pun or volumes etc....My IT guy also wants to do Snap Mirroring for our DR so does that have affect on the way we set this up? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-25 : 19:13:18
|
| We use mount points and separate everything:F:F:\BackupF:\Data1F:\Data2...F:\LogF:\TempdbDataF:\TempdbLogF:\ is where you'd install the SQL system stuff.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
marko198
Starting Member
4 Posts |
Posted - 2014-05-26 : 01:02:03
|
| Hi thereThanks for this. So in Windows Explorer you actually only have one mounted drive in F. But each folder within that drive is split onto different volumes and LUNS on your SAN storage? I think we need to ensure that they are separated out because of the way SNAP Mirroring works with NetApp.Many thanksMark |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-26 : 04:02:05
|
| Another considertaion is SQL Server Licensing ? Are you using processor or server licensing model. This may impact how you set up servers\instancesJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-26 : 20:02:12
|
quote:
Originally posted by marko198
Hi thereThanks for this. So in Windows Explorer you actually only have one mounted drive in F. But each folder within that drive is split onto different volumes and LUNS on your SAN storage? I think we need to ensure that they are separated out because of the way SNAP Mirroring works with NetApp.Many thanksMark
Yes one drive letter per SQL instance is how we have things configured. Each mount point has its own set of LUNs. We've used Veritas Storage Foundation to manage the disks/mounts on a server.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-05-27 : 13:50:18
|
| I would not recommend using a single instance - and probably would not recommend multiple instances on a single node either. Your mission critical systems will have different support requirements as well as resource requirements.Putting the data warehouse system on the same instance and/or server as the production system would not be a good idea. When users run massive adhoc queries that needs to read through all of the available data - it will impact the CPUs, IO and memory on that system. This will definitely have an impact on your production systems.I would say you need 2 separate servers (at a minimum) - one for production systems and the other for data warehouse/reporting/archive. Whether or not you use multiple instances on each one will depend on security requirements for each application. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-28 : 01:34:23
|
| As well as system load profiling , licensing requirements etc, arrange the servers\instances in a way to allow Windows and SQL Server patching to be efficient. Typically a server restart is required - so what is the impact of the server restarting? will it be approved by all owners?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
1 Server multiple instances or 1 instance or even