Walking with SQL Server

1. Memory Capping SQL Server is an application that uses as much memory as is provided to it. Setting memory too high can cause other applications to compete with SQL on the OS and setting memory too low can cause serious problems like memory pressure and performance issues. It is important to provide at least 4 GB memory for OS use, if you are running only SQL Server as an application on the server.  2. Data File Locations SQL Server accesses data and log files with very different I/O patterns. Data file access is mostly random whilst transaction log file access is sequential. Spinning disk storage requires re-positioning of the disk head for random read and write access. Sequential data is therefore more efficient than random data access. Separating files that have different access patterns helps to minimize disk head movements, and thus optimizes storage performance.  3. Auto growth Auto-growth of the database files should be set in MBs as it will al...

Right from when the environment is built, it is important to setup few parameters so that we do not face issues later when project team starts to use the environment. Since it is a specific documentation which targets the server builds where SQL Server is installed, it will have all the information related to the best practices to be followed right after the server build and SQL Server installation are performed. We have faced many issues in past where due to these small settings or configurations, we could not get system as it is after an outage. Backup availability:  For any database and server, it is important to have backups in place. Application backups and Server backup separately is required to bring up any service to a point in time in case of recovery from an outage.  Resource setting:  If the resources allocated to the system are not properly been configured right after the installation of the software and server builds, it might cause issues later w...

Pre-Requisites We usually think of SQL Server simply as a database engine, while it is a data platform which supports data warehousing, reporting, data integration and data analytics. An in-depth planning is required when upgrading from one version of SQL Server to a newer version along with the features that SQL Server offers. Upgrading these extensive number of features can be remarkably complicated and time-consuming than just upgrading the database engine alone.   Below mentioned are few of the “lessons learned” that help reduce risks of data losses etc.: 1.        Application compatibility: Before planning for upgrade activity, make sure that you discuss the compatibility of SQL Server and the associated application with your application vendor.  2.        Preparation for migration: In case your upgrade activity fails due to some reason and there is no solution possible other than migration,...