[分享] 系統改善方案企劃書例子

如果今天老闆要你對某系統提出改善方案, 很多工程師包括我也不知道從那裡著手開始寫. 剛好遇到這次機會花掉了我畢生腦 精 力寫好一份SQL Server改善方案, 把小弟的文章部分內容分享一下希望對各位同業有幫助. 部分內容因為公司機密有所刪減, 請見諒 (公司要用英文寫, 抱歉沒中文版本= =")


------
Introduction:
This study will focus on how can we improve existing SQL Server to provide more reliable, more secure and improve capacity in order to fulfill future usage.

Current Status overview:
Operation System: windows Server 2003 SP2 32bits with 3.75 GB ram.
SQL Server version: SQL Server 2005 standard edition.
numbers of databases: 18.


Performance evaluation:
We capture the sample data from 23/07/2012 to 27/07/2012. evaluating 8 indicators[1] covering memery, CPU, and harddisk performance. here is the result:

Existing issues:
Security: 

• • Accounts level:
  • XX% databases using administrator as its login account. That's mean using one account can access several databases.
  • All developers known SQL Server admin username and password.
  • 
    
  • Operating system level:
    According to Microsoft security guideline, the highest risk of infection servers generally meet one or more of the following criteria. Conditions 1, 3, 5 are ture in our environment
  1. Server can access to the internet.
  2. The servers have open ports to servers that are not behind a firewall.
  3. The servers read or execute files from other servers.
  4. The servers run HTTP servers, such as Internet Information Services (IIS) or Apache. (For example: SQL XML for SQL Server 2000.)
  5. The servers are also hosting file shares.
  6. The servers use SQL Mail to handle incoming or outgoing email messages.
     

System:

• • The effectiveness of existing backup solution is unknown.
  • Some databases do not accepted of any system failure or abnormal termination in office hour(ex, dms). Existing SQL Server can not provide any failover capability.
  • 32-bit Operating system means only 4GB ram can be used on the SQL server, no matter how much really it is or what platform it is using(ex. Virtual machine). In other words, the capacity of SQL server is pretty tight.
  • The SQL server is multi-functional.
  • Lack of developmen environment. Developers always do development in production server directly.

Propose improvements:
Security level:

• 1. Create independent account for each databases.
  2. Using independent account for SQL server administration. Reduced the power in a single account(domain admin).
  3. SQL Server admin password can be only known by SQL Server administrator and associated members, not for all developers.
  4. isolated to external environment. Block SQL Server connect to internet, whether incoming or out going.
  5. Simplified server function only for database system. Migrate other programs to another server. It is because execute, read or exchange files can increase the risk of infection.
  6. Disabled all share folder in the server.
     

System level:

• 1. re-Formulate clear backup plan.
  2. Periodically do backup restore drill. Let infra team-mates do it faster and more accurate.
  3. Reconsider the necessity of failover plan. If yes, details planning in hardware, software, network, infrastructure.
  4. As quick as possible to build up development environment. To prevent excess modification in production server.

Resource planning level:

• 32bit to 64 bit: 
  More and more systems choose SQL Server as their final database solution in our department. Moreover, according to a report from Gartner, a company  which delivers technology research, said SQL Server holding the third in global database market share, about 17% [3]. So we can estimate that the usage of SQL Server will increase inevitably. More usage means more RAM would be spent in the future. Unfortunately, 32bits SQL Server maximum support ram size are only 3.99GB, but 64 bits almost no limitation. The higher the memory, the larger is the buffer pool and less time is spent on accessing the data from the physical disk, reading cache, make connections, etc.
  
• Physical to Virtualization:
  Since SQL Server usage is unpredictable, Virtualization technology can dynamically increase or decrease resources to the SQL Server according to the usage. No need to open the hardware server and plug more ram or CPU physically.  More flexible.
  
• Windows Server 2003 to 2008:
  I have read many articles the differences between version 2003 and 2008. The main benefits upgrade 2003 to 2008 are [4]
• Increase server efficiency. Reduce the power consumption of server.
• more features on management and virtualization.
  
  
• Monitor performance regularly.[5]

[1] Evaluation items are based on this article.
[2] Microsoft security guideline [3] Oracle依舊領跑關系型數據庫市場
[4] Windows Server 2008 wiki
[5] Why you really do Performance Management in production.