Publishing when editor database is on external database server
We have a few databases and servers to consider.
- Editor database server: hosting one SqlServer database for the editor server
- Source database, the editor database, hosted at: DatabaseServerComputer
- Editor server hosting the server application: EditorServerComputer
- Target database: local database at EditorServerComputer created each time a publication is run and filled with data
Requirements
There are some requirement to setup Signifikant to use a separate, external database server computer, for publishing. The publisher copies data from the source database to a target database, the latter is on editor server file system.
The copy process uses a link between the source- and target- databases. Initially the target is empty, it is filled up during the publish process when data is pushed to the target. The target database files are detached at the end, to be freed to be uploaded to a remote web-server.
Link and login
This link is created in the source database using a privileged account, to have all rights to do this.
- Create an SqlServer login in the source database.
- Enable Sysadmin or both Alter any linked server and Alter any login since the login will create a link to the target db
- Add a User Mapping to the master database for the account
- Enter the login/password it in the server configuration file: server.config
Target database
- To enable full access to target databases at the editor server add login
- Add login/password to the editor server SqlServer instance with Sysadmin role
- Use same login/password in source and target to keep things simpler
- Enable Windows and SqlServer login to the target db instance
- Use SQLServerManager15.msc to:
- Enable all protocols for the target instance
- Start the SQL Server Browser service
- Restart the SqlServer instance afterwards when above is changed
Changes in the server.config file
| Note | Description |
|---|---|
| A | This is the address/login to the target database, it is a local database at the editor server. It is used to attach to the database from the external editor database server. |
| B | This is for databases mounted by the manager. The definition could be different to the local database instanse, but this is correct and workes the same. |
| C | Something else, the corresponding file for the Web application need to define a target folder for uploaded publications. |
| D | Editor server uses this to identify where the editor database is, and how to login to it. Note that database files are not defined here, since they are elsewhere. The database must be attached and available at the external server before application server starts up. |
| E | NB! Available in version 5.2 and later. When site/remote publisher is configured to make a copy of editor database to local publish-to database instance, this URI path is used to write a backup from the database server, to a common folder, to be restored on the editor server. The editor data base is copied to the local SqlServer instance to avoid any disturbance, from simultaneous updates in the editor data base. Customers with editing & publishing during day, or import & publishing during off hours may else struggle with aborted publication jobs. |
This is a sample configuration file, it may or may not have parts that are present/absent in your file. So let us consider the marked parts only.
<?xml version="1.0" encoding="utf-16"?>
<ServerConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<PublisherSettings>
<RemoteUploadTryCount>5</RemoteUploadTryCount>
</PublisherSettings>
<SqlServerDatabaseSettings>
A => <PublishToSqlServerLogin>
<UseSqlServerUser>true</UseSqlServerUser>
<SqlServerUser>SqlLoginUserName</SqlServerUser>
<SqlServerPassword>SqlLoginpassword</SqlServerPassword>
</PublishToSqlServerLogin>
<PublishToInstanceName>EditorServerComputer\InstanceName</PublishToInstanceName>
B => <MountAtSqlServerLogin>
<UseSqlServerUser>true</UseSqlServerUser>
<SqlServerUser>SqlLoginUserName</SqlServerUser>
<SqlServerPassword>SqlLoginpassword</SqlServerPassword>
</MountAtSqlServerLogin>
<MountAtInstanceName>EditorServerComputer\InstanceName</MountAtInstanceName>
<DetachDatabaseTimeout>60</DetachDatabaseTimeout>
<AttachDatabaseTimeout>60</AttachDatabaseTimeout>
<DropDatabaseTimeout>60</DropDatabaseTimeout>
<ShrinkDatabaseTimeout>600</ShrinkDatabaseTimeout>
<PublisherCommandTimeout>60</PublisherCommandTimeout>
<CommandTimeout>120</CommandTimeout>
</SqlServerDatabaseSettings>
<LogLevel>Verbose</LogLevel>
C =><RemoteArea>D:\Assert\Data\RemotePublish</RemoteArea>
<Sites>
<AssertSite Id="EditorSite">
<SiteStatus>Online</SiteStatus>
<Name>EditorSite</Name>
<RootPath> C:\Assert\Data\EditorSite</RootPath>
<IsLocalDb>false</IsLocalDb>
D => <SqlServerLogin>
<UseSqlServerUser>true</UseSqlServerUser>
<SqlServerUser>SqlLoginUserName</SqlServerUser>
<SqlServerPassword>SqlLoginpassword</SqlServerPassword>
</SqlServerLogin>
<SqlServerInstance>DatabaseServerComputer</SqlServerInstance>
<Database>EditorDatabase</Database>
<DatabaseFilePath />
<DatabaseLogFilePath />
E => <PublishPublicPath>\\common-server\path\folder</PublishPublicPath>
<ImagePath>C:\Assert\Data\EditorSite\Images</ImagePath>
<DocumentPath>C:\Assert\Data\EditorSite\Documents</DocumentPath>
<ContentSetPath>C:\Assert\Data\EditorSite\ContentSet</ContentSetPath>
<PublicationPath>C:\Assert\Data\EditorSite\Publication</PublicationPath>
<SpoolPath>C:\Assert\Data\EditorSite\Spool</SpoolPath>
<InitialCatalogue>1</InitialCatalogue>
<PublicationDate>0001-01-01T00:00:00</PublicationDate>
<LanguageList />
<DocumentLanguageList />
<UseLoremIpsum>false</UseLoremIpsum>
<IsPublication>false</IsPublication>
</AssertSite>
</Sites>
</ServerConfiguration>
Related articles