Undoubtedly, many of us have the task of installing a new stand-alone SQL Server instance which includes the database engine service only. For instance, it can primarily be needed for dedicated and consolidated OLTP environments. Consequently, we can be asked to create a formal document for others so that they can easily follow it for future installations and standard configurations.
Today's post is going to outline the process of installing a basic stand-alone SQL Server 2017 instance. This process is just a basic guideline and, surely, not a rule for each installation, because it is fully understood that every environment is different and needs a customised installation to meet very specific requirements. You can read the whole tip about it at mssqltips here https://www.mssqltips.com/sqlservertip/5616/steps-to-install-a-standalone-sql-server-2017-instance. I hope you find it very useful and practical. That's all for now. Please let me know any remarks you may have. Stay tuned!
Monday, 30 July 2018
Tuesday, 20 March 2018
Configuring Read-Only Routing and load-balancing across Read-Only replicas
With the arrival of AlwaysOn Availability Group in SQL Server 2012, implementing HA+DR solutions have been an easier and not expensive task in comparison to legacy architectures such as Database Mirroring for HA and Log Shipping for DR, and FCI for HA and Database Mirroring for DR. Nevertheless, at the beginning not everyone has been fully aware of all the power of this technology so that some might not have made the most out of it. Naturally, this technology has been improved over the years, for instance, load-balancing across readable secondary replicas was added, and today in this post, I am coming with a script to configure it.
Saturday, 17 February 2018
Quickly Checking for Stale Statistics in SQL Server
Monitoring the state of database statistics is crucial because they significantly influence overall performance. When statistics become outdated, performance degradation is inevitable, often leading to slow and inefficient systems—something nobody wants. This raises several important questions:
- How often have you faced unforeseen performance issues, even after completing your regular database maintenance tasks?
- Have you wondered why tasks like index rebuilding and statistics updates are sometimes insufficient?
- If everything was running smoothly before, why has performance suddenly declined?
- Why are indexes not being utilised as expected? Should more indexes be created?
The answer to many of these questions often lies in stale statistics. It's essential to remember that statistics contain vital information that the SQL Optimiser uses to generate optimal execution plans for queries. If these statistics are outdated, the impact on performance can be severe. Simply having the right indexes isn’t enough if the statistics are stale.
Tuesday, 13 February 2018
Avoid changing default ANSI database options
Not having another way of fixing some specific errors, at times some people may consider turning off any ANSI database options as a final solution. To be perfectly honest, I do recommend getting to the bottom of each problem and then fixing it at that level instead of changing default ANSI settings (unless it is just a quick fix or is truly necessary because you verified the benefits are significant). For instance, it is by no means uncommon to turn ANSI_WARRINGS off to fix the following error:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
Sunday, 21 January 2018
How to make uniform all the collations of table columns for all databases
Clearly, it is of paramount importance to standardise the collations for all databases in a SQL Server instance in order to avoid dealing with unforeseen conflicts of page code compatibility. We may find ourselves in complicated situations because databases with different collations were migrated from other environments and the new consolidated environment was not prepared to host those new databases, but in one way or another we may need to consolidate them in only one server. It may be compounded by the fact that there may be many character columns of a database using different collations and another collation at database level. What’s more, the tempdb database may be using another different collation. So, it may turn out to be not only a complex issue but also time-consuming.
To give you just an example, we can start finding out what character columns are using different collations from SQL_Latin1_General_CP1_CI_AS that we need to change in order to make everything uniform. I am going to display a useful script to do it. In this example I am assuming that we want to use SQL_Latin1_General_CP1_CI_AS for all objects in the database server.
After that,we can make the decision of changing all character columns for all databases and use SQL_Latin1_General_CP1_CI_AS, and then make that change at database level. Be cautious and make sure as well you have tempdb’s collation set SQL_Latin1_General_CP1_CI_AS.
It is worth
noting that while running the script above some errors may arise because of some indexes might be using one of the columns we are trying to alter. So, in this likely event, it is recommendable to drop those indexes and then run the script again. That is all for the time being. Let me know any remarks you may have.
To give you just an example, we can start finding out what character columns are using different collations from SQL_Latin1_General_CP1_CI_AS that we need to change in order to make everything uniform. I am going to display a useful script to do it. In this example I am assuming that we want to use SQL_Latin1_General_CP1_CI_AS for all objects in the database server.
EXEC sp_MSforeachdb ' USE [?] select db_name(),c.name from sys.columns c inner join sys.types t on t.user_type_id= c.user_type_id inner join sys.tables tb on c.object_id=tb.object_id where c.collation_name is not null and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams'' and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS'' order by tb.name, c.column_id'
EXEC sp_MSforeachdb ' USE [?] if db_name() not in (''master'',''tempdb'',''msdb'',''model'') begin select replace( REPLACE( ''ALTER TABLE '' + QUOTENAME(SCHEMA_NAME(tb.schema_id)) + ''.'' + QUOTENAME(tb.name) + '' ALTER COLUMN '' + QUOTENAME(c.name) + '' '' + QUOTENAME(t.name) + ''('' + CAST( case when T.NAME=''NVARCHAR'' THEN c.max_length/2 WHEN T.NAME=''NCHAR'' THEN c.max_length/2 ELSE c.max_length END AS VARCHAR(10)) +'')'' + '' COLLATE SQL_Latin1_General_CP1_CI_AS'' + CASE WHEN c.is_nullable =1 THEN '' NULL '' else '' NOT NULL ;'' END, ''-1'', ''MAX'' ), ''[text](16)'', ''[varchar](max)'') as cmd INTO #TblTMP from sys.columns c inner join sys.types t on t.user_type_id= c.user_type_id inner join sys.tables tb on c.object_id=tb.object_id where c.collation_name is not null and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams'' and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS'' order by tb.name, c.column_id declare @cmd varchar(max) declare c_cmd cursor for select cmd from #TblTMP open c_cmd fetch next from c_cmd into @cmd while (@@fetch_status=0) begin exec( @cmd) fetch next from c_cmd into @cmd end close c_cmd deallocate c_cmd drop table #TblTMP end'
Friday, 12 January 2018
MSSQL_ENG003165: An error was encountered while replication was being restored/removed. The database has been left offline
While restoring a replicated database without KEEP_REPLICATION option, SQL Server will remove replication settings by executing sp_restoredbreplication at the end of the process. The 'sp_restoredbreplication' system stored procedure will delete all replication metadata, that is, deletion of 'tr_MStran_alterschemaonly', 'tr_MStran_altertable', 'tr_MStran_altertrigger' and 'tr_MStran_alterview' tiggers (which were created to validate alterations on the replication of tables, triggers, views), disable user tables for replication, and deletion of subscription/publications/articles. Nevertheless, there might be some cases where 'sp_restoredbreplication' cannot be executed successfully and ends up leaving the database OFFLINE. I personally experienced that case and the error was something like this:
Msg 3165, Level 16, State 1, Line 1
Database ‘MyDB’ was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database ‘MyDB’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Looking into this case, I could see that the cause was a DDL database trigger which existed inside the database. Let me expand on what I am saying. The database had that trigger to audit some schema changes which were supposed to save into an auditing table. Unfortunately, that auditing table did not exist in the server where the database was being restored, and the deletion of objects of replication settings were not completed, which means that 'sp_restoredbreplication' was not executed correctly. Consequently, the restoration was stopped and SQL Server decided to leave the database OFFLINE.
In order to restore a copy of this database, we need to disable all DDL database triggers before taking its backup. Only then will the database be restored successfully. The other method to deal with this issue is to change the status to ONLINE manually after the restoration finishes unsuccessfully and also execute 'sp_restoredbreplication'.
To sum up, we need to proceed with more cautiousness while working with databases linked to replication. That is all for now. Let me know any remarks you may have. Thanks for reading. Stay tuned.
Msg 3165, Level 16, State 1, Line 1
Database ‘MyDB’ was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database ‘MyDB’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Looking into this case, I could see that the cause was a DDL database trigger which existed inside the database. Let me expand on what I am saying. The database had that trigger to audit some schema changes which were supposed to save into an auditing table. Unfortunately, that auditing table did not exist in the server where the database was being restored, and the deletion of objects of replication settings were not completed, which means that 'sp_restoredbreplication' was not executed correctly. Consequently, the restoration was stopped and SQL Server decided to leave the database OFFLINE.
In order to restore a copy of this database, we need to disable all DDL database triggers before taking its backup. Only then will the database be restored successfully. The other method to deal with this issue is to change the status to ONLINE manually after the restoration finishes unsuccessfully and also execute 'sp_restoredbreplication'.
To sum up, we need to proceed with more cautiousness while working with databases linked to replication. That is all for now. Let me know any remarks you may have. Thanks for reading. Stay tuned.
Thursday, 4 January 2018
Table-valued user-defined functions and the database collation
When it comes to altering database collations we may face some problems that tend to slow us down at the beginning. Nevertheless, digging into the message errors we might not spot the causes easily. For instance, while executing the following script to change the collation at database level an error may arise informing that some objects depends on it and it is not possible to make that change. Here is the script.
As far as we know collations are heavily linked to character columns, and it includes columns of table-valued user-defined functions as they may have character columns on its definition. When theses functions are created they inherit the database collation by design for their columns. Here the error message:
Msg 5075, Level 16, State 1, Line 1
The object 'TVFUserTable' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'MyDBUser' cannot be set to SQL_Latin1_General_CP1_CI_AS.
What we just need to do to be able to change the collation at database level is firstly drop every schema-bound objects, then make the change and finally create the objects again. That is all for now. Let me know any remarks you may have.
ALTER DATABASE MyDBUser SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE MyDBUser COLLATE SQL_Latin1_General_CP1_CI_AS; ALTER DATABASE MyDBUser SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Msg 5075, Level 16, State 1, Line 1
The object 'TVFUserTable' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'MyDBUser' cannot be set to SQL_Latin1_General_CP1_CI_AS.
What we just need to do to be able to change the collation at database level is firstly drop every schema-bound objects, then make the change and finally create the objects again. That is all for now. Let me know any remarks you may have.
Saturday, 30 December 2017
Memory + CPU support for all Windows Server versions and editions
Some weeks ago I outlined my Memory + CPU support compact brief for SQL Server, and now it is time for Windows Server. Undoubtedly, when we are sizing a database server not only take a look at the hardware support for SQL Server but also for Windows Server. Here is the info you might need to have it at hand and make a good use of it.
If any of you want to get closer to related topics you can check out Windows Server – Sockets, Logical Processors, Symmetric Multi Threading and Windows Server scalability and more!
Physical Memory Limits: Windows Server 2012 (R2)
The following table specifies the limits on physical memory for Windows Server 2012. Windows Server 2012 is available only in X64 editions.
Physical Memory Limits: Windows Server 2016
The following table specifies the limits on physical memory for Windows Server 2016.
Version
|
Memory
|
CPU
|
Windows Server 2016 Datacenter
|
24
TB
|
64 CPU. Unlimited cores
|
Windows Server 2016 Standard
|
24
TB
|
64 CPU. Unlimited cores
|
Physical Memory Limits: Windows Server 2012 (R2)
The following table specifies the limits on physical memory for Windows Server 2012. Windows Server 2012 is available only in X64 editions.
Version
|
Memory
|
CPU
|
Windows
Server 2012 Datacenter
|
4 TB
|
64
CPU or 640 logical processors (or 320 with Hyper-V Role)
|
Windows
Server 2012 Standard
|
4 TB
|
64
CPU or 640 logical processors (or 320 with Hyper-V Role)
|
Windows
Server 2012 Essentials
|
64 GB
|
2
CPU
|
Windows
Server 2012 Foundation
|
32 GB
|
1
CPU
|
Windows
Storage Server 2012 Workgroup
|
32 GB
|
|
Windows
Storage Server 2012 Standard
|
4 TB
|
|
Hyper-V
Server 2012
|
4 TB
|
Physical Memory Limits: Windows Server 2008 R2
The following table specifies the limits on physical memory for Windows Server 2008 R2. Windows Server 2008 R2 is available only in 64-bit editions. Windows Server 2008 R2 for Itanium-Based Systems support up to 2TB.
Version
|
Memory
|
CPU
|
Windows
Server 2008 R2 Datacenter
|
2 TB
|
64
CPU or 256 logical
processors (or 64 with Hyper-V Role )
|
Windows
Server 2008 R2 Enterprise
|
2 TB
|
8
CPU or 256 logical
processors (or 64 with Hyper-V Role )
|
Windows
Server 2008 R2 Foundation
|
8 GB
|
|
Windows
Server 2008 R2 Standard
|
32 GB
|
4
CPÛ or 256 logical
processors (or 64 with Hyper-V Role )
|
Windows
HPC Server 2008 R2
|
128 GB
|
|
Windows
Web Server 2008 R2
|
32 GB
|
4
CPÛ or 256 logical
processors (or 64 with Hyper-V Role )
|
Physical Memory Limits: Windows Server 2008
The following table specifies the limits on physical
memory for Windows Server 2008. Limits greater than 4 GB for 32-bit
Windows assume that PAE is enabled.
Memory
|
CPU
|
|||
Version
|
X86
|
X64
|
x86 (SP2)
|
x64 (SP2)
|
Windows
Server 2008 Datacenter
|
64 GB
|
1 TB
|
32
CPU or 32 logical processors
|
32
CPU or 64 logical processors (or 24 with Hyper-V)
|
Windows
Server 2008 Enterprise
|
64 GB
|
1 TB
|
8
CPU or 32 logical processors
|
8
CPU or 64 logical processors(or 24 with Hyper-V)
|
Windows
Server 2008 HPC Edition
|
128GB
|
|||
Windows
Server 2008 Standard
|
4 GB
|
32 GB
|
4
CPU or 32 logical processors
|
4
CPU or 64 logical processors(or 24 with Hyper-V)
|
Windows
Small Business Server 2008
|
4 GB
|
32 GB
|
||
Windows
Web Server 2008
|
4 GB
|
32 GB
|
||
Windows Server 2008 for
Itanium-Based Systems support up to 2TB
SP1+ Hyper-V Enabled support up to 16 logical processors
SP1+ Hyper-V Enabled + KB956710 installed support up to 24 logical processors
SP1+ Hyper-V Enabled support up to 16 logical processors
SP1+ Hyper-V Enabled + KB956710 installed support up to 24 logical processors
Physical Memory Limits: Windows Home Server
Windows Home Server is available only in a 32-bit
edition. The physical memory limit is 4 GB.
Physical Memory Limits: Windows Server 2003 R2
The following table specifies the limits on physical
memory for Windows Server 2003 R2. Limits over 4 GB for 32-bit
Windows assume that PAE is enabled.
Version
|
Limit on X86
|
Limit on X64
|
Windows Server 2003 R2 Datacenter Edition
|
64 GB (16 GB with 4GT)
|
1 TB
|
Windows Server 2003 R2 Enterprise Edition
|
64 GB (16 GB with 4GT)
|
1 TB
|
Windows Server 2003 R2 Standard Edition
|
4 GB
|
32 GB
|
Physical Memory Limits: Windows Server 2003 with Service Pack 2 (SP2)
The following table specifies the limits on physical
memory for Windows Server 2003 with Service Pack 2 (SP2). Limits over
4 GB for 32-bit Windows assume that PAE is enabled.
Version
|
X86
|
X64
|
IA64
|
Windows Server 2003 with Service Pack 2 (SP2),
Datacenter Edition
|
64 GB(16 GB with 4GT)
|
1 TB
|
2 TB
|
Windows Server 2003 with Service Pack 2 (SP2),
Enterprise Edition
|
64 GB(16 GB with 4GT)
|
1 TB
|
2 TB
|
Windows Server 2003 with Service Pack 2 (SP2), Standard
Edition
|
4 GB
|
32 GB
|
Physical Memory Limits: Windows Server 2003 with Service Pack 1 (SP1)
The following table specifies the limits on physical
memory for Windows Server 2003 with Service Pack 1 (SP1). Limits over
4 GB for 32-bit Windows assume that PAE is enabled.
Version
|
X86
|
X64
|
IA64
|
Windows Server 2003 with Service Pack 1 (SP1),
Datacenter Edition
|
64 GB(16 GB with 4GT)
|
1 TB
|
1 TB
|
Windows Server 2003 with Service Pack 1 (SP1),
Enterprise Edition
|
64 GB(16 GB with 4GT)
|
1 TB
|
1 TB
|
Windows Server 2003 with Service Pack 1 (SP1), Standard
Edition
|
4 GB
|
32 GB
|
Physical Memory Limits: Windows Server 2003
The following table specifies the limits on physical
memory for Windows Server 2003. Limits over 4 GB for 32-bit Windows assume
that PAE is enabled.
Version
|
X86
|
IA64
|
Windows Server 2003, Datacenter Edition
|
64 GB(16 GB with 4GT)
|
512 GB
|
Windows Server 2003, Enterprise Edition
|
64 GB(16 GB with 4GT)
|
512 GB
|
Windows Server 2003, Standard Edition
|
4 GB
|
|
Windows Server 2003, Web Edition
|
2 GB
|
|
Windows Small Business Server 2003
|
4 GB
|
|
Windows Compute Cluster Server 2003
|
32 GB
|
|
Windows Storage Server 2003, Enterprise Edition
|
8 GB
|
|
Windows Storage Server 2003
|
4 GB
|
Windows Server 2003: The number of processors and the amount of physical RAM that are supported
The following table compares the number of processors and the
amount of physical RAM that are supported by the x64-based versions of Windows Server
2003 and by Windows XP Professional x64 Edition to those that are supported by
the 32-bit versions.
Operating system
|
Number of processors
|
Physical RAM
|
Microsoft Windows Server 2003, Standard Edition
|
4
|
4 gigabytes (GB)
|
Microsoft Windows Server 2003, Standard x64 Edition
|
4
|
32 GB
|
Microsoft Windows Server 2003, Enterprise Edition
|
8
|
64 GB
|
Microsoft Windows Server 2003, Enterprise x64 Edition
|
8
|
1 terabyte
|
Microsoft Windows Server 2003, Datacenter Edition
|
32
|
64 GB
|
Microsoft Windows Server 2003, Datacenter x64 Edition
|
64
|
1 terabyte
|
Microsoft Windows XP Professional
|
2
|
4 GB
|
Microsoft Windows XP Professional x64 Edition
|
2
|
128 GB
|
Notes
·
x86-based versions of
Windows Server 2003 that are running on a computer that uses a multicore
processor or a hyper-threading processor support a maximum number of 32 logical
processors.
·
x64-based versions of
Windows Server 2003 that are running on a computer that uses a multicore
processor or a hyper-threading processor support a maximum number of 64 logical
processors.
Memory
allocation settings
The following table compares the memory allocation settings that
are supported by the x64-based versions of Windows Server 2003 and Windows XP
Professional x64 Edition to those that are supported by the 32-bit versions.
Memory allocation settings
|
32-bit versions
|
x64-based versions
|
Total amount of virtual address space
|
4 GB
|
16 terabytes
|
Amount of virtual address space per 32-bit process
|
2 GB (3 GB if the /3GB switch is added to the Boot.ini
file)
|
2 GB (4 GB if the /LARGEADDRESSAWARE option is used)
|
Amount of virtual address space for the 64-bit processes
|
Not applicable
|
8 terabytes
|
Amount of paged pool memory
|
470 megabytes (MB)
|
128 GB
|
Amount of non-paged pool memory
|
256 MB
|
128 GB
|
Size of system cache
|
1 GB
|
1 terabyte
|
Physical Memory Limits: Windows 8
The following table specifies the limits on physical
memory for Windows 8.
Version
|
Limit on X86
|
Limit on X64
|
Windows 8
Enterprise
|
4 GB
|
512 GB
|
Windows 8
Professional
|
4 GB
|
512 GB
|
Windows 8
|
4 GB
|
128 GB
|
Physical Memory Limits: Windows 7
The following table specifies the limits on physical
memory for Windows 7.
Version
|
Limit on X86
|
Limit on X64
|
Windows 7
Ultimate
|
4 GB
|
192 GB
|
Windows 7
Enterprise
|
4 GB
|
192 GB
|
Windows 7
Professional
|
4 GB
|
192 GB
|
Windows 7
Home Premium
|
4 GB
|
16 GB
|
Windows 7
Home Basic
|
4 GB
|
8 GB
|
Windows 7
Starter
|
2 GB
|
N/A
|
Physical
Memory Limits: Windows Vista
The following table specifies the limits on physical
memory for Windows Vista.
Version
|
Limit on X86
|
Limit on X64
|
Windows Vista
Ultimate
|
4 GB
|
128 GB
|
Windows Vista
Enterprise
|
4 GB
|
128 GB
|
Windows Vista
Business
|
4 GB
|
128 GB
|
Windows Vista
Home Premium
|
4 GB
|
16 GB
|
Windows Vista
Home Basic
|
4 GB
|
8 GB
|
Windows Vista
Starter
|
1 GB
|
Physical Memory Limits: Windows XP
The following table specifies the limits on physical
memory for Windows XP.
Version
|
Limit on X86
|
Limit on X64
|
Limit on IA64
|
Windows XP
|
4 GB
|
128 GB
|
128 GB (not supported)
|
Windows XP
Starter Edition
|
512 MB
|
N/A
|
N/A
|