Fixing SQL Server 2014 Replication.

31415  crying rage face meme i1

One of the many advantages of Sagittarius being a Microsoft Partner is having access to the latest versions of all their software. The disadvantage is that you have access to all the latest versions!

I thought I would try out the latest version of SQL Server to see what improvements it had. Specifically to see how the Replication worked in SQL Server 2014 (Version 2.0.2000). Unfortunately I quite quickly ran into a bug which is one of the most horrific I've come across. 

The procedure sp_MSdetect_nonlogged_shutdown stores log messages. But in one place it says messages can only be 2048 chars long, in another it says its 1024 chars long. The problem is log messages in this version of SQL Server are up to 4000 chars long! That's in MSDB sysjobhistory for the techies. 

So when you try to start up Replication it instantly errors with a "String will be truncated". So I reported it to Microsoft and thought okay so how can I get around this in the mean time? I figured I could just update the two values to be 4000 and that would be that. 

But Microsoft has tried very hard to make sure normal users don't modify system procedures. I was lucky enough to find this great article about modifying SQL Server system procedures. The short of it is that you have to put the entire SQL Server instance in single user mode. Enable writing of system procedures and then altering the procedure.

This issue is present as of Update 4 so if you need to get replication running use the instructions below. Now its time to do something not code related :-)

Instructions


1. Stop SQL Server service.

2. On command prompt run the following command to run the server in single user mode – be sure to replace the MSSQLSERVER with the actual instance name
C:\>”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” -sMSSQLSERVER -m

3. Connect to SQL server with SSMS as the server administrator

4. Run the following to change the database to the system resource database: “USE mssqlsystemresource”

