Planning and Designing a SQL Server Database System
Planning, Designing and setting up a new server to host SQL Server instance and databases and customers data requires lot of knowledge about the process, requires lot of practical experience of solution delivery and track record. There are many factors which are involved in planning and designing a SQL Server database system. The SQL Server should be able to provide best performance, security, strong disaster recovery, high availability, easy scalability, etc. There are various processes, documentation, pre-planning, delivery, coordination, communication, testing, etc.
In this post, we are trying to highlight some of the top steps architects need to consider which planning and designing a strong SQL Server system for hosting critical data. First, we need to understand the requirement of what data will be stored, like OLLTP, OLAP, etc. What kind of data is stored, meaning structure data, unstructured data or semi-structured data. How many users will be accessing the system and over a period of 5 or more years what is the expected growth in number of users and amount of data. What kind of security measures are expected. Once we have the requirements available, these needs to be documented and signed off by all stake holders.
Next comes planning phase on how these requirements can be implemented, like how many resources are required to get this implemented and from which technology these resources are need to be from and the level of knowledge expected from the resources. Decide on a time frame on when this needs to be completed and this needs to be realistic to consider planning, implementation, testing, making any further changes, testing, planning and going live with the system. Need to select the versions like the operating system, SQL Server version, hardware to be used, etc. Planning of the hardware is very important as it is the one which decides how we can achieve performance, security, disaster recovery, high availability, scalability, etc. Need to consider future growth as well, because at the time when the plans are laid, the data size may be small and the number of users expected to use the system may be small, but over a period of time this may change and will require additional CPU, Memory, Disk or Network resources, so planning for future growth expectations will reduce the number of attempts to meet the growth requirements in future.
Next steps would be to buy the required hardware as planned before, and using the resources to build the server with best practices, get the Operating System installed with latest patches, ensure latest drivers are installed. Next install latest version of the SQL Server and patches and configure the OS and SQL Server settings as per best practices. Once the server is ready, restore the databases configure logins, jobs, etc and handover to testing. If testing is successful, then we can move forward with going live with the system, if there are any problems noticed, then we need to identify the cause and fix the issues and test again to ensure everything is working as expected. Load testing is one of the important tests to ensure best performance. Once testing is successful, need to involve all resources and plan step by step how to go-live with the system and perform a test go-live if possible and test the system. If all is good, then final go-live can be performed and new system to be monitored for few days to ensure there are no issues. We can then handover the server to the teams which will maintain and administer it going forward.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings