Árvore de páginas

Versões comparadas

Chave

  • Esta linha foi adicionada.
  • Esta linha foi removida.
  • A formatação mudou.
HTML
<div id="main-content" class="wiki-content group">
	<h1 id="MigraçãoeConversãodeCollation(SQLServer)-Índice">Contents</h1>
	<p>
		<style type='text/css'>/*<![CDATA[*/
div.rbtoc1412695589165 {
	padding: 0px;
}
div.rbtoc1412695589165 ul {
	list-style: none;
	margin-left: 0px;
}
div.rbtoc1412695589165 li {
	margin-left: 0px;
	padding-left: 0px;
}
/*]]>*/
</style>
	<div class='toc-macro rbtoc1412695589165'>
		<ul class='toc-indentation'>
			<li><span class='TOCOutline'>1</span> <a
				href='#MigraçãoeConversãodeCollation(SQLServer)-Objetivo'>Objective</a></li>
			<li><span class='TOCOutline'>2</span> <a
				href='#MigraçãoeConversãodeCollation(SQLServer)-ProcedimentodeMigraçãoeConversãodeDados'>Data
					Migration and Conversion Procedure</a></li>
		</ul>
	</div>
	</p>
	<h1 id="MigraçãoeConversãodeCollation(SQLServer)-Objetivo">Objective</h1>
	<p>The objective of this guide is to indicate a method of migrating
		data from a base with collation different than the standard
		"Latin1_General_CI_AS" required for the proper use of the Fluig base.</p>
	<h1
		id="MigraçãoeConversãodeCollation(SQLServer)-ProcedimentodeMigraçãoeConversãodeDados">Data
		Migration and Conversion Procedure</h1>
	<p>
		<strong><br /></strong>
	</p>
	<div class="aui-message warning shadowed information-macro">
		<p class="title">Attention!</p>
		<span class="aui-icon icon-warning">Icon</span>
		<div class="message-content">
			<p>This operation must take place before the conversion of a
				TOTVS | ECM base to Fluig. Even with modification of the default
				collation, the existing fields remain incorrect, and can therefore
				result in unexpected behavior by the platform.</p>
		</div>
	</div>
	<p>&#160;</p>
	<p>The following is a checklist for Fluig conversion.&#160;</p>
	<p>&#160;</p>
	<ol>
		<li>Check procedures for the service/instance of the SGBD
			Microsoft SQL&#160;<span
			style="font-size: 10.0pt; line-height: 13.0pt;">Server,
				describe in the following page:&#160;<a href="73078837.htmlhttp://tdn.totvs.com/pages/viewpage.action?pageId=185741478">Configuration
					of the SQL Server data bank</a>.
		</span><br /> <br />
		</li>
		<li>Create a new data base with <strong>grouping </strong>(Collate/Collation)
			Latin1_General_CI_AS.<br /> <span
			style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span></li>
		<li><p>
				Immediately after creation, <strong>with no connection</strong> in
				this data base, execute the command:
			</p>
			<p>
				<br /> <br />
			</p>
			<div class="code panel pdl" style="border-width: 1px;">
				<div class="codeContent panelContent pdl">
					<pre class="theme: Eclipse; brush: sql; gutter: false"
						style="font-size: 12px;">ALTER DATABASE [fluig] SET READ_COMMITTED_SNAPSHOT ON;</pre>
				</div>
			</div>
			<p>
				<br />The configurations for grouping of characters (<em>Collate/Collation</em>)
				of the data bases may still be queried through the following
				instruction:
			</p>
			<p>
				<span style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span>
			</p>
			<div class="code panel pdl" style="border-width: 1px;">
				<div class="codeContent panelContent pdl">
					<pre class="theme: Eclipse; brush: sql; gutter: false"
						style="font-size: 12px;">SELECT [name] , [is_read_committed_snapshot_on] , [collation_name]&#160;
