:::: MENU ::::
Browsing posts in: SQL Server

SQL Server 2012 Edition comparisons

 

First off, MS really should have upped the max memory cap beyond 64GB on Standard Edition, RAM is so cheap now that we’re seeing more and more servers with > 132 GB memory. Sadly, they have not.

Information is from page http://msdn.microsoft.com/en-us/library/cc645993.aspx I just needed it a bit compressed and from a DBA perspective.

 

Feature Name Enterprise Business Intelligence Standard Web Express with Advanced Services Express with Tools Express
Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine)1 Operating System maximum Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 1 Socket or 4 cores Limited to lesser of 1 Socket or 4 cores Limited to lesser of 1 Socket or 4 cores
Maximum Compute Capacity Used by a Single Instance (Analysis Services, Reporting Services) 1 Operating system maximum Operating system maximum Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 4 Sockets or 16 cores Limited to lesser of 1 Socket or 4 cores Limited to lesser of 1 Socket or 4 cores Limited to lesser of 1 Socket or 4 cores
Maximum memory utilized (SQL Server Database Engine) Operating system maximum 64 GB 64 GB 64 GB 1 GB 1 GB 1 GB
Maximum memory utilized (Analysis Services) Operating system maximum Operating system maximum 64 GB N/A N/A N/A N/A
Maximum memory utilized (Reporting Services) Operating system maximum Operating system maximum 64 GB 64 GB 4 GB N/A N/A
Maximum relational Database size 524 PB 524 PB 524 PB 524 PB 10 GB 10 GB 10 GB

 

 

