Friday, May 2, 2008
WOS Forum / Please add Deki Wiki to the install packages
WOS Forum / Please add Deki Wiki to the install packages
I have Deki-Wiki running within WOS on Windows XP. I hope this can help to create a package (if possible and if you like to).
This is what I did to get it running:
* Installed a fresh copy of WOS with Apache, PHP5, MySQL and ImageMagick.
* Sort of followed installation http://wiki.opengarden.org/User:PeteE/W … on_(Hayes), below is what I did:
1) Edit MySQL my.ini to checked that NO_AUTO_CREATE_USER was not in the sql-mode setting:
# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
* It is not by default in WOS.
2) Edit PHP5\php.ini and set the following values: short_open_tag = On
* It is by default in WOS.
3) Enabled cURL by uncommenting extension=php_curl.dll in the php.ini file.
4) For cURL to be able to be enabled the dll's libeay32.dll and ssleay32.dll need to be copied to the C:\WINDOWS\system32\ folder.
(* Please see remarks/questions at the end)
*Update* A better solution for the cURL files has been found: Copy the dll's libeay32.dll and ssleay32.dll to the \wos\apache2\bin\ folder.
5) Downloaded Deki Wiki Hayes and extracted it. Copied the deki-hayes-files\web folder to WOS\www\web.
6) Copied file mono.posix.dll from deki-hayes-files\src\redist folder to WOS\www\web\bin.
7) Edited httpd.conf file to check that PHP5 is enabled:
LoadModule php5_module "D:/WOS/php5/php5apache2.dll"
* It is by default in WOS.
Added the follwong lines:
DocumentRoot "D:/WOS/www/web"
ServerName localhost
RewriteEngine On
RewriteCond %{REQUEST_URI} ^/$
RewriteRule ^/$ /index.php?title= [L,NE]
RewriteCond %{REQUEST_URI} !/(@api|editor|skins|config)/
RewriteCond %{REQUEST_URI} !/(redirect|texvc|index|Version).php
RewriteCond %{REQUEST_URI} !/error/(40(1|3|4)|500).html
RewriteCond %{REQUEST_URI} !/favicon.ico
RewriteCond %{REQUEST_URI} !/robots.txt
RewriteCond %{REQUEST_URI} !/dummy.php
RewriteCond %{REQUEST_URI} !/phpinfo.php
RewriteCond %{QUERY_STRING} ^$ [OR] %{REQUEST_URI} ^/Special:Search
RewriteRule ^/(.*)$ /index.php?title=$1 [L,QSA,NE]
ProxyPass /@api http://localhost:8081 retry=1
ProxyPassReverse /@api http://localhost:8081
SetEnv force-proxy-request-1.0 1
SetEnv proxy-nokeepalive 1
AllowEncodedSlashes On
8) Enable the following additional modules in httpd.conf:
LoadModule rewrite_module modules/mod_rewrite.so (* already loaded by default in WOS)
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_http_module modules/mod_proxy_http.so
9) Started WOS
10) Opend http://localhost/web/config/index.php in my browser.
11) Filled in the form:
Left the Database Superuser password empty.
Added the path for ImageMagick convert and ImageMagick identify:
WOS\ImageMagick\convert.exe
WOS\ImageMagick\identify.exe
12) Clicked Install Deki Wiki!
* Installation went successful!
13) The following line is displayed "Please run the following commands manually from the command line to complete your installation:"
cd D:\WOS\www\web\config
mkdir C:\dekiwiki
copy mindtouch.deki.startup.xml C:\dekiwiki
copy LocalSettings.php D:\WOS\www\web\
copy AdminSettings.php D:\WOS\www\web\
copy mindtouch.host.bat D:\WOS\www\web\bin\
cd D:\WOS\www\web\bin
mindtouch.host.bat
* Handled it a bit different and did the following:
13a) cd D:\WOS\www\web\config
13b) mkdir D:\WOS\dekiwiki
13c) edit mindtouch.deki.startup.xml and changed
c:\dekiwiki\luceneindex
into
d:\WOS\dekiwiki\luceneindex
13d) edit mindtouch.host.bat and changed
C:\dekiwiki\mindtouch.deki.startup.xml
into
D:\WOS\dekiwiki\mindtouch.deki.startup.xml
13e) copy mindtouch.deki.startup.xml D:\WOS\dekiwiki
13f) copy LocalSettings.php D:\WOS\www\web\
13g) copy AdminSettings.php D:\WOS\www\web\
13h) cd D:\WOS\www\web\bin
13i) mindtouch.host.bat
* mindtouch.host.bat needs to keep running.
14) Opened http://localhost/
* DekiWiki (Hayes) is running.
Remarks/Questions:
* It seems that the WOS path is only in the files mindtouch.deki.startup.xml and mindtouch.host.bat and in mysql tables mysql\data\mysql\tables_priv.MYI and mysql\data\wikidb\config.MYD (both found doing a text search not a querie on the tables. Not sure what it is used for).
* It seems that the path for ImageMagick is only in mindtouch.deki.startup.xml.
* mindtouch.host.bat needs to keep running, how do you get the apache and MySQL cmd boxes to vanish after startup of WOS? I do not like it that a cmd box needs to keep on running in my taskbar. Is it possible to do the same with the mindtouch.host.bat cmd box?
* I was not able to get mindtouch.host.bat run from the option "Start this external app"? How can this be done?
* Is there a way to get the dll's libeay32.dll and ssleay32.dll loaded from the WOS\php5 folder so they do not have to be copied to the C:\WINDOWS\system32\ folder?
* I do not know what to do with the VirtualHost part to get Deki-Wiki to run next to other web sites/applications. What should I do to accomplisch this?
I have Deki-Wiki running within WOS on Windows XP. I hope this can help to create a package (if possible and if you like to).
This is what I did to get it running:
* Installed a fresh copy of WOS with Apache, PHP5, MySQL and ImageMagick.
* Sort of followed installation http://wiki.opengarden.org/User:PeteE/W … on_(Hayes), below is what I did:
1) Edit MySQL my.ini to checked that NO_AUTO_CREATE_USER was not in the sql-mode setting:
# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
* It is not by default in WOS.
2) Edit PHP5\php.ini and set the following values: short_open_tag = On
* It is by default in WOS.
3) Enabled cURL by uncommenting extension=php_curl.dll in the php.ini file.
4) For cURL to be able to be enabled the dll's libeay32.dll and ssleay32.dll need to be copied to the C:\WINDOWS\system32\ folder.
(* Please see remarks/questions at the end)
*Update* A better solution for the cURL files has been found: Copy the dll's libeay32.dll and ssleay32.dll to the \wos\apache2\bin\ folder.
5) Downloaded Deki Wiki Hayes and extracted it. Copied the deki-hayes-files\web folder to WOS\www\web.
6) Copied file mono.posix.dll from deki-hayes-files\src\redist folder to WOS\www\web\bin.
7) Edited httpd.conf file to check that PHP5 is enabled:
LoadModule php5_module "D:/WOS/php5/php5apache2.dll"
* It is by default in WOS.
Added the follwong lines:
DocumentRoot "D:/WOS/www/web"
ServerName localhost
RewriteEngine On
RewriteCond %{REQUEST_URI} ^/$
RewriteRule ^/$ /index.php?title= [L,NE]
RewriteCond %{REQUEST_URI} !/(@api|editor|skins|config)/
RewriteCond %{REQUEST_URI} !/(redirect|texvc|index|Version).php
RewriteCond %{REQUEST_URI} !/error/(40(1|3|4)|500).html
RewriteCond %{REQUEST_URI} !/favicon.ico
RewriteCond %{REQUEST_URI} !/robots.txt
RewriteCond %{REQUEST_URI} !/dummy.php
RewriteCond %{REQUEST_URI} !/phpinfo.php
RewriteCond %{QUERY_STRING} ^$ [OR] %{REQUEST_URI} ^/Special:Search
RewriteRule ^/(.*)$ /index.php?title=$1 [L,QSA,NE]
ProxyPass /@api http://localhost:8081 retry=1
ProxyPassReverse /@api http://localhost:8081
SetEnv force-proxy-request-1.0 1
SetEnv proxy-nokeepalive 1
AllowEncodedSlashes On
8) Enable the following additional modules in httpd.conf:
LoadModule rewrite_module modules/mod_rewrite.so (* already loaded by default in WOS)
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_http_module modules/mod_proxy_http.so
9) Started WOS
10) Opend http://localhost/web/config/index.php in my browser.
11) Filled in the form:
Left the Database Superuser password empty.
Added the path for ImageMagick convert and ImageMagick identify:
WOS\ImageMagick\convert.exe
WOS\ImageMagick\identify.exe
12) Clicked Install Deki Wiki!
* Installation went successful!
13) The following line is displayed "Please run the following commands manually from the command line to complete your installation:"
cd D:\WOS\www\web\config
mkdir C:\dekiwiki
copy mindtouch.deki.startup.xml C:\dekiwiki
copy LocalSettings.php D:\WOS\www\web\
copy AdminSettings.php D:\WOS\www\web\
copy mindtouch.host.bat D:\WOS\www\web\bin\
cd D:\WOS\www\web\bin
mindtouch.host.bat
* Handled it a bit different and did the following:
13a) cd D:\WOS\www\web\config
13b) mkdir D:\WOS\dekiwiki
13c) edit mindtouch.deki.startup.xml and changed
into
13d) edit mindtouch.host.bat and changed
C:\dekiwiki\mindtouch.deki.startup.xml
into
D:\WOS\dekiwiki\mindtouch.deki.startup.xml
13e) copy mindtouch.deki.startup.xml D:\WOS\dekiwiki
13f) copy LocalSettings.php D:\WOS\www\web\
13g) copy AdminSettings.php D:\WOS\www\web\
13h) cd D:\WOS\www\web\bin
13i) mindtouch.host.bat
* mindtouch.host.bat needs to keep running.
14) Opened http://localhost/
* DekiWiki (Hayes) is running.
Remarks/Questions:
* It seems that the WOS path is only in the files mindtouch.deki.startup.xml and mindtouch.host.bat and in mysql tables mysql\data\mysql\tables_priv.MYI and mysql\data\wikidb\config.MYD (both found doing a text search not a querie on the tables. Not sure what it is used for).
* It seems that the path for ImageMagick is only in mindtouch.deki.startup.xml.
* mindtouch.host.bat needs to keep running, how do you get the apache and MySQL cmd boxes to vanish after startup of WOS? I do not like it that a cmd box needs to keep on running in my taskbar. Is it possible to do the same with the mindtouch.host.bat cmd box?
* I was not able to get mindtouch.host.bat run from the option "Start this external app"? How can this be done?
* Is there a way to get the dll's libeay32.dll and ssleay32.dll loaded from the WOS\php5 folder so they do not have to be copied to the C:\WINDOWS\system32\ folder?
* I do not know what to do with the VirtualHost part to get Deki-Wiki to run next to other web sites/applications. What should I do to accomplisch this?
Friday, April 18, 2008
The Intelligent Enterprise Blog: Competing on Decisions, by Neil Raden
The Intelligent Enterprise Blog: Competing on Decisions, by Neil Raden
BI and Technology: Part II
Technology Is Not the Driver of BI Adoption
Posted Friday, March 28, 2008
10:16 AM
>>Comments
Posted Monday, July 16, 2007
12:27 AM
>>Comments
Who Defines BI? Part II
Posted Monday, April 16, 2007
8:57 AM
>>Comments
Who Defines BI?
Posted Tuesday, April 3, 2007
9:01 AM
>>Comments
BI and Technology: Part II
Thanks to all of you who responded to my last post with thoughtful comments. Rather than respond to each in a Lincoln-Douglas style debate, as Kurt Schlegel suggested, let me shift the discussion a little. Instead of arguing that technology alone can't move BI along, I'd rather explore the issue of what can.
To be effective, BI has to focus on simplicity of operation to achieve pervasiveness in the organization and beyond it. The model is the Consumer Web, which provides only the necessary presentation to perform the tasks at hand, and relies on open standards and loosely coupled services to perform the functions, which can be reconfigured dynamically. In the same way the users of the Consumer Web are willing to pay little or nothing directly (except for purchases), the cost of BI has to drop drastically from expensive, front-loaded perpetual licenses to pay-as-you-go on demand schemes.
>>Continue reading "BI and Technology: Part II"
Technology Is Not the Driver of BI Adoption
I'm having some problems with a March 20, 2008 article titled "Gartner: Emerging Technologies Will Help Drive Mainstream BI Adoption." This has been the Holy Grail of BI vendors for over a decade — to increase the number of "seats" using their products, widely reported to be about 20 percent of an organization but clearly much less than that. What troubles me the most about this article, or rather, about Gartner's analysis, is the supposition that new technology is going to crack this old chestnut. It won't. There are only two pieces of enterprise analytical software (broadly speaking) that ever gained currency in organizations in the past two decades — Excel and Google. Wouldn't it be a good idea to understand why?
>>Continue reading "Technology Is Not the Driver of BI Adoption"
Posted Friday, March 28, 2008
10:16 AM
>>Comments
Get Real About Operational BI There is a lot of conflicting information about the term “Operational BI.” We need some research to sort out the jargon and propose a clear definition for the term (I’m willing). All of the following are being positioned as Operational BI (not a complete list):
• Data warehousing of operational data for reporting, with or without integration
• Replication of operational data for reporting
• Direct reporting from operational systems
• Federated reporting from operational systems
• Process Intelligence
• Inline integration
• Sensing applications
• Decision services
• Real-time BI
>>Continue reading "Get Real About Operational BI"
Posted Monday, July 16, 2007
12:27 AM
>>Comments
Who Defines BI? Part II
These are extended responses to comments made in the original blog "Who Defines BI?"
Cliff Longman, CTO at Kalido, commented: "I think of BI as the car and data warehousing as the engine…Data warehouses should represent the historical view (and the "what if?" views as well if it is a business requirement) of data that a business relies on to judge its performance."
Cliff, we're pretty much in agreement. I think what we have is a problem of semantics (what a coincidence). We need to separate the data warehouse from data warehousing, which I think you did. The data warehouse is a repository of re-used information with historical context. Its use, going forward, will be diminished to some uncertain degree by advances in technology. It will not go away, at least not anytime soon. I have no quarrel with the data warehouse as a data source for reporting and analysis, but not as THE source.
>>Continue reading "Who Defines BI? Part II"
Posted Monday, April 16, 2007
8:57 AM
>>Comments
Who Defines BI?
I was more than a little surprised when I read the article "Think Critically When Applying Best Practices," by Bob Becker and Ralph Kimball. Unless I misread it, they have come around and defined BI as the total process, including data warehousing. This is something that the other prominent data warehousing guru's did a few years ago when, fearing they would miss the boat of the suddenly hot BI market, declared their IT-oriented data warehouse environment as BI. The fallacy in this is that the people who use BI were always conspicuously absent from the diagrams and descriptions of the data warehouse. Their architecture blueprints depicted "users" (and keep in mind that there are only two industries that call their customers users) as little stick figures crushed under the weight of their elegant, multi-colored architectures, or through demeaning models with names such as "Farmers."
>>Continue reading "Who Defines BI?"
Posted Tuesday, April 3, 2007
9:01 AM
>>Comments
Thursday, April 17, 2008
ITtoolbox - Professional IT Community
ITtoolbox - Professional IT Community
Browse ITtoolbox
CRM
Data Management
Development and Integration
Enterprise Back Office
IT Management and Trends
Networking and Infrastructure
CRM Siebel
Business Intelligence Database Data Warehouse Knowledge Management Oracle
C Languages EAI Java Visual Basic Web Design
Baan ERP PeopleSoft SAP Supply Chain
CIO Emerging Technologies Project Management
Linux Networking Security Storage UNIX
Hardware Windows Wireless
Blogs Blog or comment on real IT issues and trends.
Groups Ask and answer questions among skilled peers.
Wiki Create and edit definitions, FAQs, and HOWTOs.
Vendor Research Directory Evaluate products and services with your peers.
Members You can find members by running a search or using the alphabetical directory below.
A - B - C - D - E - F - G - H - I - J - K - L - M - N - O - P - Q - R - S - T - U - V - W - X - Y - Z
...and moreIncluding webcasts and events, job postings and alerts & newsletters.
Advertise Post a Job
Browse ITtoolbox
CRM
Data Management
Development and Integration
Enterprise Back Office
IT Management and Trends
Networking and Infrastructure
CRM Siebel
Business Intelligence Database Data Warehouse Knowledge Management Oracle
C Languages EAI Java Visual Basic Web Design
Baan ERP PeopleSoft SAP Supply Chain
CIO Emerging Technologies Project Management
Linux Networking Security Storage UNIX
Hardware Windows Wireless
Blogs Blog or comment on real IT issues and trends.
Groups Ask and answer questions among skilled peers.
Wiki Create and edit definitions, FAQs, and HOWTOs.
Vendor Research Directory Evaluate products and services with your peers.
Members You can find members by running a search or using the alphabetical directory below.
A - B - C - D - E - F - G - H - I - J - K - L - M - N - O - P - Q - R - S - T - U - V - W - X - Y - Z
...and moreIncluding webcasts and events, job postings and alerts & newsletters.
Advertise Post a Job
eLife Coupler - 856 x12 conversion | x12 Translation | EDI X12 mapping | ebXML and AS2 Messaging
eLife Coupler - 856 x12 conversion x12 Translation EDI X12 mapping ebXML and AS2 Messaging
Coupler Enterprise ?
A comprehensive suite of eBusiness Tools for Business Process Management, Workflow Automation, Data Integration, and Secure B2B communication. It is simply the easiest way to build and deploy eBusiness projects in a record time and without a single line of code.
Learn more >>
Coupler Schemas
EDI 204 Motor Carrier Load Tender Transaction
EDI 990 Response to a Load Tender
EDI 214 Transportation Carrier Shipment status
EDI 210 Motor Carrier Freight Details and Invoice
EDI 856 Advance Ship Notice/Manifest
Coupler WebClient ?/font>
A remote application that allows a partner to connect to a server partner and perform web services including C2B or B2B, and remote access to tracking information.
Learn more >>
Coupler ebXML Pack
A package that includes the basic components of Coupler to create a business workflow, an ebXML Adapter, and a Coupler Tracker to track your inbound/outbound documents. Price : $995.
Learn more >>
Coupler AS2 Pack
A package that includes the basic components of Coupler to create a business workflow, an AS2 Adapter, and a Coupler Tracker to track your inbound/outbound documents. Price : $995: Learn more >>
Coupler Translator Packs
A package that includes the basic components of Coupler to create a business workflow, & a Coupler Mapper to map one of the following specific formats
Coupler Enterprise ?
A comprehensive suite of eBusiness Tools for Business Process Management, Workflow Automation, Data Integration, and Secure B2B communication. It is simply the easiest way to build and deploy eBusiness projects in a record time and without a single line of code.
Learn more >>
Coupler Schemas
EDI 204 Motor Carrier Load Tender Transaction
EDI 990 Response to a Load Tender
EDI 214 Transportation Carrier Shipment status
EDI 210 Motor Carrier Freight Details and Invoice
EDI 856 Advance Ship Notice/Manifest
Coupler WebClient ?/font>
A remote application that allows a partner to connect to a server partner and perform web services including C2B or B2B, and remote access to tracking information.
Learn more >>
Coupler ebXML Pack
A package that includes the basic components of Coupler to create a business workflow, an ebXML Adapter, and a Coupler Tracker to track your inbound/outbound documents. Price : $995.
Learn more >>
Coupler AS2 Pack
A package that includes the basic components of Coupler to create a business workflow, an AS2 Adapter, and a Coupler Tracker to track your inbound/outbound documents. Price : $995: Learn more >>
Coupler Translator Packs
A package that includes the basic components of Coupler to create a business workflow, & a Coupler Mapper to map one of the following specific formats
Monday, April 7, 2008
Managing identity columns with replication in SQL Server
MICROSOFT SQL SERVER
Managing identity columns with replication in SQL ServerBaya Pavliashvili08.03.2007Rating: -4.50- (out of 5)
Expert advice on database administration
Digg This! StumbleUpon Del.icio.us
ttWriteMboxDiv('searchSQLServer_Tip_Content_Body');
ttWriteMboxContent('searchSQLServer_Tip_Content_Body');
function writeLink() {
var link = "http://mbox5.offermatica.com/m2/techtarget/ubox/page" +
"?mbox=dice" + "&mboxSession=" + mboxFactoryDefault.getSessionId().getId() +
"&mboxPC=" + mboxFactoryDefault.getPCId().getId() + "&mboxXDomain=disabled" +
"&mboxDefault=";
var domain = document.domain;
var diceLink = "http://ad.doubleclick.net/clk;179435819;24234612;k?http://seeker.dice.com/jobsearch/servlet/JobSearch?op=300&N=0&Hf=0&NUM_PER_PAGE=30&Ntk=JobSearchRanking&Ntx=mode+matchall&AREA_CODES=&AC_COUNTRY=1525&QUICK=1&ZIPCODE=&RADIUS=64.37376&ZC_COUNTRY=0&COUNTRY=1525&STAT_PROV=0&METRO_AREA=33.78715899%2C-84.39164034&TRAVEL=0&TAXTERM=0&SORTSPEC=0&FRMT=0&DAYSBACK=30&LOCATION_OPTION=2&FREE_TEXT=SQL&SEARCH.x=12&SEARCH.y=5" + "&WHERE=" + document.getElementById('zip').value;
link = link + escape(diceLink);
document.location.replace(link);
}
function mboxWriteTrackedLink(clickedMboxName, linkURL, linkTextOrImage){
document.write("}
New!!!SQL Server Job Bank
Find SQL Server jobs near you.
Enter Location: (City, State or ZIP)
mboxWriteTrackedLink('dice','http://www.dice.com','');
powered by:
There are some issues associated with managing identity columns with replication in your SQL Server database. As with previous releases of software, SQL Server 2005 requires that database administrators use special care when replicating tables with identity columns.
First, let me offer a little background about identity columns to help you understand why they're different from any other column with a numeric data type. Identity columns have monotonously increasing numeric values that SQL Server assigns to each row automatically when the row is created. Normally, identity columns have INT or BIGINT data types, although you could use other numeric data types as well. By default, SQL Server seeds such columns at 1 and increments by 1, but you can change to the seed and increment of your liking.
Identity columns are good candidates for a table's primary key because they're unique for each row and cannot be updated without deleting and re-creating the row.
SQL Server replication scenarios
To make this tip easier to follow, let's imagine we're trying to replicate a table with the following schema:
Click here to view schema.
Next, let's consider various replication scenarios where this table could be used:
Publisher and subscriber have the same data, and the subscribing database is used for read-only purposes. This is the simplest scenario; you don't need AccountKey column to have an identity property on the subscriber because you'll never add any rows to it. Instead, as records are added to the publisher database, they'll also be added to the subscriber through replication.
Multiple publishers replicate data to a single subscriber. With this scenario, we still presume that data is replicated in one direction, from publishers to subscriber(s), and no direct data changes occur on the subscriber. Now things are a bit more complicated, though, because we don't want duplicate values for primary key column. No worries – we can seed the AccountKey column at different values on each publisher. For example, if we expect a lot of records to be inserted on each of the three publishers, we can seed them as follows:
Publisher 1: [AccountKey] [int] IDENTITY(1,1)Publisher 2: [AccountKey] [int] IDENTITY(100000000,1)Publisher 3: [AccountKey] [int] IDENTITY(200000000,1)
This configuration would allow users to add up to 100 million records to the DimAccount table in each database. Furthermore, it also gives us an easy way of identifying records created on each server. What if we need to add more records on any server? What if our application grows by leaps and bounds and we need to add dozens of new servers with dimAccount tables? No need to panic. First, we can change the identity seed using DBCC CHECKIDENT statement at any time. So if we reach the identity seed of 199,999,999 on publisher 1 and we're about to step into the range of the second publisher, we can change the identity seed on the first server as follows:
DBCC CHECKIDENT (dimAccount, RESEED, 400000000)
Keep in mind that INT data type accepts negative values as well, and this data type can support up to 4 billion records (-2 billion to 2 billion). If you need to store more than 2 billion records, you'll need to switch to the BIGINT data type, which supports a huge range of values, between –9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. I told you there was no need to worry!
Records can be added to the subscriber database, but they don't need to be replicated to publisher(s). This case is somewhat tricky. At first you might think we can add AccountKey with identity property and set its seed to a huge value, perhaps 1,000,000,000; but that alone won't work, for two reasons. First, with such architecture, an attempt to add a replicated record to the subscriber will fail. This is because you cannot explicitly specify the value of an identity column unless you issue SET IDENTITY_INSERT ON statement.
Second, if you enable the IDENTITY INSERT and add a record with identity value of 200,000,001, you'll effectively reset the identity seed on the subscriber. The next record you add directly (not through replication) to the subscriber will have the identity value of 200,000,002, which overlaps with the range of values we assigned to a publisher. Fortunately, we can use IDENTITY, NOT FOR REPLICATION option when defining the column on the subscriber. This option advises SQL Server not to override the current identity seed when records are added to the subscriber database through replication.
Records can be added on the subscriber and must also be delivered to publishers. Allow me to digress for a second and offer a personal opinion about this scenario. Although updateable subscriptions have been supported for years, I highly recommend using this option sparingly, i.e., only when absolutely necessary. The typical developer mentality is to use this (or any other) option because it's available. This is why it's crucial to separate developer and DBA duties.
As a DBA, you need to minimize the overhead on your server, because when systems behave poorly, all fingers point at you. Always require a valid business requirement for updateable subscriptions. "Do it because we want you to" is not a valid reason. Replicating transactions bi-directionally involves a fair amount of overhead. Realize that a transaction cannot be committed on the publisher until it is also committed on the subscriber. This functionality is implemented through replication triggers and what is referred to as the two-phase commit.
In this case, we must use IDENTITY, NOT FOR REPLICATION option both on publisher(s) and on subscribers. Once again, please do not read this tip as a recommendation to use updateable subscriptions when they're not necessary. I provide additional guidance for this scenario in the following section.
A special case of scenario 4 is the "peer-to-peer" publication, available only with SQL Server 2005. I'll save the discussion of peer-to-peer publications for another tip.
Options to manage identity seeds for replicated tables
If you must use updateable subscriptions, you need to define identity ranges on publisher and subscriber servers to avoid the creation of duplicate primary keys.
SQL Server 2005 supports several options for managing identity seed ranges. Note that you can set these options only when you first add the table
More on SQL Server replication:
Guide: Replication techniques in SQL Server
Podcast: SQL Server replication
article to the publication. If you need to change the identity management options, you must remove the article from the publication and add it back. Depending on the table size and your application's availability requirements, dropping and re-adding articles on demand might not be an option. Be sure to carefully choose the proper option.
Here is the summary of identity management options:
Manual – default and self-explanatory option. SQL Server doesn't manage identity seeds for you. Database administrator must explicitly configure ranges on publisher and subscriber. You can implement identity ranges by simply adding CHECK constraints to the replicated table on the publisher and subscriber(s). The same process works if you're subscribing to transactions replicated by multiple publishers. For example, I could add the following check constraints to dimAccount:
-- on the publisher:ALTER TABLE DimAccountADD CONSTRAINT ck_AcctKeyCHECK NOT FOR REPLICATION (AccountKey < 1000)
-- on the subscriber:ALTER TABLE DimAccountADD CONSTRAINT ck_AcctKeyCHECK NOT FOR REPLICATION (AccountKey >= 1000)
Now, if identity value reaches 1,000 on the publisher, SQL Server will return the following error:
The INSERT statement conflicted with the CHECK constraint "ck_AcctKey".The conflict occurred in database "AdventureWorksDW", table "dbo.DimAccount",column 'AccountKey'.
To resolve the problem, I should find the highest identity value on the subscriber, then re-seed the identity of the replicated table on the publisher so that its primary key values do not overlap with those found on the subscriber.
-- execute on the subscriber:SELECT MAX(AccountKey) FROM dimAccount
--let's suppose the previous statement returned 1510-- next reseed the publisher and allow thesubscriber plenty of room to grow:-- execute this on the publisher so that thenext identity value is 5000:DBCC CHECKIDENT('DimAccount', RESEED, 5000)
Keep in mind that replication will copy check constraints from publisher to subscriber(s) by default. If you're managing identity seeds manually, be sure to change this default behavior (using article properties' dialog) so that these check constraints aren't replicated.
Automatic – SQL Server automatically assigns identity ranges on publisher and subscriber based on additional parameters you specify. With automatic identity management, you need to provide the following parameters:
Publisher range size – range of identity values on the publisher
Subscriber range size – range of identity values on the subscriber
Once you specify these values, SQL Server automatically adds a check constraint to the replicated table on the publisher, as well as subscriber servers. The check constraint on the publisher allows identity values between the current highest value plus identity seed (for example 500 + 1= 501) and current max value plus publisher size range (for example, 500 + 10,000 = 10,500). The subscriber is seeded at one plus the maximum value allowed on the publisher; continuing from previous examples, the seed on the subscriber would be 10,501. The value of "subscriber range size" parameter is used to determine the upper limit. If the identity range "fills up" and you attempt to add a new record, you will get the following error message:
The insert failed. It conflicted with an identity range check constraint in database 'XYZ', replicated table 'dbo.DimAccount', column 'AccountKey'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
As the error message indicates, you can fix the problem by executing a system procedure as follows:
sp_adjustpublisheridentityrange 'DimAccount'
This will adjust the check constraint and give the table on the publisher server a new range of identity values to work with.
None – this option is supported only for backward compatibility with previous versions. If you use a wizard to migrate your replicated databases from prior versions to SQL Server 2005, by default this option will be chosen for tables with identity columns. The net effect of this option is that you must manage identity values manually.
Summary
In this tip, I discussed various scenarios for replicating tables that have identity columns and options for identity management. You don't have to ditch identity columns to use replication, just handle them with care.
Managing identity columns with replication in SQL ServerBaya Pavliashvili08.03.2007Rating: -4.50- (out of 5)
Expert advice on database administration
Digg This! StumbleUpon Del.icio.us
ttWriteMboxDiv('searchSQLServer_Tip_Content_Body');
ttWriteMboxContent('searchSQLServer_Tip_Content_Body');
function writeLink() {
var link = "http://mbox5.offermatica.com/m2/techtarget/ubox/page" +
"?mbox=dice" + "&mboxSession=" + mboxFactoryDefault.getSessionId().getId() +
"&mboxPC=" + mboxFactoryDefault.getPCId().getId() + "&mboxXDomain=disabled" +
"&mboxDefault=";
var domain = document.domain;
var diceLink = "http://ad.doubleclick.net/clk;179435819;24234612;k?http://seeker.dice.com/jobsearch/servlet/JobSearch?op=300&N=0&Hf=0&NUM_PER_PAGE=30&Ntk=JobSearchRanking&Ntx=mode+matchall&AREA_CODES=&AC_COUNTRY=1525&QUICK=1&ZIPCODE=&RADIUS=64.37376&ZC_COUNTRY=0&COUNTRY=1525&STAT_PROV=0&METRO_AREA=33.78715899%2C-84.39164034&TRAVEL=0&TAXTERM=0&SORTSPEC=0&FRMT=0&DAYSBACK=30&LOCATION_OPTION=2&FREE_TEXT=SQL&SEARCH.x=12&SEARCH.y=5" + "&WHERE=" + document.getElementById('zip').value;
link = link + escape(diceLink);
document.location.replace(link);
}
function mboxWriteTrackedLink(clickedMboxName, linkURL, linkTextOrImage){
document.write("}
New!!!SQL Server Job Bank
Find SQL Server jobs near you.
Enter Location: (City, State or ZIP)
mboxWriteTrackedLink('dice','http://www.dice.com','');
powered by:
There are some issues associated with managing identity columns with replication in your SQL Server database. As with previous releases of software, SQL Server 2005 requires that database administrators use special care when replicating tables with identity columns.
First, let me offer a little background about identity columns to help you understand why they're different from any other column with a numeric data type. Identity columns have monotonously increasing numeric values that SQL Server assigns to each row automatically when the row is created. Normally, identity columns have INT or BIGINT data types, although you could use other numeric data types as well. By default, SQL Server seeds such columns at 1 and increments by 1, but you can change to the seed and increment of your liking.
Identity columns are good candidates for a table's primary key because they're unique for each row and cannot be updated without deleting and re-creating the row.
SQL Server replication scenarios
To make this tip easier to follow, let's imagine we're trying to replicate a table with the following schema:
Click here to view schema.
Next, let's consider various replication scenarios where this table could be used:
Publisher and subscriber have the same data, and the subscribing database is used for read-only purposes. This is the simplest scenario; you don't need AccountKey column to have an identity property on the subscriber because you'll never add any rows to it. Instead, as records are added to the publisher database, they'll also be added to the subscriber through replication.
Multiple publishers replicate data to a single subscriber. With this scenario, we still presume that data is replicated in one direction, from publishers to subscriber(s), and no direct data changes occur on the subscriber. Now things are a bit more complicated, though, because we don't want duplicate values for primary key column. No worries – we can seed the AccountKey column at different values on each publisher. For example, if we expect a lot of records to be inserted on each of the three publishers, we can seed them as follows:
Publisher 1: [AccountKey] [int] IDENTITY(1,1)Publisher 2: [AccountKey] [int] IDENTITY(100000000,1)Publisher 3: [AccountKey] [int] IDENTITY(200000000,1)
This configuration would allow users to add up to 100 million records to the DimAccount table in each database. Furthermore, it also gives us an easy way of identifying records created on each server. What if we need to add more records on any server? What if our application grows by leaps and bounds and we need to add dozens of new servers with dimAccount tables? No need to panic. First, we can change the identity seed using DBCC CHECKIDENT statement at any time. So if we reach the identity seed of 199,999,999 on publisher 1 and we're about to step into the range of the second publisher, we can change the identity seed on the first server as follows:
DBCC CHECKIDENT (dimAccount, RESEED, 400000000)
Keep in mind that INT data type accepts negative values as well, and this data type can support up to 4 billion records (-2 billion to 2 billion). If you need to store more than 2 billion records, you'll need to switch to the BIGINT data type, which supports a huge range of values, between –9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. I told you there was no need to worry!
Records can be added to the subscriber database, but they don't need to be replicated to publisher(s). This case is somewhat tricky. At first you might think we can add AccountKey with identity property and set its seed to a huge value, perhaps 1,000,000,000; but that alone won't work, for two reasons. First, with such architecture, an attempt to add a replicated record to the subscriber will fail. This is because you cannot explicitly specify the value of an identity column unless you issue SET IDENTITY_INSERT ON statement.
Second, if you enable the IDENTITY INSERT and add a record with identity value of 200,000,001, you'll effectively reset the identity seed on the subscriber. The next record you add directly (not through replication) to the subscriber will have the identity value of 200,000,002, which overlaps with the range of values we assigned to a publisher. Fortunately, we can use IDENTITY, NOT FOR REPLICATION option when defining the column on the subscriber. This option advises SQL Server not to override the current identity seed when records are added to the subscriber database through replication.
Records can be added on the subscriber and must also be delivered to publishers. Allow me to digress for a second and offer a personal opinion about this scenario. Although updateable subscriptions have been supported for years, I highly recommend using this option sparingly, i.e., only when absolutely necessary. The typical developer mentality is to use this (or any other) option because it's available. This is why it's crucial to separate developer and DBA duties.
As a DBA, you need to minimize the overhead on your server, because when systems behave poorly, all fingers point at you. Always require a valid business requirement for updateable subscriptions. "Do it because we want you to" is not a valid reason. Replicating transactions bi-directionally involves a fair amount of overhead. Realize that a transaction cannot be committed on the publisher until it is also committed on the subscriber. This functionality is implemented through replication triggers and what is referred to as the two-phase commit.
In this case, we must use IDENTITY, NOT FOR REPLICATION option both on publisher(s) and on subscribers. Once again, please do not read this tip as a recommendation to use updateable subscriptions when they're not necessary. I provide additional guidance for this scenario in the following section.
A special case of scenario 4 is the "peer-to-peer" publication, available only with SQL Server 2005. I'll save the discussion of peer-to-peer publications for another tip.
Options to manage identity seeds for replicated tables
If you must use updateable subscriptions, you need to define identity ranges on publisher and subscriber servers to avoid the creation of duplicate primary keys.
SQL Server 2005 supports several options for managing identity seed ranges. Note that you can set these options only when you first add the table
More on SQL Server replication:
Guide: Replication techniques in SQL Server
Podcast: SQL Server replication
article to the publication. If you need to change the identity management options, you must remove the article from the publication and add it back. Depending on the table size and your application's availability requirements, dropping and re-adding articles on demand might not be an option. Be sure to carefully choose the proper option.
Here is the summary of identity management options:
Manual – default and self-explanatory option. SQL Server doesn't manage identity seeds for you. Database administrator must explicitly configure ranges on publisher and subscriber. You can implement identity ranges by simply adding CHECK constraints to the replicated table on the publisher and subscriber(s). The same process works if you're subscribing to transactions replicated by multiple publishers. For example, I could add the following check constraints to dimAccount:
-- on the publisher:ALTER TABLE DimAccountADD CONSTRAINT ck_AcctKeyCHECK NOT FOR REPLICATION (AccountKey < 1000)
-- on the subscriber:ALTER TABLE DimAccountADD CONSTRAINT ck_AcctKeyCHECK NOT FOR REPLICATION (AccountKey >= 1000)
Now, if identity value reaches 1,000 on the publisher, SQL Server will return the following error:
The INSERT statement conflicted with the CHECK constraint "ck_AcctKey".The conflict occurred in database "AdventureWorksDW", table "dbo.DimAccount",column 'AccountKey'.
To resolve the problem, I should find the highest identity value on the subscriber, then re-seed the identity of the replicated table on the publisher so that its primary key values do not overlap with those found on the subscriber.
-- execute on the subscriber:SELECT MAX(AccountKey) FROM dimAccount
--let's suppose the previous statement returned 1510-- next reseed the publisher and allow thesubscriber plenty of room to grow:-- execute this on the publisher so that thenext identity value is 5000:DBCC CHECKIDENT('DimAccount', RESEED, 5000)
Keep in mind that replication will copy check constraints from publisher to subscriber(s) by default. If you're managing identity seeds manually, be sure to change this default behavior (using article properties' dialog) so that these check constraints aren't replicated.
Automatic – SQL Server automatically assigns identity ranges on publisher and subscriber based on additional parameters you specify. With automatic identity management, you need to provide the following parameters:
Publisher range size – range of identity values on the publisher
Subscriber range size – range of identity values on the subscriber
Once you specify these values, SQL Server automatically adds a check constraint to the replicated table on the publisher, as well as subscriber servers. The check constraint on the publisher allows identity values between the current highest value plus identity seed (for example 500 + 1= 501) and current max value plus publisher size range (for example, 500 + 10,000 = 10,500). The subscriber is seeded at one plus the maximum value allowed on the publisher; continuing from previous examples, the seed on the subscriber would be 10,501. The value of "subscriber range size" parameter is used to determine the upper limit. If the identity range "fills up" and you attempt to add a new record, you will get the following error message:
The insert failed. It conflicted with an identity range check constraint in database 'XYZ', replicated table 'dbo.DimAccount', column 'AccountKey'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
As the error message indicates, you can fix the problem by executing a system procedure as follows:
sp_adjustpublisheridentityrange 'DimAccount'
This will adjust the check constraint and give the table on the publisher server a new range of identity values to work with.
None – this option is supported only for backward compatibility with previous versions. If you use a wizard to migrate your replicated databases from prior versions to SQL Server 2005, by default this option will be chosen for tables with identity columns. The net effect of this option is that you must manage identity values manually.
Summary
In this tip, I discussed various scenarios for replicating tables that have identity columns and options for identity management. You don't have to ditch identity columns to use replication, just handle them with care.
Subscribe to:
Posts (Atom)