none
cannot create distribution or find local publications RRS feed

  • General discussion

  • I am using sql server 2014 express and follwoing the tutorials from Microsoft. I tried to right click replications I can't find configure distribution under there, all I see is "Launch replication monitor", "generate scripts", "update replication passwords", "new" which only leads to "local subscriptions", "reports" and "refresh". I also don't see local publications if I expand replications. The tutorials didin't guide me to create a publisher so when it came time to create a distributor I back tracked and went to "launch replication monitor" and then add publisher but I'm getting an error telling me my server is neither a publisher nor a distributor or I don't have permission to access replication functionality on this server. I went into permissions by right clicking on the instance and going to properties but don't see what to do there. Any suggestions?

    Tuesday, April 23, 2019 2:59 PM

All replies

  • Hi,

    SQL Express can only be used as a subscriber for replication, so you wont be able to set this as a distributor:

    https://docs.microsoft.com/en-us/sql/getting-started/features-supported-by-the-editions-of-sql-server-2014?view=sql-server-2014#Replication

    Let me know if I've misunderstood?

    Thanks,
    Matt

    Tuesday, April 23, 2019 3:41 PM
  • SQL Express cannot fulfil the distributor role. You will need to install a SQL Standard Edition or above and Create your distributor there. Your SQL Express server can be a subscriber only.
    Tuesday, April 23, 2019 3:41 PM
    Moderator
  • I just want to preface this by saying i'm very new to sql server but my question is that to perform a master slave synchronization across two separate servers can I use express edition. Will subscriber only replication work for this?

    Tuesday, April 23, 2019 4:47 PM
  • You will need one of your servers to be standard edition. Then you can do transactional replication which is the equivalent of a master/slave replication.
    Tuesday, April 23, 2019 5:23 PM
    Moderator
  • SQL replication requires a publisher (this is the server that sends the data) and a subscriber (the server that receives the data).  There is also a distributor role but this is typically on either the publisher or subscriber (I usually use the publisher but it doesnt really matter).

    The publisher has to be running on SQL Standard or higher, however the subscriber can be Web or Express edition.

    I hope that makes sense?

    Thanks,
    Matt

     
    Tuesday, April 23, 2019 5:24 PM
  • I have one more question if I'm sitting in a server room next to a physical server in a production facilty can I use that as my "publisher" and use my express edition server as my subscriber? The thing is though I want to know if I can disable that production server as a publisher afterwards if that's possible?
    Tuesday, April 23, 2019 6:48 PM
  • Yes, but if you only want a one time sync, snapshot replication could be a better fit, or even bcp.
    Tuesday, April 23, 2019 6:51 PM
    Moderator
  • Yeah you can remove it as a publisher as that is just removing the replication.
    Tuesday, April 23, 2019 6:53 PM
  • Part of my concern is when the connection is not established though is the sql server going to freak out or anything when my laptop is not physically connected to the server?
    Tuesday, April 23, 2019 9:32 PM
  • How come you're using a laptop for the subscriber? If this isnt replicating to another server then what is the replication for?

    When the subscriber is disconnected then the publisher will log errors to say that but it will still work. Best to remove replication fully when you're done to stop the errors

    Tuesday, April 23, 2019 10:11 PM
  • I'm doing this as proof of concept for the project in regards to their recipes that reside on the customer server. I just want to make sure the production environment server will continue to work as intended I just wanted to be absolutely sure
    Wednesday, April 24, 2019 2:53 PM
  • Ah OK.  I've not really seen any degradation in performance when using trans replication.  One of the downsides though is that if you make schema changes then you need to take a fresh snapshot of data for the changes to be reflected.

    Wednesday, April 24, 2019 3:07 PM
  • You don't always need a snapshot when you do a schema change.  If you do, it should only be for the tables which have changed.
    Wednesday, April 24, 2019 3:11 PM
    Moderator
  • Snapshot replication would be the best option then right? I just need to replicate one table or tables but not the entire server
    Wednesday, April 24, 2019 4:57 PM
  • It depends on how in-sync the replica needs to be.  Snapshot replication does not send transactional changes - it just refreshes the snapshot on intervals.  Transactional Replication will send transactional changes to the subscriber too.
    Wednesday, April 24, 2019 4:59 PM
  • Can you please explain to me what you mean by transactional changes?
    Wednesday, April 24, 2019 6:45 PM
  • Sure.  By transactional changes, I just mean updates to the database. 

    Snapshot replication takes a snapshot of the database and copies it in its entirety on a schedule.  So in-between snapshot replication intervals, no data is transferred from the publisher to the subscriber.

    Transactional Replication takes an initial snapshot of the database and then will also replicate changes too.  So if you want the data on the subscriber to be relatively up to date then you should use transactional replication

    Snapshot replication also locks tables during the entire snapshot process and so this can cause disruption to service - transactional replication does this for the intitial snapshot but tables are not locked when it sends transactional changes.

    Let me know if you have any further questions.

    Thanks,

    Matt

    Wednesday, April 24, 2019 6:56 PM
  • Sorry I have another question regarding connecting. So I'm sitting in a server room and connected to the server by plugging in via ethernet to the switch. My question is as a beginner I don't know how I pull up the relevant server databases from here? In other words what do next? Are there any good tutorials you can share with me? 
    Thursday, April 25, 2019 1:31 PM
  • If you know the SQL Server name use SSMS (SQL Server Management Studio) to gain access to the SQL Server and then use this tutorial to create your publication. 

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-2017

    Thursday, April 25, 2019 2:06 PM
    Moderator
  • I'm now connected to the sql server here at the facility by plugging into the switch and I am following through the tutorial as it pertains to preparing the server for replication. I came across this and I found that on the server here at the facility this filepath doesn't have a data folder.I have to create a folder called to repldata at this filepath to share the snapshot folder according to the tutorial. How important is this can I just create it at MSSQL even though there is no data folder in there?

    The filepath is C:/programfiles/Microsoft SQL Server/MSSQL.X/MSSQL/Data

    Thursday, April 25, 2019 9:52 PM
  • No, this folder should have been created when you created the distributor.

    I suggest you create a folder somewhere else on your server and not within the program files directory which is a protected space.

    sp_helpdistributor will tell you where your default snapshot folder is. You can also use this.

    Wednesday, May 1, 2019 6:16 PM
    Moderator