Feature Name Enterprise Business Intelligence Standard Web Express with Advanced Services Express with Tools Express
Server Core support1 Yes Yes Yes Yes Yes Yes Yes
Log Shipping Yes Yes Yes Yes
Database mirroring Yes Yes (Safety Full Only) Yes (Safety Full Only) Witness only Witness only Witness only Witness only
Failover Clustering Yes (Node support: Operating system maximum Yes (Node support: 2) Yes (Node support: 2)
Backup compression Yes Yes Yes
Database snapshot Yes
AlwaysOn Availability Groups Yes
SQL Server Multi-Subnet Clustering Yes
Connection Director Yes
Online page and file restore Yes
Online indexing Yes
Online schema change Yes
Fast recovery Yes
Mirrored backups Yes
Hot Add Memory and CPU2 Yes
Database Recovery Advisor Yes Yes Yes Yes Yes Yes Yes

 

Feature Name Enterprise Business Intelligence Standard Web Express with Advanced Services Express with Tools Express
SQL Server change tracking Yes Yes Yes Yes Yes Yes Yes
Merge replication Yes Yes Yes Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
Transactional replication Yes Yes Yes Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
Snapshot replication Yes Yes Yes Yes (Subscriber only Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
Heterogeneous subscribers Yes Yes Yes
Oracle publishing Yes
Peer to Peer transactional replication Yes

 

Report Server Database Server Edition Requirements

When creating a report server database, not all editions of SQL Server can be used to host the database. The following table shows you which editions of the Database Engine you can use for specific editions of Reporting Services.

For this edition of SQL Server Reporting Services Use this edition of the Database Engine instance to host the database
Enterprise Standard, Business Intelligence Enterprise, editions (local or remote)
Business Intelligence Standard, Business Intelligence Enterprise, editions (local or remote)
Standard Standard, Enterprise editions (local or remote)
Web Web edition (local only)
Express with Advanced Services Express with Advanced Services (local only).
Evaluation Evaluation

 

 

Feature Name Enterprise Business Intelligence Standard Web Express with Advanced Services Express with Tools Express
SQL Server Migration Assistant Yes Yes Yes Yes Yes Yes Yes
Database mail Yes Yes Yes Yes

 

 

 

 

 


SQL Server 2012 AlwaysOn backups and checkdb

The other day I read the marketing fluff on the new possibillities to backup from a secondary replica, decided to find out more about it.

After reading up on what it can and can’t do my conclusion is this:

Full backup – Only on primary replica

Full backup with copy_only – Primary and secondary replicas

Differential – Only on primary replica

Log backup – Primary and secondary replicas

So what does this mean then? Well, if diff backups are in your plan now and you don’t want to change it, then stick with full backups and diff backups on primary replica, consider offloading log backups to secondary replica.  Because diff backups can not be based on a full backup with copy_only and a secondary replica can only do full backups with copy_only.

If you’re running full backups and log backups do consider running them on a secondary replica, I’ve tested restoring a full backup with copy_only leaving it with norecovery and then restoring a log backup after and it worked. But do your own testing please!

Also, if you’re backing up from the secondary replica all the time, do some restores every now and then on a testserver and run dbcc checkdb on it. Just to make sure.

Also 2, you can still backup from a secondary replica even if it is not readable. Still only full with copy_only and log backups but it’s better than nothing.

On another note, but related, today I ran a DBCC CheckDB() on a non-readable database, the secondary replica in an Availability Group, setup with synchronous mirroring, and to my coworkers surprise; it worked!

 

So to sum it up: On a secondary replica that is non-readable we can do:

  • Full backup with copy_only
  • log backup
  • dbcc checkdb

 

Source: http://msdn.microsoft.com/en-us/library/hh710053.aspx

//R

 


Creating a dump of SQL Server

Copied from http://blogs.msdn.com/b/askjay/archive/2010/02/05/how-can-i-create-a-dump-of-sql-server.aspx

( Too good not to keep handy :-)   )

 

 

You can create a memory dump of the SQL Server process space in several ways.  There are many external tools that can help you accomplish this such as userdump.exe, debugdiag.exe, and ADPlus.exe.  In this post, I’ll cover 3 common ways to accomplish this for SQL Server:  The most common way (sqldumper), using the debugger, and three methods from within SQL Server itself.

First, two of these methods will require that you get the process id (PID) of SQL Server.   Here are a few simple ways to do that:

1.  From a command window, execute –> tasklist | find /i “sqlservr” or from the Debugging tools directory execute –> tlist | find /i “sqlservr”

2.  Open task manager, and find sqlservr.exe and get the PID from the PID column.  If you don’t see this column, then select View –> Select Columns to add it.

3.  In a query window inside of SSMS on the instance you wish to dump, execute –> “SELECT SERVERPROPERTY(‘PROCESSID’)

4.  Get the process ID from the SQL Server Errorlog.

Now that you have the PID, you can use one of the following ways to create the dump:

Method 1 (Using SqlDumper)

The most common way (and the way used internally by SQL Server) is to run SqlDumper.exe.   You will find SqlDumper.exe in the following directory for a default installation of SQL Server:

C:\Program Files\Microsoft SQL Server\100\Shared

The syntax of SqlDumper is as follows:

SqlDumper <process id (PID)> <thread id (TID)> <Flags:Minidump Flags> <SQLInfoPtr> <Dump Directory>

more parameters can be seen with SqlDumper /?

The common flags are as follows:

0x0120 – Minidump (this is a dump of just the stacks and loaded modules – this is the smallest of the dump types – this is normally the type of dump created automatically by SQL Server during AVs and other exceptions)

0x01100 – Full Dump (this dumps the entire process space – this can be very large on a 64 bit system or any system with a large amount of memory assigned to SQL Server)

0x8100 – Filtered Dump (a filtered dump is a dump of all of Stolen Memory plus certain areas of the buffer pool)

NOTICE:  SQLDumper can be used to dump ANY user mode process – not just SQL Server.

Some examples for SQLServer running under SPID 4024 to c:\temp:

Minidump:  sqldumper 4024 0 0x0120 0 c:\temp
Full Dump:  sqldumper 4024 0 0x01100 0 c:\temp
Filtered Dump:  sqldumper 4024 0 0x8100 0 c:\temp

The dump file will have the naming convention of:   SQLDmpr####.mdmp (i.e.  SQLDmpr0001.mdmp)

Method 2 (Using a debugger)

To dump SQL Server from WINDBG or other debugger, attach the debugger to the process using the PID:

image

Once connected, just use the .dump command which has the following syntax:

Options are:
/a – Create dumps for all processes (requires -u)
/b[a] – Package dump in a CAB and delete dump
/c <comment> – Add a comment (not supported in all formats)
/j <addr> – Provide a JIT_DEBUG_INFO address
/f – Create a legacy style full dump
/m[acdfFhiprRtuw] – Create a minidump (default)
/o – Overwrite any existing file
/u – Append unique identifier to dump name

“.dump /ma” is the recommend method of creating a complete memory dump of a user mode process.

So, to create a complete memory dump of the SQL Server to c:\temp, execute the following:

.dump /ma c:\temp\sqldump.dmp

Method 3 (From within SQL Server)

From inside SQL Server, you can create a dump using two different methods.  First, to create a manual dump immediately, use the following undocumented command:

DBCC STACKDUMP

This will create a memory dump in the LOG directory of your SQL Server instance installation.  To enable this method to create a FULL DUMP, you must turn on trace flags 2544 and 2546:

dbcc traceon(2544, -1)
go

dbcc traceon(2546, -1)
go

dbcc stackdump

To create only a minidump, enable trace flag 2546.  To create a full-filtered dump, use trace flag 2551.

You can use the undocumented DBCC DUMPTRIGGER command to enable SQL Server to create a dump on the occurrence of an error.  You can use the following to enable a full dump on error 802 (There is insufficient memory available in the buffer pool):

— turn on TFs for full dump
dbcc traceon(2544, -1)
go
dbcc traceon(2546, -1)
go

— set DUMP TRIGGER for exception 802
dbcc dumptrigger(‘set’, 802)
go

— view exceptions set for DUMP TRIGGER
dbcc traceon(3604, -1)
go
dbcc dumptrigger(‘display’)
go
dbcc traceoff(3604, -1)
go

— Turn off dumptrigger for exception 802
dbcc dumptrigger(‘clear’, 802)
go

Finally, you can also use the –y parameter on SQL Server startup to achieve the same functionality as SQL Server’s DBCC DUMPTRIGGER.   For more information refer to:

http://blogs.msdn.com/psssql/archive/2008/01/10/how-it-works-sql-server-engine-error-messages.aspx

One method not covered in this post is to create the dump from within Task Manager as seen here:

image


Just a quick note….

Setting up some new SQL Server 2012 AlwaysOn systems, reading through Brent Ozars excellent article on the subject, look here: http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/

Love his humour:

“By default, SQL Server does not accept remote connections. This is because end users are the root of all our problems. While I admire Microsoft’s reluctance to let their product be used, we’re going to have to change that default setting. ”

 

Hahahaha!!!

 

//R



Print all SQL Agent jobs with their schema

Sure, you can just right click on Manage schedules, se picture below, but how do you get a list of all jobs and their schedules that you can just copy paste?

 

We can get that info via a script also, the script is found at the bottom.

The output would be something like this:

Much easier to use.

The script creates a function in msdb which you use when running the select. See below. <disclaimer>And as always, don’t just run something you find on the web without understanding what it does.</disclaimer>

 

USE msdb
GO

--drop function [dbo].[udf_schedule_description]

CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
@freq_interval INT ,
@freq_subday_type INT ,
@freq_subday_interval INT ,
@freq_relative_interval INT ,
@freq_recurrence_factor INT ,
@active_start_date INT ,
@active_end_date INT,
@active_start_time INT ,
@active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description
END

-- Select job information with the help of the function

SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time,
dbo.udf_schedule_description(dbo.sysschedules.freq_type, dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type, dbo.sysschedules.freq_subday_interval, dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor, dbo.sysschedules.active_start_date, dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time, dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

Count records without running a count(*)

Runnin a count(*) actually goes out to the table or index and reads all rows, unnessecary i/o if you don’t have to do it.

Instead you can just use a simple select from sysindexes:

SELECT   rows
FROM     sysindexes WITH (NOLOCK) 
WHERE    id = OBJECT_ID('tablename')
AND      indid < 2

//Richard


Send database restore script and create logins script via mail

A customer is running a huuge number of databases in a single instance, every database has it’s own SQL Server login, need to have these documented in case of a total crash. First method, may be changed later, is to use Databasemail to send us restore database command and create login commands every morning.

So, first set up Databasemail, there are numerous sites to show you how, example http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/  just use your own parameters and get a database profile you can use.

Next, set up a new Agent Job, set it as type T-SQL and copy in something like the below in the Command window, again, change to suit your site, in our case we only want specifik databases, that’s what the line
AND        bs.database_name like ''%YOURDATABASENAME%'' is there for, you could omit it if you want all of them.

bs.type = ‘D’ means only full backups, for diff’s use ‘I’ instead.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail',
@recipients = 'mailadress@domain.com',
@query = 'select name as ''Missing fullbackups within 24 hours'' from sys.databases
where name not in ( select database_name from msdb.dbo.backupset
where (CONVERT(datetime, backup_start_date, 102) >= dateadd(hour, -24, getdate()))
)

select  ''RESTORE DATABASE ['' + bs.database_name + '']
from disk = N'''''' + bmf.physical_device_name + '''''''' +
'' WITH  FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10''
+ CHAR(10) + ''GO'' + CHAR(10)
from    msdb.dbo.backupmediafamily bmf,
msdb.dbo.backupset bs
where   bs.media_set_id = bmf.media_set_id
and        (CONVERT(datetime, bs.backup_start_date, 102) >= dateadd(hour, -24, getdate()))
and        bs.database_name like ''%YOURDATABASENAME%''
and     bs.type = ''D''
order by 1

select ''create login ['' + p.name + ''] '' +
case when p.type in(''U'',''G'') then ''from windows '' else '''' end +
''with '' +
case when p.type = ''S'' then ''password = '' + master.sys.fn_varbintohexstr(l.password_hash) + '' hashed, '' +
''sid = '' + master.sys.fn_varbintohexstr(l.sid) + '', check_policy = '' +
case when l.is_policy_checked > 0 then ''ON, '' else ''OFF, '' end + ''check_expiration = '' +
case when l.is_expiration_checked > 0 then ''ON, '' else ''OFF, '' end +
case when l.credential_id > 0 then ''credential = '' + c.name + '', '' else '''' end
else '''' end +
''default_database = '' + p.default_database_name +
case when len(p.default_language_name) > 0 then '', default_language = '' + p.default_language_name else '''' end
from sys.server_principals p
left join sys.sql_logins l
on  p.principal_id = l.principal_id
left join sys.credentials c
on  l.credential_id = c.credential_id
where p.type in(''S'',''U'',''G'')
and  p.name not like ''builtin%''
order by p.name
',
@subject = 'Scripts to restore databases and create logins',
@attach_query_result_as_file = 1

 

If the resulting mail with attachment is too large then change max size according
to eg. http://stackoverflow.com/questions/997628/how-do-i-increase-the-maximum-allowed-attachment-size-for-email-sent-using-msdb

Watch out for large attachments, couldn’t figure out a way to compress the resulting attachment, in my case they are 5 MB each.

//Richard



Setup mirroring with TSQL

Instance1 is principal, instance2 is mirror. In the example below are they on the same server, thus we need to use different ports setting up endpoints.

1. Principal DB must be in full mode.

2. Backup full and restore to mirror with no recovery

3. Backup tranlog, restore with no recovery

4. Create endpoints:

Instance 2 (mirror):

CREATE ENDPOINT Mirroring
STATE=STARTED AS TCP(LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Instance 1(principal):

CREATE ENDPOINT Mirroring
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

 

5. Alter database Instance2.

ALTER DATABASE databasename SET PARTNER = N’TCP://servername:5022′

EXEC sys.sp_dbmmonitoraddmonitoring

 

6. Alter databaser Instance1.

ALTER DATABASE databasename SET PARTNER = N’TCP://server:5023′

ALTER DATABASE databasename SET SAFETY OFF

EXEC sys.sp_dbmmonitoraddmonitoring — default is 1 minute

 

There, done! Make sure to backup logfiles or the logfile will grow and fill disk, causing server to stop.