Tag: Bug

SQL

Enabling IFI on Setup in SQL Server 2016

SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

Continue reading “Enabling IFI on Setup in SQL Server 2016” →

AlwaysOn, Availability Groups, Bug, SQL

BUG with Availability Groups and sys.master_files

I recently came across a bug with SQL Server and Availability Groups whereby catalog view data is incorrectly reported on all secondary replicas.

This bug has the potential for putting the availability of your environment at risk as reporting around capacity could be calculated incorrectly.

Continue reading “BUG with Availability Groups and sys.master_files” →

SQL

SSMS Results: A Cautionary Tale

As a person who messes with SQL Server there is a pretty strong probability that you are using SQL Server Management Studio (SSMS). It is a very full featured tool, but does have a couple of problems and quirks (like crashing on large result sets due to it reaching the maximum memory allocation for a 32bit process).

The other day I came across another quirk, maybe even a bug, which can happen when returning and working with XML results, which could lead you to copy incorrect data…

Continue reading “SSMS Results: A Cautionary Tale” →

SQL 2012

SQL Server 2012 Data Loss Bug

Microsoft have just posted a hotfix for a very serious bug in SQL Server 2012. 

This bug could potentially cause you to lose data during online index rebuilds.

 

At this time I would not recommend downloading the hotfix (wait for a CU which will address the problem). In the meantime follow the posted workaround which is to perform online index rebuilds with a MAXDOP of 1. For example:

 

ALTER INDEX <idxname> ON <objectname> REBUILD WITH (ONLINE=ON, MAXDOP=1);

 

SQL

SSMS – Index Outside The Bounds Of An Array

Hit a strange issue this morning. I was working with one of the network guys testing out connecting to SQL behind an F5 load balancer, something I’ve done multiple times previously.

I was able to connect using SQLCMD, Invoke-SQLCmd and using a UDL source, but for some reason, every time I tried to connect using SSMS I would get an error stating that an “Index was outside the bounds of an array”.

SSMS+-+Index+out+of+bounds+on+an+array
Advanced Information for Error

A little research showed that this error cropped up when trying to connect to SQL 2012 from an earlier version of SSMS. This wasn’t the case here though, everything was SQL 2012, and I was able to connect to the server directly without any problems, it was only an error when going through the F5 VIP.

After a little work and research with the network admin we discovered that OneConnect was causing the issue. It was attempting to pool connections and was causing the problem, turning it off fixed the issue entirely.

Just something to keep an eye out for if you use F5’s to handle load balancing or DR client redirection.

Rant, SQL, SSRS, Windows

Windows Hotfix KB 2661254 Breaks Reporting Services

I have spent the last 3 weeks trying to troubleshoot an issue with Reporting Services for SQL Server 2008 R2 Service Pack 2 failing to start on a server and have come to discover that a Windows Hotfix is causing the issue.

There is no distinction between trying to install a slipstreamed version of SQL Server 2008 R2 with SP2 or trying to install SQL Server 2008 R2 and then attempting to apply SP2 on top of it, either way if KB 2661254 is installed the Reporting Services service will fail to start. You will not get an error indicating the reason for the failure, just that it failed (way to go with the pertinent error messages there Microsoft).

The Windows hotfix KB 2661254 is an update for the certificate minimum key length to prevent the use of any certificate keys that are less than 1024 bit long. This is a security measure to help prevent brute force attacks against private keys. Why this breaks SSRS I do not know. The patch can be safely applied to systems running SQL Server 2008 R2 SP1. 

For now I have passed along word to the sysadmins to not deploy this particular patch to any Windows machine that runs SQL Server and have created a Microsoft Connect item in a hope that they provide resolution to the issue. Please try this in your own test environment, then upvote and mark that you are able to reproduce the problem on Connect.

Replication, SQL

Replication–Beware Reuse of View Names When Filtering Articles

I came across a very strange issue with replication this weekend.

As a part of a release a new table was being created with extra columns and a view was created with the old table name that referenced the old column set (so as not to break old legacy applications). This table was replicated. The dev team provided us with scripts to setup replication. During the attempt to perform the snapshot I ran into some problems and so dropped the publication.

