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.

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'

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.

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'

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.

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.

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;

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.

Friday, 29 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 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

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. Collapse this tableExpand this table

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