DOT NET RULES

SELECT  log_reuse_wait_desc
FROM    sys.databases
WHERE   name = 'football'

USE MASTER;
GO

CREATE DATABASE [Football] ON  PRIMARY
( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 80MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON
( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 20MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB)
GO

USE FOOTBALL;
GO

CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO

CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO

declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<4000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END

BACKUP DATABASE [Football] TO  DISK = N'D:\DATA\backup\football_full.bak' WITH INIT,STATS;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football_log.bak' WITH INIT,STATS;
GO

ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football1_log.bak' WITH INIT,STATS;
GO

ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO

BACKUP DATABASE [Football] TO  DISK = N'D:\DATA\backup\football1_full.bak' WITH INIT,STATS;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football2_log.bak' WITH INIT,STATS;
GO

USE MASTER;
GO

CREATE DATABASE [Football] ON  PRIMARY
( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 180MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON
( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 40MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB)
GO

DBCC LOGINFO(N'Football');
GO

USE MASTER;
GO

DBCC SQLPERF(LOGSPACE);
GO

USE FOOTBALL;
GO

ALTER DATABASE Football MODIFY FILE (NAME =N'Football_Log',SIZE=60MB);

DBCC LOGINFO(N'Football');
GO

DBCC SQLPERF(LOGSPACE);
GO

USE FOOTBALL;
GO

CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO

CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO

declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<100.000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END

SELECT * FROM Footballer;
GO

DBCC LOGINFO(N'Football');
GO


DBCC SQLPERF(LOGSPACE);
GO

Configuring SQL Server after installation October 28, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: SQL Server Configuration Manager
add a comment

In this post I will demonstrate how to configure SQL Server after installation. I have a relevant post in this blog on how to install SQL Server.

I have installed SQL Server Developer edition in my machine. Developer edition of SQL Server 2012 can be installed in a client operating system.

1) I will use SQL Server Configuration Manager to change some initial configurations.We can manage the various services that are running and are related to SQL Server.We can manage the various network protocols.

Have a look at the picture below to see how you can open SQL Server Configuration Manager.

2) After firing up this tool, I can see all the SQL Server Services that are installed in this machine.You can see their names, their state, their start mode, that security account they are running under,the Process ID and the Service Type.You can see that there are 3 Database SQL Server Services running in my machine.One service for the named instance for the SQL Server 2008 R2 Enterprise Edition, one named instance for SQL Server 2012 Express Edition and one service for the default instance of SQL Server 2012 Developer Edition.

Have a look at the picture below

3) We can Start, Stop, Pause, Resume, Restart each service. Have a look at the picture below

4) We can also set/change the various properties by clicking on Properties.

We can change the Start Mode of a particular service e.g From Automatic to Manual.Have a look at the picture below.

5) We can also configure settings that are relevant with network protocols.These are going to be the protocols that SQL Server listens to for client connections.

When I select the protocols for the MSSQLSERVER I see there are 3 protocols:Shared Memory,Named Pipes,TCP/IP.If you want to allow remote connections from clients over a network like the Internet that supports TCP/IP we must enable that protocol.

Have a look at the picture below

We can also change some settings regarding the TCP/IP protocol by clicking on Properties.We can change the default  TCP/IP port number 1433, to something else.

After changing these settings you may need to restart the service.

Have a look at the picture below

6) Another thing you can do with SQL Server Configuration Manager is configuring client connections to other SQL Servers, meaning how this SQL Server instance is going to make outbound database connections.

Have a look at the picture below.Please take a note on the order of the protocols.You can see that all the protocols are enabled.

These are the protocols that SQL Server uses to connect to other instances of SQL Server. We can also set aliases from SQL Server Configuration Manager.A client will connect to the alias ( a name other the server instance name ).You can set the alias name, the port number,the protocol and the server it will connect to.

Have a look at the picture below

7) In order to show you the rest of SQL Server configurations I will switch to the familiar environment of SQL Server Management Studio – SSMS.Make sure you close the SQL Server Configuration Manager snap-in.

I will connect through SSMS to my SQL Server 2012 Developer Edition using Windows Authentication.Then I select the SQL Server instance and right-click on it. From the available options I choose Properties. In this new window I can set  various options regarding memory,processors and security.Do not make changes that affect the whole SQL Server instance unless you know exactly what you are doing.You should consult the product documentation. I will be going through most of these options in the next steps.In the General tab you can see information about the name of SQL Server instance, the language, the number of available processors,the amount of memory.

Have a look at the picture below

This information is read only.