All of a sudden users started to complain that they could no longer see the view and couldn’t query the data. I looked, and sure enough the view was not there. Very strange. I told the users that I’d get it created again once replication was done. I redid the publication and the users called me saying not to worry, the view was back.

What the heck?

After digging through I found the cause of the issue. The dev had created the replication scripts and included a filter to eliminate certain columns. This replication then gets wrapped into an article view. In this case the dev had manually named the @view_name in sp_articleview to be the same as the view in the database. This caused SQL to drop the existing view and create it’s own. When the publication was dropped so was the view.

 

Here are the steps to repro this behavior:

Firstly I’m creating a view against the local table SQLMonitor.dbo.SQLBuilds

CREATE VIEW Bananas

AS

    SELECT  Build ,

            [KB Article] ,

            URL ,

            Description ,

            MajorBuild ,

            MinorBuild

    FROM    SQLMonitor.dbo.SQLBuilds

A quick select shows data:

image

 

Now let’s create a publication for replication with column filters against the SQLBuilds table. We’ll specify the @view_name to be Bananas. I’m also including an @filter_clause to eliminate rows (I’ll explain that in a bit).

-- Enabling the replication database

USE master

EXEC sp_replicationdboption @dbname = N'SQLMonitor', @optname = N'publish',

    @value = N'true'

GO

 

EXEC [SQLMonitor].sys.sp_addlogreader_agent @job_login = NULL,

    @job_password = NULL, @publisher_security_mode = 1

GO

EXEC [SQLMonitor].sys.sp_addqreader_agent @job_login = NULL,

    @job_password = NULL, @frompublisher = 1

GO

-- Adding the transactional publication

USE [SQLMonitor]

EXEC sp_addpublication @publication = N'Bananas Test',

    @description = N'Transactional publication of database ''SQLMonitor'' from Publisher ''local''.',

    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true',

    @allow_pull = N'true', @allow_anonymous = N'false',

    @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',

    @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',

    @allow_subscription_copy = N'false', @add_to_active_directory = N'false',

    @repl_freq = N'continuous', @status = N'active',

    @independent_agent = N'true', @immediate_sync = N'false',

    @allow_sync_tran = N'false', @autogen_sync_procs = N'false',

    @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,

    @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',

    @enabled_for_het_sub = N'false'

GO

 

 

EXEC sp_addpublication_snapshot @publication = N'Bananas Test',

    @frequency_type = 1, @frequency_interval = 0,

    @frequency_relative_interval = 0, @frequency_recurrence_factor = 0,

    @frequency_subday = 0, @frequency_subday_interval = 0,

    @active_start_time_of_day = 0, @active_end_time_of_day = 235959,

    @active_start_date = 0, @active_end_date = 0, @job_login = NULL,

    @job_password = NULL, @publisher_security_mode = 1

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'sa'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT AUTHORITYSYSTEM'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT SERVICESQLSERVERAGENT'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT SERVICEMSSQLSERVER'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'distributor_admin'

GO

 

-- Adding the transactional articles

USE [SQLMonitor]

EXEC sp_addarticle @publication = N'Bananas Test', @article = N'SQLBuilds',

    @source_owner = N'dbo', @source_object = N'SQLBuilds', @type = N'logbased',

    @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',

    @schema_option = 0x000000000803509F,

    @identityrangemanagementoption = N'none',

    @destination_table = N'SQLBuilds', @destination_owner = N'dbo',

    @status = 24, @vertical_partition = N'true',

    @ins_cmd = N'CALL [sp_MSins_dboSQLBuilds]',

    @del_cmd = N'CALL [sp_MSdel_dboSQLBuilds]',

    @upd_cmd = N'SCALL [sp_MSupd_dboSQLBuilds]'

 

-- Adding the article's partition column(s)

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'Build', @operation = N'add', @force_invalidate_snapshot = 1,

    @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'KB Article', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'MajorBuild', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'MinorBuild', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

 

-- Adding the article synchronization object

