Wednesday, October 10, 2012

MS SQL 2012: Some important configurations


I. Change security authentication mode
  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
  4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
II. Enable the sa login
  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. On the General page, you might have to create and confirm a password for the login.
  3. On the Status page, in the Login section, click Enabled, and then click OK.
III. Change memory
  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Memory page, set Minimum & Maximum server memory
  3. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
IV. Change IP & port to connect
  1. Open SQL Server Configuration Manager
  2. Under SQL Server Network Configuration >> Protocols for MSSQLSERVER >> right side >> right click on TCP/IP >> select Properties >> tab IP Address
  3. Select an IP option (e.g. IP2) >> change IP Address value to the IP which you want to use for connection >> Change TCP Port to the port which you want >> select Enable = Yes
  4. Restart MS SQL Server
V. Change auto growth
By default DB will have auto growth is 10% for its data file and log file. It may cause disk space problem when your DB increase data. To change, let do below steps:
  1. Right click on the DB >> select Properties >> select Files
  2. Click on ... button (see the following picture) then change the auto growth number.





Subscribe to RSS Feed Follow me on Twitter!