8) We can also click on the Connection Properties in the Server Properties Window.
You can see the Authentication Mode and the username I used to log in.I am connected to the master database. You can also see the product name and version of SQL Server. There is also adequate information regarding the operating system.This information is also read only.

Have a look at the picture below

9) The next option in the Server Properties window is the Memory tab.

Have a look at the picture below

Most of the default options in this tab will be ok for most SQL Server configurations , unless you have heavy loads.Minimum and maximum server memory settings let you control how much memory SQL Server can use. We all know SQL Server is very demanding when it comes to memory.You should change these settings if you have another server application running in the same machine e.g Exchange Server. By the way you should never do that, having another server application running in the same machine where our production SQL Server lives. Some people ask me how much memory SQL Server is using.Well the answer to that should be “All of it”.

If you have 32 Gbytes of RAM in the box that SQL Server lives and there is only one database attached on this server that has a size of 32 Gbytes,SQL Server will cache the entire database in memory.This will improve performance significantly.If you add more memory to the server, make sure you will increase the Maximum server memory as well.

To recap the default memory settings and in particular the maximum server memory setting,if unchanged allows SQL Server to use as much memory as it wants. Well SQL Server listen to calls from the OS and it releases memory back to it so the operating system is not starved.You can change the settings from the Server Properties window.You can also use T-Sql to do that

Have a look at the picture below to see the statements I am using to set the maximum memory to 3800 Mbytes. In my machine the available Physical RAM is 4Gbytes.

 

Some people suggest that we should limit this number (maximum server memory) to 80-85% of the actual physical memory on a server that has only the Database Engine running.

You could use T-SQL to get the values for minimum and maximum memory settings.

In a new query window in SSMS, type and execute the following statement.

SELECT
    name,
    minimum,
    maximum,
    value_in_use
FROM master.sys.configurations
WHERE name IN (‘min server memory (MB)’, ‘max server memory (MB)’);

You can also use a DBCC command to get a snapshot of the current memory status of SQL Server.

In a new query window you could type DBCC MEMORYSTATUS. Have a look in this link for more information.

In the Memory tab  you could see another option if you are run a 32-bit server.This option that does not show up in my case since I am running a 64-bit version of SQL Server (as we saw earlier) is Use AWE to Allocate Memory .Do check this option for 32-bit servers with >4GB of memory, and keep it unchecked in all other cases.

Some of the changes I mentioned above will take effect immediately but in some cases you must restart the service.

10) The next set of options I will be looking into are the ones located in the Processors tab.

Have a look at the picture below

You can see that there are 4 available processors in this computer.SQL Server supports many processors and takes advantage of their power.

If you want to change the I/O Affinity or the Processor Affinity  you must first uncheck the two boxes under the label Enable processors.

Let me explain what I/O Affinity and Processor Affinity mean. By setting the Processor Affinity to one or more processors you basically dedicate SQL Server to use one or more of these processors.

By setting the I/O Affinity to a specific processor you basically bind I/O threads to this  processor.

I would advise against changing the values for I/O Affinity and Processor Affinity . Leave the default settings.

Another option I want to talk about is Boost SQL Server priority.BY enabling this option, SQL Server process will run as High Priority instead of the usual Normal priority. In most cases this will not bring any benefit to your database applications so leave the default value.

Another option I want to talk about is Maximum worker threads.This helps limit the resources consumed on an SQL Server instance by client connections. A new Windows thread is created for each connection.If you set a maximum it can hurt performance.Once that limit  is reached any new connection must wait for another connection to close in order to have access to the server.

By default, the max worker threads setting is 0 in SQL Server 2005,SQL Server 2008 and SQL Server 2012.Do not change the default value.

11) In the next tab we will look into settings regarding Security in SQL Server.I have already blogged about SQL Server Security in this post.

Have a look at the picture below.

We have two authentication modes in SQL Server,Windows Authentication Mode and Mixed Mode.

Windows Authentication basically means that a user must log in to a windows domain or a windows local account first before logging into an SQL Server system.Basically a security token is created by Windows when someone logs in and this is passed to SQL Server that “validates” the account  name and password based on that token. In a sentence the user logged in SQL Server is checked and identified by Windows.

SQL Server validates the account name and password using the Windows principal token in the operating system.

Windows Authentication disables SQL Server Authentication.I have been asked if you can disable Windows Authentication and the short answer to that is NO.

When possible (Microsoft recommends it) use Windows Authentication only since it is inherently more secure.For example when using Active Directory you can use Group Policies which means we can enforce certain policies like “enforce users to use strong passwords”.Another very useful policy is the “Account Lockout” which locks accounts when too many failed login attempts occur.