5. Run the following to update the stored procedure
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [sys].[sp_MSdetect_nonlogged_shutdown]
(
    @subsystem nvarchar(60),
    @agent_id int
)
as
begin
    declare @job_id binary(16)
    declare @agent_name sysname
    declare @message nvarchar(4000)
    declare @retcode int
    declare @runstatus int
    declare @run_date int
    declare @run_time int
    declare @run_date_orig int
    declare @run_time_orig int
    declare @merge_session_id int
    
    --
    -- security check
    -- only db_owner can execute this
    --
    if (is_member ('db_owner') != 1) 
    begin
        raiserror(14260, 16, -1)
        return (1)
    end

    -- Detect if the agent was shutdown without a logged reason
    if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'SNAPSHOT'
    begin
        if exists (select runstatus from MSsnapshot_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC       runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSsnapshot_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSsnapshot_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'LOGREADER'
    begin
        if exists (select runstatus from MSlogreader_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSlogreader_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSlogreader_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'DISTRIBUTION'
    begin
        if exists (select runstatus from MSdistribution_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSdistribution_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSdistribution_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'MERGE'
    begin
        if exists (select runstatus from dbo.MSmerge_sessions where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            session_id = (select top 1 session_id from dbo.MSmerge_sessions where agent_id = @agent_id order by session_id desc))
            begin
                select @job_id = job_id, @agent_name = name from dbo.MSmerge_agents where id = @agent_id
                
                select top 1 @merge_session_id = session_id from dbo.MSmerge_sessions 
where agent_id = @agent_id 
order by session_id desc
            end
    end
    else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'QUEUEREADER'
    begin
        if exists (select runstatus from MSqreader_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC       runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSqreader_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSqreader_agents where id = @agent_id
            end
    end

    -- If no job_id assume shutdown was logged properly
    if @job_id is null
        return 0

    -- Get last message from SQL Agent History table
    create table #JobHistory (
        instance_id int NOT NULL, 
        job_id uniqueidentifier NOT NULL,
        job_name sysname NOT NULL,
        step_id int NOT NULL,
        step_name nvarchar(100) NOT NULL, 
        sql_message_id int NOT NULL,
        sql_severity int NOT NULL,
        message nvarchar(4000) NOT NULL,
        run_status int NOT NULL,
        run_date int NOT NULL,
        run_time int NOT NULL,
        run_duration int NOT NULL,
        operator_emailed sysname NULL,
        operator_netsent sysname NULL,
        operator_paged sysname NULL,
        retries_attempted int NOT NULL,
        server sysname NOT NULL
    )
    if @@error <> 0
        return 1

    -- Insert last history for step_id 2 (Agent running)
    insert TOP(2) into #JobHistory exec sys.sp_MSreplhelp_jobhistory @job_id = @job_id, @step_id = 2, 
        @mode = 'FULL'          


declare cursorHistory cursor local fast_forward for
    select message, 
    run_status,
    run_date,
    run_time
    from #JobHistory
    order by run_date desc, 
    run_time desc, 
    instance_id asc
    
    open cursorHistory

    fetch cursorHistory into @message, @runstatus, @run_date, @run_time

    select @run_date_orig = @run_date, 
  @run_time_orig = @run_time
  
    while @@fetch_status <> -1
    begin   
    -- as long as we are looking at the history for the same run 
    -- date and time then we should log all rows. there should 
    -- be 2 rows since we perform a TOP on exec sp_help_jobhistory
if @run_date_orig = @run_date
  and @run_time_orig = @run_time
begin
   -- Map SQL Agent runstatus to Replication runstatus
   set @runstatus = 
   case @runstatus
       when 0 then 6   -- Fail mapping
       when 1 then 2   -- Success mapping
       when 2 then 5   -- Retry mapping
       when 3 then 2   -- Shutdown mapping
       when 4 then 3   -- Inprogress mapping
       when 5 then 0   -- Unknown is mapped to never run
   end

   -- If no message, provide a default message
-- Also overwrite all inprogress messages to be "See SQL Agent history log".
-- This is to prevent "Agent running. See monitor" to be logged into repl monitor.
-- In this case (the last job history message is InProgress), we know that
-- there have been failures of SQL Server Agent history logging.
-- In fact, the only possible "in progress" msg in SQL Agent job step
-- history for push jobs is "Agent running. See monitor". It is confusing that those
-- messages showed up in repl monitor.
   if @message is null or @runstatus = 3
   begin
       raiserror(20557, 10, -1, @agent_name)
       select @message = formatmessage(20557, @agent_name)
   end

   if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'SNAPSHOT'
       exec @retcode = sys.sp_MSadd_snapshot_history @agent_id = @agent_id, @runstatus = @runstatus,
               @comments = @message
   else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'LOGREADER'
       exec @retcode = sys.sp_MSadd_logreader_history @agent_id = @agent_id, @runstatus = @runstatus,
               @comments = @message
   else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'DISTRIBUTION'
       exec @retcode = sys.sp_MSadd_distribution_history @agent_id = @agent_id, @runstatus = @runstatus,
               @comments = @message
   else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'MERGE'
exec @retcode = sys.sp_MSadd_merge_history @agent_id = @agent_id, @runstatus = @runstatus,
               @comments = @message, @called_by_nonlogged_shutdown_detection_agent = 1, @session_id_override = @merge_session_id
   else if UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'QUEUEREADER'
       exec @retcode = sys.sp_MSadd_qreader_history @agent_id = @agent_id, @runstatus = @runstatus,
               @comments = @message

   if @@error <> 0 or @retcode <> 0
       return 1
end

fetch cursorHistory into @message, @runstatus, @run_date, @run_time
end

close cursorHistory
deallocate cursorHistory

    drop table #JobHistory
end

6. Return the system resource database to read-only – “alter database mssqlsystemresource set read_only”

7. Shutdown the instance – “shutdown”

8. Start the instance using the service as usual




 

 

 

Whatever your business, be it a regional or global brand, the content you produce plays a vital role in your success. You know that… hence you’re reading this.

A well formulated and executed content strategy not only drives more traffic, at the core, it defines what your business is and helps build a strong connection between you and your audiences.

So let's quickly look at why developing a coherent content strategy is important and how setting clear goals and understanding your audience will elevate your online performance. 

What is a Content Strategy?

It's basic right? Content is at the core of how you define the way your business presents itself and an effective strategy should look to ensure that tone of voice, messaging and the core values are surfaced across all channels, from service or product pages on your website, to blog posts, through social media updates blah blah blah.

But let's keep it simple - your content strategy should be a clear roadmap that connects your marketing activities to your business goals. Align to your customer’s wants and needs and engage them at every interaction point and boom, you're in business. 

Who are my Audience?

You likely start all your projects with this chalked on the wall because your business knows “exactly” who its customers are right? Sounds obvious but we often find its not been done forensically enough (not based on data), is too old (more than 12 months ago - forget it) or its a spin off from some brand work that was legitimately aspirational but doesn’t face the reality of who you your business is actually engaging today.

So start (or circle back) with audience research, building out those personas to understand their ambitions, their lifestyle, their pain points or concerns, and crucially their wants and needs - in your context. 

Do I need to tailor content?

As part of your research find out where your audiences spend their time online and how they interact with content: Some may spend time thoroughly researching a product or service, whereas other audiences may want their content to be quick, snappy or easily digestible in the form of a video, infographic or short blog posts.

 

Ultimately, the key is to produce a strategy that creates the type of content your customers want to see:

  • What are the problems that your product or service will help them solve?

  • Who are they most influenced by?

  • What voices influence their behaviour?

  • What type of content do they consume?

  • Where do they consume content and engage with brands?

Different Content, Different Objectives

 All content is not born equal: When producing your strategy, it is important that the objectives for each individual piece are defined, that these fulfil your marketing objectives and tie to the overarching goals for your business.

There are various content frameworks that exist to aid content development in this way, but one that is popular and effective is Google’s hero, hub and hygiene method: It provides a framework on developing content to achieve different goals and gives guidance on the effort needed to create each type of content.

Hero Content

Hero content is essentially campaign content, it is big splash ideas designed to appeal to a large audience with the aim of telling your brand’s story at scale. 

Ways of measuring hero include the amount of PR mentions or links from authoritative domains plus social interactions and mentions of your brand across all channels. 

Considering the scale of hero campaigns, this content is not regularly produced and is reserved for peak promotional times where it’s important for a business to stand out from their competitors.

Hub Content

Hub content is the stuff that keeps your audience engaged, it expands on the themes of product or service level content, educates users and helps create a connection between themselves and your brand.

Hygiene Content

Hygiene content is the bread and butter of any website, it is the BAU content for products and services, it is SEO focused and targets important keywords at a product, service or guide level.

How do I manage all this?

Content development is only one part of the ongoing work needed when working with an effective content strategy. We call it “feeding the beast” because it really is the fuel in your brand vehicle and once you start you really can’t stop (if it’s delivering results) but that’s where performance measurement comes in.

Your greatest gift in managing the outputs from your hero/hub/hygiene style efforts is to understand If your content is working. To truly deliver results your business must first understand the objectives and goals of each piece of content to effectively measure its success. That as a guiding light from day 1 will let you slow down, speed up, stop or start new content briefs and projects.

Remember - content strategies are not set in stone. They are living breathing things and should adapt and pivot as insights become available and your brand naturally evolves.

If ever you want to chat content and explore new initiatives we’re always here to help.

want to speak to one of our experts?

 
Richard Brisley
Richard Brisley
Tech Lead
Richard is the longest standing member of the Sagittarius team, he works tirelessly to support the development and side-facing team with problem solving and pitches alike. His skills as a .NET programmer and database administrator have been paramount to the success of Sagittarius and our continued success. 

In 2016 and again in 2019 Richard was recognise in the BIMA 100 awards for his outstanding work in Tech, his passion for digital and his contribution to the industry. 

Richard Brisley

Richard Brisley

19 Nov 2014 - 5 minute read
share this

stay in the know, stay ahead.

Get the latest from the agency, including news, events and expert content.
explore services in the article
find out what we can do for you
read some of our case studies