EXEC sp_articleview @publication = N'Bananas Test', @article = N'SQLBuilds',

    @view_name = N'Bananas', @filter_clause = N'[KB Article] >0',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

GO

 

This does it’s job, creates the publication with the article and everything. Now let’s query the view again…

image

 

Several columns are now missing and the view matches what’s specified in the replication script. The old view has been dropped and a new system view has been created:

image

 

 

Now dropping the publication also drops the view

-- Dropping the transactional articles

USE [SQLMonitor]

EXEC sp_dropsubscription @publication = N'Bananas Test',

    @article = N'SQLBuilds', @subscriber = N'all', @destination_db = N'all'

GO

USE [SQLMonitor]

EXEC sp_droparticle @publication = N'Bananas Test', @article = N'SQLBuilds',

    @force_invalidate_snapshot = 1

GO

 

-- Dropping the transactional publication

USE [SQLMonitor]

EXEC sp_droppublication @publication = N'Bananas Test'

GO

 

image

 

This appears to be a bug and not the behavior I would expect.

If I recreate the view and then attempt to create the publication again, this time without the @filter_clause I get an error stating the view already exists:

USE [SQLMonitor]

EXEC sp_addpublication @publication = N'Bananas Test',

    @description = N'Transactional publication of database ''SQLMonitor'' from Publisher ''local''.',

    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true',

    @allow_pull = N'true', @allow_anonymous = N'false',

    @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',

    @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',

    @allow_subscription_copy = N'false', @add_to_active_directory = N'false',

    @repl_freq = N'continuous', @status = N'active',

    @independent_agent = N'true', @immediate_sync = N'false',

    @allow_sync_tran = N'false', @autogen_sync_procs = N'false',

    @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,

    @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',

    @enabled_for_het_sub = N'false'

GO

 

 

EXEC sp_addpublication_snapshot @publication = N'Bananas Test',

    @frequency_type = 1, @frequency_interval = 0,

    @frequency_relative_interval = 0, @frequency_recurrence_factor = 0,

    @frequency_subday = 0, @frequency_subday_interval = 0,

    @active_start_time_of_day = 0, @active_end_time_of_day = 235959,

    @active_start_date = 0, @active_end_date = 0, @job_login = NULL,

    @job_password = NULL, @publisher_security_mode = 1

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'sa'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT AUTHORITYSYSTEM'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT SERVICESQLSERVERAGENT'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'NT SERVICEMSSQLSERVER'

GO

EXEC sp_grant_publication_access @publication = N'Bananas Test',

    @login = N'distributor_admin'

GO

 

-- Adding the transactional articles

USE [SQLMonitor]

EXEC sp_addarticle @publication = N'Bananas Test', @article = N'SQLBuilds',

    @source_owner = N'dbo', @source_object = N'SQLBuilds', @type = N'logbased',

    @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',

    @schema_option = 0x000000000803509F,

    @identityrangemanagementoption = N'none',

    @destination_table = N'SQLBuilds', @destination_owner = N'dbo',

    @status = 24, @vertical_partition = N'true',

    @ins_cmd = N'CALL [sp_MSins_dboSQLBuilds]',

    @del_cmd = N'CALL [sp_MSdel_dboSQLBuilds]',

    @upd_cmd = N'SCALL [sp_MSupd_dboSQLBuilds]'

 

-- Adding the article's partition column(s)

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'Build', @operation = N'add', @force_invalidate_snapshot = 1,

    @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'KB Article', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'MajorBuild', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',

    @column = N'MinorBuild', @operation = N'add',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

 

-- Adding the article synchronization object

EXEC sp_articleview @publication = N'Bananas Test', @article = N'SQLBuilds',

    @view_name = N'Bananas', @filter_clause = N'',

    @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

GO

 

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 3: There is already an object named ‘Bananas’ in the database.
Msg 21745, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 272
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the WHERE clause of a SELECT statement to produce a valid query.
Msg 3931, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 401
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

 

Something to be aware of if you are going to be playing with replication.

I’ve opened a Connect bug with Microsoft to try and get this fixed. Please go vote on it. And be careful with your replication.