FROM [sys].[databases] 
WHERE [name] LIKE 'fluig%';</pre>
				</div>
			</div>
			<p>
				<br /> <br />
			</p></li>
		<li>Install the latest version of Fluig and configure it to point
			to this data base (fluig).<br /> <br />
		</li>
		<li>Start the Fluig service, and observe the LOG to make sure
			that <span style="font-size: 10.0pt; line-height: 13.0pt;">the
				service started correctly, without any serious errors, such as
				failure to&#160;</span><span
			style="font-size: 10.0pt; line-height: 13.0pt;">connect with
				SGBD.</span><br /> <span style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span>
		</li>
		<li>After the service has finished starting up (with the message
			"== FLUIG STARTED AND RUNNING RIGHT NOW ==" reported in the LOG),
			interrupt the Fluig service.<br /> <br />
		</li>
		<li>Generate a DUMP of this data base, with the entire structure
			of tables and&#160;<span>fields.&#160;</span><br /> <span>For
				the SGBD Microsoft SQL Server, use the following&#160;</span><span>procedure:</span><br />
			<ol>
				<li style="list-style-type: none;"><ol>
						<li>Move the mouse over the database and click on the right
							button.</li>
						<li>In the drop-down menu, choose <strong>Tasks >
								Generate Scripts...</strong></li>
						<li>Click on <strong>Next ></strong> to see the
							presentation&#160;<span>Introducing <strong>Generate
									and Post Scripts</strong> wizard.
						</span></li>
						<li><span>Check <strong>Generate script for
									entire data&#160;</strong></span><span><strong>base</strong> and <strong>all
									data base objects</strong> and click&#160;</span><span><strong>Next
									></strong>.</span></li>
						<li><span>In the following screen, in the <strong>Output
									Type</strong> group, and in the group below,&#160;
						</span><span>select <strong>Save</strong> in the new query window.
						</span></li>
						<li><span>Click on <strong>Advanced</strong>.
						</span></li>
						<li><span>In the <strong>Advanced Script
									Options</strong> screen, change the option&#160;
						</span><span><strong>Grouping Scripts</strong> to <strong>True</strong>
								and click on <strong>OK</strong>.</span></li>
						<li><span>When returning to the <strong>Generate
									and Post Scripts&#160;</strong>screen, click&#160;
						</span><span>on <strong>Next ></strong>.
						</span></li>
						<li><p>
								In the next step, adjust the configurations to generate a script
								of the base. Click on <strong>Next ></strong>.
							</p></li>
						<li><p>Go back to the Microsoft SQL Server Management
								Studio window.</p></li>
						<li><p>
								<span>In the new window open with the script,
									comment/remove the first few&#160;</span><span>lines, up to the
									<strong>USE [fluig]</strong> instruction.
								</span>
							</p>
							<p>
								<span><br /></span>
							</p></li>
					</ol></li>
			</ol>
		</li>
		<li><p>Select and delete all tables in this new database.</p>
			<p>&#160;</p></li>
		<li><p>
				Execute the script DUMP generate in this new database in order to
				create the entire structure with <strong>no</strong> records.
			</p>
			<p>&#160;</p></li>
		<li><p>Proceed with constraints in the database tables. For
				the SGBD Microsoft SQL Server, use the following&#160;instruction:</p> <br />
			<ol>
				<li style="list-style-type: none;"><ol>
						<li><p>
								Open a New Query window (menu <strong>File > New >
									Database Mechanism Query</strong>);.
							</p></li>
						<li><p>Insert the query below and execute it in the new
								database.</p>
							<div class="code panel pdl" style="border-width: 1px;">
								<div class="codeContent panelContent pdl">
									<pre class="theme: Eclipse; brush: sql; gutter: false"
										style="font-size: 12px;">DECLARE @TABLENAME VARCHAR(8000) 
DECLARE @TABLENAME_HEADER VARCHAR(8000) 
DECLARE TNAMES_CURSOR CURSOR FOR 
 
SELECT [name] from [sys].[tables] 
 
OPEN TNAMES_CURSOR 
 	FETCH NEXT FROM TNAMES_CURSOR INTO @TABLENAME 
 		WHILE (@@FETCH_STATUS &lt;> -1) 
 		BEGIN 
 			IF (@@FETCH_STATUS &lt;> -2) 
 			BEGIN 
 			SELECT @TABLENAME_HEADER = 'ALTER TABLE ' + RTRIM(UPPER(@TABLENAME)) + ' NOCHECK CONSTRAINT ALL;' 
			--Change NOCHECK to CHECK and then enable constraints 
			PRINT @TABLENAME_HEADER 
		END 
	FETCH NEXT FROM TNAMES_CURSOR INTO @TABLENAME 
END
 