We also have Mixed Authentication Mode which basically means Windows Authentication & SQL Server Authentication.

With SQL Server Authentication mode SQL Server manages a seperate set of users and groups to give permissions to.SQL Server logins are not based on Windows accounts,usernames and passwords are created by SQL Server and also stored there.

If you choose to have SQL Server Authentication you would have to keep credentials for your windows domain and SQL Server.When using the SQL Server Authentication make sure you create a long sa password and hide it.

In any case you need to have this authentication mode enabled especially if you need to provide access to your users outside the organisation.SQL Server Authentication does not require a domain controller.

We can obviously change the authentication mode from SSMS but we can also type the following in a new query window

USE master
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

--Windows Authentication mode
USE master
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

In the Login auditing section we can select what kind of logins we will audit.

We can also use C2 Audit mode. It configures SQL Server to record attempts to access statements and objects.

C2 Audit mode can still be configured in SQL Server but is now rarely used. It has a negative performance impact through the generation of large volumes of event information.

When you set this option you should be prepared to run out of disk space.If this happens SQL Server will shut itself down.That is why I think it is imperative for everyone to check through jobs and alerts the remaining disk space where our databases live.

12) The next set of options I will be looking into are the ones located in the Connections tab.

Have a look at the picture below

You can set the maximum number of concurrent connections.That means if you set the number to anything other zero,then it is going to limit server resources that can service client connections.Be very careful with that setting.

You can also change the setting “Use query governor to prevent long-running queries”. This means that if we have long-running queries that use all the resources and put huge pressure on SQL Server resources.

We can limit the execution time of every query be checking this option. The default value is 300.That is 300 seconds by the way.That means that if the query does not complete within 300 seconds it will be aborted and will rollback the transaction.

You can also allow remote connections to the server.The default value is 600 seconds, that means 10 minutes.This option is enabled by default in SQL Server 2012 but not in previous versions. Do enable this option if you need it. Do not change the default value.

13) The next set of options I will be looking into are the ones located in the Database Settings tab.

Have a look at the picture below

The first option is the setting for the index fill factor.This specifies the amount of empty space that SQL Server leaves when an index is rebuild or created.This setting should be low (this is a percentage value) for databases that data does not change much and higher in OLTP databases where data is constantly updated,inserted or deleted.

We also have the option to use compressed backups.

Backup compression is available in the SQL Server 2008 R2 Standard edition . When we compress backups we save backup space and also reduce backup times.Obviously we will need more CPU resources to perform compression during a backup or decompression during a restore.One thing to keep in mind is that if we have databases that use data compression or are encrypted, backup compression will not be very efficient. Backup compression does not use row-level or page-level data compression. Backup compression happens at the time of a backup, and it uses its own compression algorithms.

We can also set the  “Default backup media retention” option.This option specifies the length of time to retain each backup set.

You can change this setting using T-SQL.

In a new query window type and execute the following statement

USE AdventureWorks2012 ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'media retention', 90 ;
GO
RECONFIGURE;
GO

Another option we can set is Recovery Interval. This option sets the maximum number of minutes for a database that Microsoft SQL Server needs to recover databases. The default value is 0, which lets SQL Server to automatically configure the recovery interval.

We can also change the Data Default locations option if we choose to.I do not see any reason why you want to change that.This is a setting that you change without fear that you will break something.

14) The next set of options I will be looking into are the ones located in the Advanced tab.

Have a look at the picture below

There are many options we can set in this tab. One of them is “Allow triggers to fire others“.This option is on by default. What actually means is that a trigger will allow to fire another trigger.Sometimes if we are not careful with that option we can get in an infinite loop. Another very important setting is the Optimize for Ad hoc Workloads.You can find more about this very useful option here.

Another option is Max Degree of Parallelism  and sets the number of processors used for the execution of a query with a parallel plan.

For best practices and recommendations on Max Degree of Parallelism have a look at this link.

Bear in mind that these are advanced settings and I would not touch them after the initial installation of SQL Server.

15) The next set of options I will be looking into are the ones located in the Permissions tab.

Have a look at the picture below

In this tab we see a list of principals and their permissions they have on the server level.I have selected the user fofo-pc\Nikos. This user is administrator in Windows. This user/principal is also in the fixed server role of sysadmin.You can see the permissions it has on the server.

Make sure you know exactly what each of the options I mentioned does and only then change them.

Hope it helps!!!