SELECT @TABLENAME_HEADER = '--************* End of Tables *************--' 
PRINT @TABLENAME_HEADER PRINT ' ' 
DEALLOCATE TNAMES_CURSOR </pre>
								</div>
							</div></li>
						<li><p>Copy the result, paste it in a New Query window
								and execute it (in the new database).</p>
							<p>&#160;</p></li>
					</ol></li>
			</ol></li>
		<li><p>With the Fluig services interrupted, import the data
				from the old database, using, for example, an import and export data
				wizard. For the SGBD Microsoft SQL Server, use the
				following&#160;procedure:</p> <br />
			<ol>
				<li style="list-style-type: none;"><ol>
						<li><p>Move the mouse over the new database and click on
								the right button.</p></li>
						<li><p>
								In the drop-down menu, select <strong>Tasks > Import
									Data...</strong>
							</p></li>
						<li><p>
								In the introductory screen for the SQL Server Import and Export
								Wizard, click on <strong>Next ></strong>.
							</p></li>
						<li><p>
								In the field <strong>Data</strong> source, select <strong>SQL
									Server Native Client</strong> (the version number is displayed at the
								end, use the same number as the data bank service version).
							</p></li>
						<li><p>
								In the field <strong>Server</strong> name, enter the server
								(enter or select from drop-down box) and the instance that has
								the previous database (which must be reconciled /
								normalized).&#160;
							</p></li>
						<li><p>
								In the <strong>Authentication</strong> group, use the method
								that allows access to all data from this previous database. A
								user with <strong>db_owner</strong> permission is recommended.
							</p></li>
						<li><p>
								In the field <strong>Database</strong>, select or enter the
								previous base and click on <strong>Advance ></strong>.
							</p></li>
						<li><p>
								In the screen with the header&#160;<strong>Choose a
									Target</strong>, the fields with access information to this new database
								should be automatically filled out. Correct any incorrect or
								incomplete information and click <strong>Next ></strong>.
							</p></li>
						<li><p>
								In the screen with the header <strong>Specify Copy</strong> or <strong>Table
									Query</strong>, select <strong>Copy</strong> data from one or more
								tables or views and click <strong>Next ></strong>.
							</p></li>
						<li><p>
								In the screen with the header <strong>Select Tables</strong> and
								<strong>Source Views</strong>, click on the first selection box
								in the headers of the <strong>Tables and views</strong> list.
							</p></li>
						<li><p>
								With all options checked, click <strong>Edit
									Mappings...</strong>
							</p></li>
						<li><p>
								In the box <strong>Transfer Settings, </strong>check the box <strong>Enable
									insertion of identity</strong> and OK.
							</p>
							<div class="aui-message warning shadowed information-macro">
								<p class="title">Attention!</p>
								<span class="aui-icon icon-warning">Icon</span>
								<div class="message-content">
									<p>Make sure to check this option to avoid failure in data
										copying.</p>
								</div>
							</div></li>
						<li><p>
								Back to the previous window, <strong>Select Tables</strong> e <strong>Source
									Views</strong>, click on <strong>Next ></strong>.
							</p></li>
						<li><p>
								In the screen with the header <strong>Review Data</strong> <strong>Type
									Mapping</strong>, click <strong>Next</strong>.
							</p></li>
						<li><p>
								In the screen with the header <strong>Execute Package,
								</strong>check <strong>Execute immediately</strong> and click <strong>Next
									></strong>.
							</p></li>
						<li><p>
								In the screen with the header <strong>Conclude Wizard</strong>,
								review the actions taken and click <strong>Next</strong>.
							</p></li>
						<li><p>The following screen, displaying execution of the
								actions, should also display a message that import was
								successful.</p></li>
					</ol></li>
			</ol>
			<p>&#160;</p></li>
		<li><p>Perform access permissions transfer procedure to the
				appropriate users in the new database. These permissions may have
				been lost.&#160;</p>
			<p>&#160;</p></li>
		<li><p>
				Delete the temporary directories (tmp, log and data), located in the
				path <em>&lt;INSTALL_FLUIG>/jboss/standalone</em>.
			</p>
			<p>&#160;</p></li>
		<li><p>
				In the setting file, located in the path <em>&lt;INSTALL_FLUIG>/jboss/standalone/standalone.xml</em>,
				change the setting for access to the database to use of the new
				database.
			</p>
			<p>&#160;</p></li>
		<li><p>Restart the service.</p>
			<p>&#160;</p></li>
		<li><p>Perform the desired validation and use the new
				installation.</p></li>
	</ol>
	<p>&#160;</p>
	<p>&#160;</p>
</div>