Best Practices SQL Server for OpenText Content Server 16
OpenText SMAX商品说明书
Solution Brochure OpenText SMAXSmarter servicemanagement for everyonethat delivers efficient IT Service Management (ITSM), IT Asset Management (IT AM), and Enterprise Service Management (ESM). Powered by embedded analytics and machine learning, it is easy to use, easy to extend, and easy to run anywhere.SMAX helps you get more done, your way. Chooseincident, problem, change, request, release, servicecatalog, service-level, and knowledge management—based on ITIL best practices. Shift from slow, error-prone manual tasks to AI-driven automation that enableshigh-speed service fulfillment and issue resolution. Andreplace complex, over-customized deployments witheasy-to-update alternatives that can run anywhere—saving you time, effort, and money.SMAX—Driving greater productivity and faster issueresolution with AI-driven automation.Cuts average service desk call times by 20%.IT benefit—Employee benefit—Boosts user satisfaction by promoting self-help and decreasing downtime.Create a more engaging service desk experience. Agents can interact with users over the phone, via chat, or using LiveSupport, a dedicated user interface optimized for service desk agents that offers real-time solutions. And users can crowdsource knowledge, proactively collaborating with their peers to get questions answered.Empowers agents to tackle and resolve more issues, faster—delivering a better experience for all users.IT benefit—Business benefit—Improves productivity across the enterprise.service lifecycleCover all service phases, from plan to build to run. A modern user interface makes your work easy. You can automate processes, workflows, and tasks; resolve issues quickly; and respond to service requests efficiently. Built-in AI and machine learning make it possible.Comprehensiveservice managementModern service desk with live support and collaborationUnburden your service-desk agents and boost usersatisfaction with 24x7 self-service support. Now everyonecan use natural language and their favorite mobile deviceto receive instant help from an intent-based conversationalchatbot.Allow users to open a ticket easily via email or by submittingthe screenshot of an error message from their mobile device.AI and machine learning automatically analyze and categorizetickets for fast resolution. The system automatically learnsfrom manual adjustments made by service desk agents. user experienceDeliver enterprise-wide service and support from a fullyintegrated, intelligent, role-based self-service portal. Openingrequests is easy. Users can use natural language, submiterror-message screenshots, or chat with virtual agents fromtheir desks or mobile devices.Get fast answers from global searches, thanks to AI andmachine learning. Results come from multiple data sources—including incident, problem, and change management;knowledge-management databases; or Microsoft SharePoint.Users and agents can easily search using natural languagerather than IT keyword search terms.Self-service andrequest fulfillmentSmart searchVirtual agent Smart ticket andsmart emailFrees agents from repetitivetasks, improves supportscalability, and cuts costs.Streamlines service processes,shortens ticket-handling times,and speeds up issue resolution.IT benefit—IT benefit—Employee benefit—Employee benefit—Offers anywhere, anytimeself-help and fasterproblem resolution.Allows users to work theway that’s most convenientfor them.Decreases incident volumesand scales support.Provides quick answers forincoming requests and issues.IT benefit—IT benefit—Employee benefit—Employee benefit—Meets expectations foreasy, personalized, no-waitself-service.Makes it easy for users toproactively find answers totheir questions./SMAX7Makes it easy for agents and users to communicate clearly and accurately..Allows agents to deliver superior service with a single solution, unrestricted by language.Empower service portal users to open a request using their local language—for example, Chinese. The service desk agent can then open the ticket using the UI in another language—for example, English. The translation occurs automatically, inline and on-the-fly, for both agent and user. Note: Google Translator is required.Inline TranslationIT benefit—Business benefit—Choose from more than 100 out-of-the-box, ready-to-go ITservice management performance and productivity reports—or build your own—to quickly create interactive, role-based,real-time dashboards.Control change risk with AI, machine learning, and automation.Execute changes and releases automatically. And use automateddiscovery to gain clear visibility into your IT assets, understand theimpact of changes, and identify potential collisions. Equipped withthis knowledge, you can facilitate compliance and avoid changesthat introduce unintended risk. management productivityManage services proactively and avoid recurring incidents byfinding root causes with analytics based on AI and machinelearning. By identifying patterns and trends in user surveysand service management data, you can continually improveyour services.Resolve issues faster with automatic ticket categorization,routing, and agent-to-agent chat. Based on AI, hot topicanalytics helps you find root causes quickly and identifytrends to proactively prevent future issues.Intelligent analytics Incident and problem management Reporting and dashboards Change and release managementProvides the insights needed tomake informed, timely decisions.Allows IT to pivot with easeand keep the business runningsmoothly.IT benefit—IT benefit—Business benefit—Business benefit—Provides the informationneeded to make betterdecisions, faster.Improves enterpriseproductivity by minimizingservice disruptions.Supports proactive problemsolving and data-drivendecisions.Accelerates mean-time-to-restore-services and drivescontinual service improvement.IT benefit—IT benefit—Business benefit—Employee benefit—Reduces service disruptionsand their associated costs.Builds a responsive servicedesk that resolves issuesand requests quickly.Manage service requests throughout their lifecycle—from the initial request to its closure. Fulfillment plans include task plans, which can include both manual and automatic tasks.Gain insights based on accurate change management data, including suggestions for improvements. View graphical analyses of change management KPI performance—for example, change success rates and levels of automation. Customize your analysis with different criteria, such as service name, change, and time.Change analyticsRequest fulfillmentTrack and measure how well your organization is meeting service-level targets and fulfilling agreed-on service levels. With clear and instant visibility into service performance, you can prioritize work queues, review response times, and reset your targets.Create, maintain, share, and use knowledge to enable self-service support. Build knowledge based on roles—making it available to all users or limiting it to service desk personnel only.Knowledge managementService-level managementImproves agent efficiency and productivity.Demonstrates value to users and business stakeholders.IT benefit—IT benefit—Employee benefit—Business benefit—Gives users quick, easy access to standard services.Aligns IT services with business needs.Improves change success rates and reduces unplanned downtime.Offloads the burden of recurring incidents and requests from the service desk.IT benefit—IT benefit—Business benefit—Employee benefit—Minimizes the risk of unplanned service outages.Gives users instant access to the information they need to get back to work.Manage investments, software licenses, contracts, and otherassets for better decision making and cost reduction. Gain fullvisibility into your assets, from procurement through disposal.Keep track of software licenses to improve usage, decreaseIT spending, and support compliance.context and impactGet accurate, up-to-date configuration data and service contextto support ITSM processes with native SACM. Collect, reconcile,and manage data for all configuration items—and understand allrelationships—with automated discovery. Easily map inventory andinfrastructure dependencies to business services for a real-timeview of your hybrid environment.Automatically recognize the assets—along with theirconfigurations and interdependencies—that will be impactedby incoming changes, incidents, or service requests. Service asset andconfiguration managementFast, automatic ci detectionIt and software asset managementOptimizes resource usage andholds costs down.IT benefit—Business benefit—Limits financial and legalrisk.Provides a single source oftruth for robust planning andincreased transparency.Removes the need to huntfor the critical asset andconfiguration informationrequired to resolve issuesquickly.IT benefit—IT benefit—Business benefit—Employee benefit—Delivers reliable servicesand strengthens policycompliance.Makes it easy to reportissues without searchingthrough confusing technicaldata.Configure existing workflows and tasks or extend yourservice management solution and create new user-defined, process-based applications without writing any code. This will simplify the upgrade process and provide instant access to new features.Automate processes, workflows, and tasks using out-of-the-box best practices based on ITL. Easily remediate incidents and execute changes ranging from simple access requests to comprehensive workflows (such as detect-to-correct). Automate the fulfillment of IT services across environments—new and traditional, public and private cloud—to unify and accelerate service delivery.Enjoy the flexibility of running your service management solution wherever you need to—on premises or in private or public clouds such as AWS, Microsoft Azure, Google Cloud, and SaaS. You can also choose the named, concurrent, or unit-based licensing option that works best for you.Automated processes, workflows, and tasksRuns anywhereProcess and workflow design (codeless configuration)Removes the usual upgrade, customization, and maintenance complexities.IT benefit—Business benefit—Allows the organization to quickly and economically adapt to changing service demands.Reduces risk and inefficiencies related to manual repetitive work.Meets your evolving IT needs with flexible deployment and software licensing options.IT benefit—IT benefit—Business benefit—Business benefit—Drives down operational costs.Offers freedom of choice for easy alignment with corporate strategies.Manage your services and related investments throughouttheir entire lifecycle—from idea to service pipeline, servicecatalog, and service retirement.Track and manage the progress and health of your IT projectsand programs. With information about resource demand,financial implications, and business value at your fingertips,your decision-making abilities are significantly enhanced.service strategyCollect feedback from your service consumers to helpgenerate and develop new service ideas. You can thenturn these ideas into substantive proposals, build on themvia social collaboration, and add them to your servicemanagement portfolio.Gain a comprehensive overview of all your applications anddetermine which ones need to be modernized—for example,identifying which ones are suitable for the cloud.Idea and proposal management Application portfolio managementService portfolio andcatalog managementProject and program managementLearn more at/smax Start your free trial Optimizes the allocation ofcapabilities and resources.Optimizes resource andbudget plans for new orchanged services.IT benefit—IT benefit—Business benefit—Business benefit—Achieves proper alignmentbetween service offeringsand business needs.Maximizes the businessreturn of your services.Expedites continuous serviceimprovement.Gives IT a clear way to assessand prioritize applicationoptimization.IT benefit—IT benefit—Business benefit—Business benefit—Drives and demonstratesinnovation.Serves as a solidfoundation for applicationtransformation strategies./SMAX19 OpenText SMAX18/smax。
使用SQLServer的OPENROWSET函数
使用SQLServer的OPEN ROWSE T你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据，或者批量向S QL Server表导入数据。
在这种情况下，你可以在T-SQL（Transa ct-SQL，微软对SQL 的扩展）中用OPENR OWSE T函数给数据源传入一个连接串和查询来提取需要你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据，或者批量向S Q L Server表导入数据。
在这种情况下，你可以在T-SQL（Transa ct-SQL，微软对SQL的扩展）中用OPENR OWSE T函数给数据源传入一个连接串和查询来提取需要的数据。
你可以使用O PENRO WSET函数从任何支持注册OL E DB的数据源获取数据，比如从SQL Server或Acce ss的远程实例中提取数据。
如果你用OPENROW SET从S Q L Server实例中获取数据，该实例必须配置为允许a d hoc分布式查询。
要配置远程S Q L Server实例支持a d hoc查询，需要使用系统存储过程s p_con figur e先设置a d vanc ed option s，再启用Ad Hoc Distributed Querie s（ad hoc分布式查询）。
请看下面的T-SQL脚本：EXEC sp_con figur e 'show advanc ed option s', 1;GORECONF IGURE;GOEXEC sp_con figur e 'Ad Hoc Distri buted Querie s', 1GORECONF IGURE;GO要注意的是，在运行完存储过程之后，你必须运行“RECONF IGURE”命令。
SqlServer调用OPENQUERY函数远程执行增删改查
SqlServer调⽤OPENQUERY函数远程执⾏增删改查/*OPENQUERY函数，远程执⾏数据库增删改查关于OPENQUERY函数第⼆个参数不⽀持拼接变量的⽅案⽅案1：将OPENQUERY语句整个拼接为字符串，再⽤EXEC执⾏该字符串语句⽅案2：将要拼接的变量直接挪移到括号外进⾏拼接由于字符串中单引号多层引⽤需要⽤到很多个单引号，容易混淆，导致⽅案1写起来头疼、眼睛疼，但是⾃由度⼤，想怎么来就怎么来。
⽽⽅案2能够清晰简洁地使⽤变量，但经我⽬前的测试，发现只有SELECT语句和DELETE语句的WHERE⼦句可以挪移出来（见上⽅SQL语句），限制太⼤。
OPENQUERY(MySQL, 'select * from hhp_user where chrusername = ''hhp'';')第⼀个参数为配置好的链接服务器名称，第⼆个参数为要执⾏的MySQL命令*/DECLARE @username NVARCHAR(50),@pwd VARCHAR(64),@pwdmd5 VARCHAR(64),@sql VARCHAR(2000),@sql2 VARCHAR(2000)--设置⽤户名SET @username = 'hhp'--设置密码SET @pwd = '123456'--对密码进⾏MD5加密SET @pwdmd5 = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @pwd)),3,32)--1.SELECT语句SELECT * FROM OPENQUERY(MySQL, 'select * from hhp_user where chrusername = ''hhp'';');--或SELECT * FROM OPENQUERY(MySQL, 'select * from hhp_user') WHERE chrusername = @username--2.INSERT语句，INTO可省略INSERT INTO OPENQUERY(MySQL,'select chrusername,chrpwd from hhp_user;') VALUES( @username , @pwdmd5)--3.UPDATE语句SET @pwd = 'hhp'SET @pwdmd5 = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @pwd)),3,32)UPDATE OPENQUERY(MySQL, 'select chrusername, chrpwd from hhp_user where chrusername = ''hhp''') SET chrpwd = @pwdmd5--4.DELETE语句，FROM可省略DELETE FROM OPENQUERY(MySQL, 'select * from hhp_user where chrusername = ''hhp''')--或SET @username = 'ls'DELETE FROM OPENQUERY(MySQL, 'select * from hhp_user') WHERE chrusername = @username。
OpenText IDOL专业服务产品介绍说明书
FlyerIDOL Professional ServicesRoadmap, design, and deployment services delivered by OpenT ext IDOL experts.IDOL Professional Servicesat a Glance:■A n auto racing company was able to glean real-time market response analysis to its events,resulting in significant optimization of its marketing spend—all made possible through our services■H ealthcare insurance companies and major hospitals are now able to perform analysis thatwas otherwise impossible on claims and patientrecords to better serve patients■T ight integration of rich media analytics is making public and private sector facilities moresecure than ever through multichannel analytics,professionally and accurately deployed the first time OverviewOpenT ext™ Intelligent Data Operating Layer(IDOL) applies data analytics with artificialintelligence and machine learning to enableorganizations to automate and supplementa vast array of manual-based tasks such astrend analysis and video surveillance. IDOLcombines context-based analytics and visu-alization capabilities to empower knowledgeworkers and help companies take a data-drivenapproach to their processes, operations, andcustomer interactions.The OpenText IDOL Professional Servicesteam has assisted numerous organizations ingaining significant improvements in the per-formance, scalability, and analytics capabilitiesenabled by IDOL. Examples include a varietyof industries:■An auto racing company was able to gleanreal-time market response analysis to itsevents, resulting in significant optimizationof its marketing spend—all made possiblethrough our services■Healthcare insurance companies andmajor hospitals are now able to performanalysis that was otherwise impossibleon claims and patient records to betterserve patients■Tight integration of rich media analyticsis making public and private sectorfacilities more secure than ever throughmultichannel analytics, professionallyand accurately deployed the first timeIn nearly every industry across the globe, IDOLis making a positive impact when prudently de-ployed using IDOL Professional Services.Service Planning and DeploymentT o realize the full potential and value of IDOL,planning and design are critical to your deploy-ment and migration. We offer two services thatare designed to help you address the primarystages of IDOL implementation:IDOL Deployment Roadmapand Design ServiceThrough this service, our consultants captureand document all customer requirements, andmap them to proven practices that have beenrefined by a global team with extensive IDOLexperience. We detail and augment our recom-mendations with the level of effort and resourceestimations. During this engagement, we canalso assess your existing architecture, as newIDOL version features and performance mayallow architectures with improved resilienceand a lower hardware footprint in addition toimproved performance. The design and rec-ommendation is documented and discussedfor refinement before finalization. Y ou may thendecide to implement the recommendations orengage OpenT ext IDOL consultants to deliverthe implementation.IDOL Deployment and Migration ServiceThrough this service, we assist with the in-stallation and configuration of IDOL in yourenvironment. As part of the service, we de-liver a solution roadmap outlining additionaluse cases that could improve your businessand increase your return on investment. Ourproven methodology enables the success ofyour implementation. We transfer all compo-nents and data in the existing IDOL system orlegacy search system to the newly-deployedIDOL infrastructure, and we install and con-figure IDOL functional components that arelicensed. During the implementation, we willconfigure IDOL, connectors, distribution sys-tem, and jobs to ingest data into a distributedIDOL index. Our consultants will also provideadvisory consulting for using IDOL APIs forcustom search application development or willdeploy an out-of-the-box search user interface in your environment In addition, we deliver in-stallation specifications and best practices for operations and administration, with a compre-hensive consulting report.The OpenT ext Professional Services DifferenceOpenText provides unmatched capabilities with a comprehensive set of consulting and im-plementation services and unique intellectual property that help you drive innovation through streamlined and efficient software delivery.■Proven OpenT ext software solution implementation expertise ■More than 20 years of experience helping large, complex, global organizations realize value from their OpenT ext software investments■Rich intellectual property and unparalleled reach into product engineering■T echnology-agnostic implementation approach with no vendor lock-in, no rip-and-replace■Education and support services to ensure adoptionLearn more at/en-us/ professional-services/overviewwww.micro /opentext261-000131-001 | O | 07/23 | © 2023 Open T ext。
SQLSERVER调用OPENROWSET的方法
SQLSERVER调⽤OPENROWSET的⽅法前⾔：正好这两天在同步⽣产环境的某张表数据到测试环境，之前⽤过⼀些同步数据软件，感觉不太可靠，有时候稍有操作不当，就会出现⽣产环境数据被清空等情况，还要去恢复数据。
如果能恢复还好，不能恢复那么......想想就觉得阔怕，后来想起 SQLSERVER 有OPENROWSET 函数可以通过 T-SQL 访问远程数据库，正好可以使⽤，看得见的SQL ⽐同步数据软件看起来安⼼多了，哈哈.... 不讲废话了⼀、OPENROWSET 简介：包含访问 OLE DB 数据源中的远程数据所需的所有连接信息。
当访问链接服务器中的表时，这种⽅法是⼀种替代⽅法，并且是⼀种使⽤ OLE DB 连接并访问远程数据的⼀次性的临时⽅法。
对于较频繁引⽤ OLE DB 数据源的情况，请改为使⽤链接服务器。
OPENROWSET 函数可以在查询的 FROM ⼦句中引⽤，就好象它是⼀个表名。
依据 OLE DB 提供程序的功能，还可以将 OPENROWSET 函数引⽤为 INSERT、UPDATE 或 DELETE 语句的⽬标表。
尽管查询可能返回多个结果集，但 OPENROWSET 只返回第⼀个结果集。
1. 语法详解OPENROWSET( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'|'provider_string' }, { [ catalog. ][ schema. ] object|'query'}} )provider_name：字符串，表⽰在注册表中指定的 OLE DB 访问接⼝的友好名称）。
provider_name 没有默认值datasource：对应于特定 OLE DB 数据源的字符串常量。
datasource 是要传递给提供程序的 IDBProperties 接⼝的DBPROP_INIT_DATASOURCE 属性，该属性⽤于初始化提供 程序。
sql server中openquery的用法
SQL Server中的openquery是一个非常有用的功能，它允许用户在一个远程服务器上执行查询。
通过Openquery，用户可以在当前服务器上执行远程服务器上的查询，并将结果返回到本地服务器上。
在实际应用中，openquery经常用于处理跨服务器的数据查询、数据同步等任务。
下文将详细介绍openquery的用法和应用。
二、基本语法在SQL Server中，使用openquery需要以下基本语法：OPENQUERY ( linked_server , 'query' )其中，linked_server是连接到远程服务器的名称或标识符，需要在当前服务器上进行配置。
query是在远程服务器上执行的查询语句。
三、示例下面是一个简单的示例，演示了如何使用openquery执行跨服务器查询：```FROM OPENQUERY(LinkedServerName, 'SELECT * FROM TableName')```在这个示例中，LinkedServerName是远程服务器的名称，TableName是远程服务器上的表名。
通过openquery，可以在当前服务器上查询远程服务器上的数据。
四、注意事项在使用openquery时，需要注意以下事项：1. 需要在当前服务器上配置连接到远程服务器的linked server。
可以通过sp_addlinkedserver存储过程或SQL Server Management Studio等工具进行配置。
2. openquery需要在当前服务器的上下文中执行，因此需要确保当前服务器上可以访问远程服务器。
3. 在编写查询语句时，需要注意避免使用在当前服务器上无法识别的特定于远程服务器的语法或函数。
五、应用场景openquery可以应用在多种场景中，主要包括以下几个方面：1. 跨服务器数据查询：在需要从远程服务器上获取数据的情况下，可以使用openquery执行跨服务器查询。
sql freetext
sql freetextSQL Freetext（全文搜索）是一种在SQL查询中使用的技术，它允许我们通过关键词来搜索数据库中的文本内容。
这种技术的使用非常广泛，可以在各种应用场景中发挥作用，包括搜索引擎、电子商务网站、博客等。
SQL Freetext的一个主要特点是它能够处理自然语言查询，而不仅仅是固定的关键词匹配。
这意味着我们可以输入一个或多个关键词，然后系统会根据这些关键词在数据库中进行匹配，并返回与这些关键词相关的文本内容。
在使用SQL Freetext进行全文搜索时，我们需要先创建一个全文索引。
全文索引是一个包含了数据库中所有文本内容的索引结构，它被用来加速全文搜索的过程。
全文索引的创建一般是在数据库表中的文本列上进行的，我们可以选择在一个或多个列上创建全文索引，以适应不同的查询需求。
在使用SQL Freetext进行查询时，我们可以使用FREETEXT函数来指定要搜索的关键词。
FREETEXT函数会将输入的关键词与全文索引中的内容进行匹配，并返回匹配的结果。
在查询结果中，我们可以根据匹配的程度对结果进行排序，以便更好地满足用户的需求。
除了FREETEXT函数，SQL Freetext还提供了其他一些函数和操作符，用于更精确地控制全文搜索的过程。
例如，我们可以使用CONTAINS函数来指定必须同时包含多个关键词的查询，或者使用NEAR操作符来指定两个关键词之间的距离限制。
在使用SQL Freetext进行查询时，我们还可以指定一些其他的参数，以进一步优化搜索的结果。
例如，我们可以使用LANGUAGE参数来指定查询所使用的语言，以便更好地匹配文本内容。
我们还可以使用TOP参数来限制返回的结果数量，以提高查询的效率。
尽管SQL Freetext在全文搜索方面提供了很多强大的功能，但是在使用时还是需要一些注意事项。
首先，由于全文索引需要占用一定的存储空间，所以在创建全文索引时需要考虑存储的成本。
SQLServer全文检索（full-text）语法
SQLServer全⽂检索（full-text）语法sql server 全⽂检索有两种搜索⽅式，⼀种是contains，另⼀种是freetext。
前者是包含，类似于like '%关键词%'，后者则是将⼀段⽂字分词以后对每个词进⾏搜索。
具体语法：contains： SELECT字段1,字段2 FROM表名 WHERE contains(字段,'"词⼀" or "词⼆"') 根据查找结果的相似度排序 SELECT字段1,字段2 FROM表名 inner join containstable(表名, 字段,'"词⼀" or "词⼆"',10) as k on表名.id = k.[key] order by k.RANK DESC freetext: SELECT字段1,字段2 FROM表名 WHERE freetext(字段,'词⼀词⼆') 根据查找结果的相似度排序 SELECT字段1,字段2 FROM表名 inner join freetexttable(表名, 字段,'词⼀词⼆',10) as k on表名.id = k.[key] order by k.RANK DESC上⽂中freetexttable或containstable的10表⽰取10条数据具体详细操作：数据库数据执⾏任意⽂本查询：1.使⽤FREETEXT谓词 FREETEXT接受两个参数。
第⼀个参数表⽰要搜索的列，可以提供列名，或者⽤*字符搜索表中的所有列。
第⼆个参数表⽰要搜索的短语。
例: select Title from Titles where FREETEXT(Title,'secret computer')该SQL语句将数据库表Titles中的Title列中的内容包括secret或computer的纪录查询出来。
OpenText企业内容管理系统购买者指南说明书
Choosing Governance- Based EnterpriseContent ManagementBuyer’s Guide for Electronic Records and Content ManagementBuyer’s Guide /opentextBuyer’s GuideChoosing Governance-Based Enterprise Content ManagementIntroductionFew organizations today are prepared to handle the exponential growth in the volume, velocity, and variety of their enterprise data. Fewer still are equipped to manage and account for that data and make the resulting records accessible to the organization as a whole. While information governance is a well established practice among government entities, it has typically not compelled private sector organizations to invest in OpenT ext ™ Enterprise Content Management (ECM) systems.This is changing, however. A 2015 survey conducted by AIIM revealed that 53% of companies surveyed now have new information gover-nance initiatives in place, driven by a variety of factors ranging from compliance to data loss prevention 1.0% 10% 20% 30% 40% 50% 60% 70%De ning sta responsibilities for desk, home, and mobile securityAbility to respond to requests, e.g., FoIA, personal data, etc.Supporting or defending litigation or disputesSecuring intellectual proprietary, competitive, or sensitive informationReducing storage space/defensible deletionCreating searchable knowledge for future referencePreventing data losses, privacy breaches, and con dentiality issuesCompliance with legal, audit, and regulators’ rulesFigure 1. What are the three biggest drivers to create and enforce IG policies in your organization? (N=319)The purpose of this ECM buyer’s guide is to provide organizations with a concise overview of how to select an ECM system that will also en-able industry best-practices information governance, as well as how to scope a project and plan it based on your business needs and objec-tives. This buyer’s guide is organized as follows:■ Introduction■ Single-sheet, quick-reference guide to choosing an ECM ■ Governance-based ECM: definition and discussion ■ T aking a best-practices approach ■ Core functions■ Key elements and capabilities ■ Additional considerations __________1 AIIM Industry Watch: Information Management State of the Industry 2016.59%percentage of large organizations citing compliance and risk as the biggest drivers for IM, up from 38% last year 1A governance-based ECM allows organizations to:■ Manage content across the information lifecycle■ Ensure the authenticity, reliability, and integrity of documentsand records■ Deliver content to authorized users across a range of devices ■ Access, share, and use content in a secure,managed environment■ Establish rules and processes so governance and recordsfunctions are managed transparently■ Overcome storage and access constraints to content ■ Transition from the confines of paper to the flexibility andfreedom of digital■ Leverage search technologies, links, and relationships for greatercollaboration and contextInternal pressure• Sta costs • Training • IT costs• Infrastructure costsExternal pressure• Money• Competition • Economy • ShareholdersBusiness challenges• Risk• Compliance • LegalOperational demands• Business continuity • Customer service • Mergers, acquisitionsLeveraging assets• Sta• Information • InfrastructureBusiness goals• Growth • Service • Costs• Productivity• CompetitivenessPressuresRelieve pressureand growECM selectionInformation governance strategyFigure 2. Considerations and drivers in selecting an enterprise records management systemBiggest problems related to poor enterprise content management practices 262% too long to find content52% duplicated efforts46%insufficient reuseQuick-Reference Guide to Choosing an ECM SystemMinimum Core FunctionalityAn ECM integrates document, email, records, and Web content man-Ex agement , content workflow, image processing, and collaboration tech -nologies. Organizations rely on ECM as a means to facilitate the lifetime management of their business content, from creation to disposal, ac-cording to their business needs and policy.Core functions:■ Contextual links among content, people, and process ■ Enterprise management through classification and linkeddisposal policies■ Efficient management and linking of governance andrecordkeeping controls for all content, not just records■ Storage and preservation of digital records Ability to maintaincontent processes and add layers of metadata■ Access, security, and auditBenefits:■ Improved sharing and collaboration __________2 Ibid3 Forrester Research and ARMA International Records Management Online Survey, Q2 20154 AIIM Industry Watch: Information Management State of the Industry 2016.■ Productivity and process improvements■ Consolidation of enterprise applications ■ Improved regulatory compliance■ Reduced paper storage and handling costs Reduced cost and riskof legal discovery, investigation, or audit■ Environmental savingsNew content sources are growing at an exponential rate, yet they are the least likely to have retention policies applied, according to a Q2 2015 online survey conducted by Forrester Research and ARMA. Conversely, physical records are the most likely to have such policies applied, de-spite their slowing and/or decreasing growth (39% of respondents re-ported a decrease in the volume of paper records)3.Big Data6%Social or collaboration sites (e.g., Jive or Chatter)Cloud-based le sharing and collaboration (e.g., Box)Mobile messaging/SMS/texts/BYODLine-of-business applications (e.g., ERP and CRM)Corporate websitesternal social media (e.g., Facebook, LinkedIn, or Twitter)Document management/ECM systemsInstant messagesVoicemailIntranet or company portalShared drives DatabasesDesktops/laptopsEmailPhysical records13%13%22%25%28%29%37%40%41%42%43%43%45%49%74%Figure 3. New content sources are least likely to have retention policies appliedAccording to a 2015 AIIM survey, 60% of respondents feel that automation is the only way to keep up with the increasing volumes of electronic content. Similarly, 63% feel that improved searchability is the biggest benefit of automated classification 4.Refer to the questions and capabilities below to initially assess, and ultimately choose, the ECM system that best suits the needs and objec-tives of your organization.Buyer’s GuideChoosing Governance-Based Enterprise Content ManagementDoes the Proposed ECM System Offer…Authenticity, reliability, and integrity of content:■Can the system capture metadata and events to support the transformation of information into records?■Can it understand the metadata required for good governance and recordkeeping? Learn more.Importance of metadata standards:■Is the system content-agnostic and source-agnostic—i.e., canit receive different content types from a wide variety of sources, including scanned images, pictures, voice, and video? Learn more. Governance and records controls:■Does the system create a governance framework and establish a shared workspace that protects sensitive content?■Are control tools linked automatically across classification, disposal, and security schema? Learn more.Content classification:■Does the system link content to the business process that created it, as well as create links between content that ispart of the same transaction sequence? Learn more.Disposal guidelines:■Does the system establish standards and guidelines for how content should be retained, how long, and which content should be destroyed or archived?■Does the system allow you to proactively manage the retention and disposal of all content under management (not just records)? Learn more.Security and access scheme:■Does the system create a governance framework and establish a shared workspace that protects sensitive content?■Does the system allow you to define individual privileges determining who has the authority to view, update, amend, or destroy a document or record and its properties? Learn more.Vocabulary controls:■Are there vocabulary controls in place to standardize on theuse of terminology for naming and improved content retrieval? Learn more. Use and tracking: Does the system manage user permissions, access controls, and security status? Can ittrack the use, location, and flow of content? Learn e and tracking:■Does the system manage user permissions, access controls, and security status?■Can it track the use, location, and flow of content? Learn more. Capture and registration:■Can the system define processes for incorporatingcontent created and received by an organization?■Does it record metadata about the content?■Can it assign a unique identifier to each content item, andassign attributes of the records control tools? Learn more.Implementation of disposal:■Does the system enable the routine disposal of content (including records), and provide documentation for audit purposes? Learn more.Storage and preservation:■Can the system preserve the accessibility and integrity of content and its metadata when stored, or when content is migratedor moved between systems? Learn more.Additional ConsiderationsEnterprise integration:■Is the system flexible and configurable enough to simplify implementation and ongoing management?■Does it offer sufficient room for growth to future proof digital records, and ensure the continuation of governance-based ECM? Learn more.Standards and standardization:■Can the system standardize on processes to ensurebusiness content is captured and managed appropriately,and enforce adherence to relevant standards? Learn more.Interoperability:■Can the system transfer and utilize information in a uniform and efficient manner across multiple organizations and systems, including electronic business, cross-agency systems, and Webenabled transactions?■Is it sufficiently equipped to move beyond these systems,as well as beyond organizational and jurisdictional boundaries? Learn more.Skill sets:■Does your organization possess the technology skills to understand network and bandwidth requirements, and the ability to manage system traffic issues?■Does it also possess the governance and recordkeeping skills needed to configure the internal components of the software? Learn more.Why Is Electronic Records Management Importantin ECM?Organizations that want to manage their content in accordance with their information governance strategy will recognize the importance of having an integrated approach to the management of working content and records. In many instances, this content will evolve into a record over time. Content, and therefore records, arises from business ac-tions, and thus needs to be managed in ways that preserve these links and enable organizations to make authoritative statements about its authenticity, reliability, integrity, and usability. This means that systems managing records must ensure that records are persistently linked with the business action and the people involved with that action.Figure 4. The information lifecycleEnterprise Content Management Core FunctionsAn ECM system can include elements of a number of disciplines, such as electronic records management; electronic document management; content management; content workflow; and image processing and ar-chiving. The challenge most organizations face is how to make informed decisions about the functionality they need, and then strategically se-lect the right technologies that will serve as a foundation for future growth as their needs become more sophisticated. T o build this foun-dation, it is vital that an organization understands the core functionality required of any governance based ECM regarding its business needs. These core functions include:■Contextual links among content, people, and process■Enterprise management through classification and linked disposal policies■Efficient management and linking of governance and recordkeeping controls for all content, not just records■Storage and preservation of digital content■Ability to maintain content processes and add layers of metadata■Access, security, and auditKey Elements and CapabilitiesAuthenticity, reliability, and integrity of content:As a discipline, recordkeeping is more interested in the circumstances that led to the creation of a record, and in maintaining the chain of cus-tody of that record. Information workers, on the other hand, are more interested in the content itself (whether it’s a finalized record or not) and in ensuring that it is authentic and accurate—particularly if they are re-sponding to a customer request. In a digital environment, content needs to be linked to metadata (i.e., information that systems use to index, describe, and find content) throughout the content lifecycle. Systems should have the ability to capture metadata and events to support the evolution of content into records. These characteristics provide authen-ticity, reliability, and integrity to governance-based ECM. However, the critical capability is whether a system can also understand the metadata needed for good governance and recordkeeping.Importance of metadata standards:The ISO metadata standard (ISO 23081-1:2006) establishes a bench-mark for interoperability between systems, as well as for defining meta-data in a consistent way so that data can be migrated between systems. An ECM should be capable of receiving content from a wide variety of sources, including scanned images, pictures, voice, and video. It should also be content-agnostic and independent of the source system, so when the source system changes or is replaced, records from the business process have been captured and can be viewed in a non-proprietary format.Governance and records controls:Records controls help create a governance framework and establish a shared workspace that protects sensitive content. Ideally, control tools are linked, so that by applying and using the classification scheme, the disposal and security schemes are automatically applied to content.Buyer’s GuideChoosing Governance-Based Enterprise Content ManagementContent classification:Classification confirms that a document o rrecord is part of a busi-ness process at a specific time and place,thus providing authenticity. It links the content to the business process that created it, as well as creates links and relationships between other content that is part of the same transaction.Disposal guidelines:Disposal guidelines standardize decision making about how content should be retained, for how long, and which content should be de-stroyed or archived. Retention and disposal should be based on policy that supports both regulatory and business requirements.Security and access scheme:A security and access scheme defines individual privileges to deter-mine who has the authority to view, update, amend, or destroy con-tent and its properties, as well as helps manage accessibility overtime. Contrary to popular belief, a well configured security scheme facilitates more open and flexible access to content.Vocabulary controls:Vocabulary controls help standardize the use of terminology for nam-ing—such as abbreviations, names of individuals, and business terms—for the purpose of improving content retrieval.Use and tracking:The ISO metadata standard (ISO 23081-1:2006) establishes a ben-chUse and tracking relate to the capacity of the ECM system to manage user permissions; access and security status; rights of people external to the organization to access content; and tracking the use, location, or flow of content and records with audit logs.Capture and registration:Capture and registration define the processes for incorporating con-tent created and received by an organization into the system; recording initial information (metadata) about the content (documents, email, or records); assigning each item a unique identifier; and assigning attri-butes of the governance and records control tools. Implementation of disposal:Functionality is needed to ensure that disposal occurs routinely. This process should be documented and authorized. Routine disposal of content is integral to successfully retiring legacy applications by mini-mizing the volume of content to be archived or migrated. The disposal process must be carried out according to defined policy and be docu-mented for audit purposes to ensure it is defensible.Storage and preservation:In a digital environment, storage and preservation relate to storing con-tent and records, linked to their metadata, in a way that preserves their accessibility and integrity, even when they are migrated or moved be-tween systems. Support for, and compliance with, standards such as the Victorian Electronic Records Strategy (VERS) facilitates the digital preservation and migration of electronic records.Additional ConsiderationsEnterprise integration:If the preservation and accurate accounting of business content and its associated metadata are the goals of ECM, then flexibility is the meansof accomplishing it. For a solution to be truly flexible, implemen-tations must consider multiple linkages with business software, which can involve content moving between the ECM application and business systems on a continuous basis. If this cannot be done with ease and certainty, organizations may be stuck with features that are in place within the ECM system. Thus, when integrating systems, organizations should understand the extent of integration possible and what func-tionality will be supported by connecting systems, and the availability of commercialized integration modules with major business applications. Standards and standardization:Given that an ECM system offers a near-infinite number of configura-tions, there is a risk that everyone will potentially do the same thing in different ways. The one-off nature of these random configurations means that finding congruence in metadata elements across multiple systems is a big challenge. Metadata alignment is critical to support in-teroperability. Businesses maydiscoverthattheirintegration and interop-erabilitybenefits diminish because of a poorly designed configuration. Here, organizations need to consider how they are going to adhere to relevant standards and how best to standardize processes within their organization to ensure business records and content are captured and managed appropriately. The ECM system selected must be designed to support relevant industry standards, making it easier to achieve a configurable but consistent approach to managing business content. Interoperability:Interoperability is the ability to transfer and use information in a uniform and efficient manner across multiple organizations and systems. As the demands for electronic business, cross-agency systems, and Web-enabled transactions grow, the capacity of the ECM system to meet user demands will not only affect the quality and coverage of content, but also may impair the operations of suppliers, customers, partners, and the wider community. Electronic content and records will therefore need to be well equipped to move beyond systems and across organi-zational and jurisdictional boundaries.Skill sets:Acquiring and configuring an ECM system takes a considerable degree of skill, including:■T echnology skills to understand network and bandwidth requirements, and the ability to manage the system traffic issues■Governance and recordkeeping skills needed toconfigure the internal components of the softwareIn the absence of such specialized skills, an organization may simply decide to apply the tenets of a paper-based system to an ECM system, which can negate the benefits of deploying such a system. Therefore, it is critical to take extra care when transitioning to an ECM system. Without the appropriate level of expert guidance, it is possible to not reap the full benefit of the new solution.ConclusionWhen assessing and choosing an ECM system, keep in mind a wide spectrum of considerations such as core functionality, capabilities, flex-ibility and ease of integration, and your business needs and objectives. The right ECM solution should not only deliver productivity improve-ments, increase compliance state, and reduce paper-related costs, but it should also enable and support the sustainability of records over time, thus minimizing longer-term risks. Configurable and flexible solutions that do not complicate implementation and systems management are critical requirements for future-proofing digital records, and ensuring the con-tinuation of governance-based best-practices content management. Additional ConsiderationsWhile this buyer’s guide provides valuable information on how to select an ECM system that will also enable best-practices information governance, it is by no means comprehensive. For additional information on how to choose the ECM system that’s right for your organization, please visit the resources listed below.Publications:Information Management—2016 and BeyondDisposing of Digital DebrisAIIM Industry Watch: Information Management State of the I ndustry 2016 Why is Information Governance too important to be left to humans?Online resources:AIIM resource centersWhite papers and free downloads from ARMA InternationalLearn more at/contentmgr/opentext。
sql server中openquery的用法 -回复
sql server中openquery的用法-回复SQL Server中的Openquery函数用于执行远程服务器上的查询，并将结果返回给本地服务器。
它可以用于在SQL Server中访问其他数据库服务器或链接服务器上的表。
一、Openquery函数的语法和参数在SQL Server中，Openquery函数的语法如下：OPENQUERY ( linked_server ,'query' )其中，linked_server是指其他数据库服务器或链接服务器的名称，而query是指在远程服务器上执行的查询语句。
Openquery函数只支持远程服务器的只读操作。
二、在SQL Server中创建链接服务器在使用Openquery函数之前，我们需要先在SQL Server中创建链接服务器。
链接服务器允许我们在SQL Server中访问其他数据库服务器或不同的数据库引擎（如Oracle或MySQL）。
创建链接服务器的步骤如下：1. 打开SQL Server Management Studio（SSMS）。
2. 在对象资源管理器中展开“服务器对象”-> “链接服务器”节点。
3. 右键单击“链接服务器”节点，然后选择“新建链接服务器”选项。
4. 在“常规”选项卡中，输入链接服务器的名称。
5. 在“服务器类型”下拉框中选择要链接的数据库类型。
6. 在“安全标识”选项卡中，选择使用固定的本地登录，并提供用于连接远程服务器的登录凭据。
7. 在“服务器选项”选项卡中，设置链接服务器的各种选项。
8. 单击“确定”按钮创建链接服务器。
三、使用Openquery函数执行远程查询在创建链接服务器后，我们可以使用Openquery函数在SQL Server中执行远程查询。
以下是使用Openquery函数执行远程查询的步骤：1. 打开SQL Server Management Studio（SSMS）。
2. 在查询编辑器中，编写查询语句，使用Openquery函数执行远程查询。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Product: Version:Task/Topic:Audience:Platform:Document ID:Updated:December 6, 2016Best Practices Microsoft® SQL Server for OpenText™ Content Server 16.x™OpenText Content Server16.xDeployment, Administration, Performance Administrators SQL Server 2012, 2014, 2016, and Azure 500287 John Postma, Director, Engineering ServicesRose Liang, Performance AnalystFeng Xiao, Senior Performance Engineering AnalystAmarendra Kondra, Performance ArchitectScott Tindal, Performance ArchitectMichelle Schwan, Database DeveloperOpenText Performance EngineeringContentsAudience (3)Executive Summary (4)Installation Options (5)SQL Server Setup Best Practices (6)Antivirus Software (6)Instant Database File Initialization (7)Lock Pages in Memory (8)Maximum Degree of Parallelism (MaxDOP) (9)Min and Max Memory (10)Perform Volume Maintenance Task (11)Storage Best Practices (11)tempDB Configuration (12)SQL Server Configuration Settings (13)Allocate Full Extent (13)AlwaysOn Availability Groups (13)Cost Threshold for Parallelism (14)Optimize for Ad hoc Workloads (15)Content Server Database Settings (16)Clustered Indexes (16)Collation (17)Compatibility Level (18)Data Compression (19)Database Data, Log File Size, and AutoGrowth (20)Recovery Model (21)Statistics (22)Table and Index Fragmentation, Fill factor (23)Monitoring and Benchmarking (24)Benchmark (24)SQL Server Performance Monitoring Tools (26)Activity Monitor (26)Azure Monitoring (26)Management Data Warehouse (27)Identifying Worst-Performing SQL (28)Content Server Connect Logs (28)SQL Server DMVs (28)Locking (29)Lock Escalation (29)Transaction Isolation (30)Appendices (31)Appendix A – References (31)Appendix B – Dynamic Management Views (DMVs) (32)Appendix C – Collation Fix (41)About OpenText (42)AudienceThe document is intended for a technical audience that is planning an implementation of OpenText™ products. OpenText recommends consulting with OpenText Professional Services who can assist with the specific details of individual implementation architectures.DisclaimerThe tests and results described in this document apply only to the OpenText configuration described herein. For testing or certification of other configurations, contact OpenText Corporation for more information.All tests described in this document were run on equipment located in the OpenText Performance Laboratory and were performed by the OpenText Performance Engineering Group. Note that using a configuration similar to that described in this document, or any other certified configuration, does not guarantee the results documented herein. There may be parameters or variables that were not contemplated during these performance tests that could affect results in other test environments.For any OpenText production deployment, OpenText recommends a rigorous performance evaluation of the specific environment and applications to ensure that there are no configuration or custom development bottlenecks present that hinder overall performance.NOTE: This document is specific to OpenText Content Server 16.If your environment has OpenText Content Server 10.5 deployed,refer to:Best Practices – SQL Server for OpenText Content Server 10.5Executive SummaryThis white paper is intended to explore aspects of Microsoft® SQL Server that may be of value when configuring and scaling OpenText Content Server™ 16.x. It is relevant to SQL Server Azure, 2016, 2014, and 2012, and is based on customer experiences, performance lab tests with a typical document management workload, and technical advisements from Microsoft.Most common performance issues can be solved by ensuring that the hardware used to deploy SQL Server has sufficient CPU, RAM and fast I/O devices, properly balanced.These topics explore non-default options available when simple expansion of resources is ineffective, and discuss best practices for administration of Content Server’s database. It concentrates on non-default options, because as a recommended starting point, Content Server on SQL Server installations uses Microsoft’s default deployment options. Usage profiles vary widely, so any actions taken based on topics discussed in this paper must be verified in your own environment prior to production deployment, and a rollback plan must be available in case adverse effects are detected.These recommendations are not intended to replace the services of an experienced and trained SQL Server database administrator (DBA), and do not cover standard operational procedures for SQL Server database maintenance, but rather offer advice specific to Content Server on the SQL Server platform.Installation OptionsConsider the following tips and guidelines when initially installing Content Server for use with SQL Server:•For Microsoft Azure SQL Databaseso The database must be created in the Microsoft Azure Portal (see article Azure Create a SQL Database)o Use the configuration options in SQL Server Configuration Settings and Content Server Database Settingso The database can be managed in SQL Server Management Studio (SSMS) 2016 and in the Microsoft Azure Portal•For SQL Server 2016 and priorCore Content Server database is installed using the sa SQL Server account. For SQL Server DBAs who are uncomfortable allowing an application to log into the SQL Server database with the sa account, the SQL Server DBA can create the database and user account.To create the database, the SQL Server DBA can follow the SQL command below or can use SQL Server Management Studio (SSMS) to create the database GUI. Please note: database name, name, filename, size and password should be altered.CREATE DATABASE dbnameON PRIMARY (NAME = 'livelink_Data',FILENAME = 'C:\databases\dbname.mdf', SIZE = 5MB,MAXSIZE = UNLIMITED)LOG ON (NAME = ‘livelink_Log',FILENAME = 'C:\databases\dbname.ldf', SIZE = 1MB,MAXSIZE = UNLIMITED)Apply database configurations.ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ONALTER DATABASE dbname SET RECOVERY FULL (need to set directories – SIMPLE is Content Server default setting)ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 110Create user account.CREATE LOGIN dbuser WITH PASSWORD = 'pwd',DEFAULT_DATABASE=dbnameUse the configuration options in SQL Server Configuration Settings and Content Server Database Settings.SQL Server Setup Best PracticesOpenText recommends that you install and configure SQL Server following Microsoft’s recommendations for best performance. This section covers many SQL Server settings, and refers to Microsoft documentation where applicable.In addition to configuring the settings described in this section, OpenText recommends that you install the latest SQL Server Service Pack that is supported by your Content Server Update level (see Content Server Release Notes).Some of these settings may be version specific.Antivirus SoftwareVersions SQL Server 2012.Description Antivirus software scans files and monitors activity to prevent,detect, and remove malicious software. Guidelines for antivirussoftware configuration are provided in the Microsoft support article:How to choose antivirus software to run on computers that arerunning SQL Server.Default Depends on vendor.Recommendation Exclude all database data and log files from scanning (includingtempDB). Exclude SQL Server engine process from activemonitoring.Notes Follow the Microsoft support article for SQL Server version-specificdetails.Instant Database File InitializationVersions SQL Server 2012, 2014 and 2016.Description Allows faster creation and auto-growth of database and log files bynot filling reclaimed disk space with zeroes before use. See thearticle, Database Instant File Initialization.Default Not enabled.Recommendation Enable this feature by assigning the SE_MANAGE_VOLUME_NAMEuser right to the SQL Server service account. (It appears asPerform Volume Maintenance Tasks in the Local Security Policytool User Rights Assignment list.)Notes Microsoft states that, because deleted disk data is overwritten onlywhen data is written to files, an unauthorized principal who gainsaccess to data files or backups may be able to access the deletedcontent. Ensure that access to these files is secured, or disable thissetting when potential security concerns outweigh the performancebenefit.If the database has Transparent Data Encryption enabled, itcannot use instant initialization.Permissions To set this for the SQL Server service, you must have administrative rights on the Windows server.Lock Pages in MemoryVersions SQL Server 2012, 2014 and 2016.Description Memory for the buffer pool is allocated in a way that makes it non-page-able, avoiding delays that can occur when information has tobe loaded from the page file. See the article: Enable the LockPages in Memory Option.Default Not set.Recommendation Enable this feature by assigning the Lock pages in memory to theSQL Server Service account in the User Rights Assignment folderin Windows Security Local Policies.If you enable this setting, be sure to also set max memoryappropriately to leave sufficient memory for the operating systemand other background services.Notes If SQL Server is running in a virtual environment, be aware that, ifmax memory is set too high, there is a potential for memoryovercommits. This can expand and decrease available memory,leading to memory pressure and potential problems.Permissions To set this for the SQL Server service, you must have administrative rights on the Windows server.Maximum Degree of Parallelism (MaxDOP)Description MaxDOP controls the maximum number of processors that areused for the execution of a query in a parallel plan. See the article,Configure the max degree of parallelism .Parallelism is often beneficial for longer-running queries or forqueries that have complicated execution plans. However, OLTP-centric application performance can suffer, especially on higher-endservers, when the time that it takes SQL Server to coordinate aparallel plan outweighs the advantages of using one.Default0 (unlimited) Recommendation Consider modifying the default value when SQL Server experiencesexcessive CXPACKET wait types.For non-NUMA servers, set MaxDOP no higher than the number ofphysical cores, to a maximum of 8.For NUMA servers, set MaxDOP to the number of physical coresper NUMA node, to a maximum of 8.Note: Non-uniform memory access (NUMA) is a processorarchitecture that divides system memory into sections that areassociated with sets of processors (called NUMA nodes). It ismeant to alleviate the memory-access bottlenecks that areassociated with SMP designs. A side effect of this approach is thateach node can access its local memory more quickly than it canaccess memory on remote nodes, so you can improve performanceby ensuring that threads run on the same NUMA node.Also see the Cost Threshold for Parallelism section for relatedsettings that restrict when parallelism is used, to allow bestperformance with Content Server.Note : Any value that you consider using should be thoroughlytested against the specific application activity or pattern of queriesbefore you implement that value on a production server.NotesSeveral factors can limit the number of processors that SQL Server will utilize, including: • licensing limits related to the SQL Server edition • custom processor affinity settings and limits defined in aResource Governor poolThese factors may require you to adjust the recommendedMaxDOP setting. See related reference items in Appendix A –References for background information.See Appendix B – Dynamic Management Views (DMVs) forexamples of monitoring SQL Server wait types.PermissionsChanging this setting requires the ALTER SETTINGS server-levelpermission.Min and Max MemoryVersions SQL Server 2012, 2014 and 2016Description The min server memory and max server memory settingsconfigure the amount of memory that is managed by the SQLServer Memory Manager. SQL Server will not release memorybelow the min. memory setting, and will not allocate more than themax memory while it runs. See the article, Server MemoryConfiguration Options.Default The default setting for min server memory is 0, and the defaultsetting for max server memory is 2,147,483,647 MB. SQLServer dynamically determines how much memory it will use, basedon current activity and available memory.Recommendation On a server dedicated to a single SQL Server instance, leavingSQL Server to dynamically manage its memory usage can providethe best results over time, but min and max memory should be setwhen:•The Lock pages in memory setting is enabled. You shouldset the max memory to a value that leaves sufficientmemory for the parts of SQL Server that are not includedin the max server memory setting (thread stacks,extended SPs, and so on), the operating system, and otherservices.•More than one SQL Server instance, or other services, arehosted on the server. Setting max memory for eachinstance will ensure balanced memory use.•Memory pressure could cause SQL Server memory use todrop to a level that affects performance. Set minimummemory to a value that maintains stable performance.Notes Monitor available memory, and adjust as needed.SQL Server is not guaranteed to allocate the amount of memoryspecified in min server memory. If the load on the server neverrequires allocating the amount of memory specified in min servermemory, SQL Server will run with less memory.Permissions Changing this setting requires the ALTER SETTINGS server-levelpermission.Perform Volume Maintenance TaskVersion SQL Server 2016.Description This is a replacement for Instant Database File Initialization. It cannow be handled on the install of SQL Server. For more informationsee the MSDN article Instant File Initialization.Default Not enabled.Permissions To set this for the SQL Server service, you must have administrative rights on the Windows server.Storage Best PracticesDescription SQL Server is an I/O-intensive application. Proper configuration ofI/O subsystems is critical to achieve optimal performance.With the wide variety of storage types available, it is difficult to makespecific recommendations. Microsoft provides some guidelines in aHardware and Software Requirements and Azure Performance BestPractices.For the purposes of characterizing expected I/O patterns, ContentServer is primarily an OLTP-type application.This section covers a few specific topics related to storage, but it isnot meant to be a comprehensive guide for storage planning. Default Windows NTFS default cluster size is 4 KB.Recommendation Microsoft recommends a cluster size of 64 KB for partitions thathouse SQL Server data, log, and tempDB files.Transaction log and tempDB data files have the most impact onquery performance, so Microsoft recommends placing them onRAID 10 storage. (This provides the best performance compared toother RAID levels that provide data protection.)If you use a SAN, increase the host bus adapter (HBA) queue depthas needed to support the amount of IOPS generated by SQLServer.Use a tool such as the DiskSpd Utility to benchmark andunderstand the I/O characteristics of available storage, and to aid inplanning the location of the Content Server database, transactionlog, and tempDB files.Notes Also, see the sections on tempDB Configuration, Database Data,Log File Size, and AutoGrowth for other recommendations relatedto data and log files.tempDB ConfigurationVersions SQL Server 2012, 2014 and 2016.Description The tempDB is a global resource that stores user objects (such astemp tables), internal objects (such as work tables, work files,intermediate results for large sorts and index builds). Whensnapshot isolation is used, the tempDB stores the before images ofblocks that are being modified, to allow for row versioning andconsistent committed read access.Default Single data file.Recommendation The tempDB has a large impact on Content Server performance.Follow these guidelines for best results:•Create one data file per physical core, up to a maximum ofeight. If the tempDB continues to have memorycontention, add four files at a time (up to the total numberof logical processors).•Make each data file the same size. Each one should belarge enough to accommodate a typical workload. (As ageneral rule, set it to one-and-a-half times the size of thelargest single table in any database used by the instance.)Allow auto-growth to accommodate usage spikes.•Place these files on your fastest available storage.•Ensure that the tempDB has the same collation as theContent Server database.Notes Be mindful of factors that can limit the number of processors SQLServer will utilize. Appropriately set the number of tempDB files. Monitoring Monitor latch waits related to pages in tempDB. PAGELATCH_XXwait types can indicate tempDB contention. Appendix B – DynamicManagement Views (DMVs) provides sample queries that you canuse to monitor waits and identify active tasks that are blocked ontempDB.Monitor the space used in and the growth of tempDB, and adjusttempDB size as needed.Permissions Adding or modifying tempDB data files requires the ALTERpermission on the tempDB database.SQL Server Configuration SettingsGlobal server settings that affect all databases on an instance.Allocate Full ExtentDescription Trace flag 1118 enables SQL Server to allocate a full extent to eachdatabase object. Rather than one page at a time, which can reducecontention on SGAM pages.In SQL Server 2016 and Azure, this can be handled via thefollowing ALTER commands. See the article, Changes in defaultbehavior:ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION ONALTER DATABASE <dbname> MODIFY FILE GROUP<filegroupname>AUTOGROW_ALL_FILESSee the article: Recommendations to reduce allocation contentionin SQL Server tempDB database.Default Not enabledRecommendation Consider enabling this flag if latch waits on pages in tempDB causelong delays that are not resolved by the recommendations in thetempDB section.AlwaysOn Availability GroupsDescription AlwaysOn Availability Groups are a high-availability anddisaster recovery solution that support a failover environment for aset of user databases. See the article, AlwaysOn AvailabilityGroups.Default DisabledRecommendation Content Server supports SQL Server 2014 AlwaysOn solution withthe ODBC Driver 11 for SQL Server. Follow the documentationfrom Microsoft to configure Always On Availability Groups. TheContent Server system should connect to the availability grouplistener (virtual IP).Cost Threshold for ParallelismDescription The Cost Threshold for Parallelism is the threshold at which SQLServer will create and run a parallel plan for a query. If theestimated cost for a serial plan is higher than this value, SQL Serveruses a parallel plan. This setting is ignored and a serial plan isalways used if:•the server has only one processor•affinity settings limit SQL Server to one processor•MaxDOP is set to 1Default 5Recommendation Content Server mainly issues small OLTP-type queries where theoverhead of parallelism outweighs the benefit, but it does issue asmall number of longer queries that may run faster with parallelism.OpenText recommends that you increase the cost threshold settingin combination with configuring the Maximum Degree of Parallelism(MaxDOP) setting. This reduces the overhead for smaller queries,while still allowing longer queries to benefit from parallelism. Seethe article, Configure the cost threshold.The optimal value depends on a variety of factors includinghardware capability and load level. Load tests in the OpenTextperformance lab achieved improved results with a Cost Thresholdof 50, which is a reasonable starting point. Monitor the following andadjust the cost threshold as needed:•CXPACKET wait type: When a parallel plan is used for aquery there is some overhead coordinating the threadsthat are tracked under the CXPACKET wait. It is normal tohave some CXPACKET waits when parallel plans are used.However, if it is one of the highest wait types, furtherchanges to this setting may be warranted. See AppendixB – Dynamic Management Views (DMVs) for examples ofquerying DMVs for wait info.Appendix B – Dynamic Management Views (DMVs)• Seefor examples of querying DMVs for queries usingParallelism.•THREADPOOL wait type: If many queries are using aparallel plan, there can be periods when SQL Server usesall of its available worker threads. Time spent by a querywaiting for an available worker thread is tracked under theTHREADPOOL wait type. If this is one of the highest waittypes, it may be an indication that too many queries areusing parallel plans. In this case, the cost threshold forparallelism should be increased, or maximum workerthreads increased if the system is not experiencing CPUpressure. However, there can be other causes for anincrease in this wait type (blocked or long running queries),so it should only be considered in combination with a morecomprehensive view of query performance and locking. Permissions Changing this setting requires the ALTER SETTINGS server-levelpermission.Optimize for Ad hoc WorkloadsDescription The ad hoc caching mechanism can reduce stress on memory-bound systems. It caches a stub of the query plan, and stores thefull plan only if a query is issued more than once. This prevents thecache from being dominated by plans that are not reused, freeingspace for more frequently accessed plans.Turning this on does not affect plans already in the cache, only newplans created after enabling the setting. For more details, seearticle Optimize for ad hoc workloads.Default OffRecommendation When there is memory pressure, and the plan cache contains asignificant number of single-use plans, enable this setting. Monitoring Check the portion of the plan cache used by single use queries:see Appendix B – Dynamic Management Views (DMVs) CachedQuery Plans.Content Server Database SettingsThese settings are specific to the Content Server database.Please give notice to the section on Compatibility Level. This will affect the performance of Content Server on SQL Server 2014 and later.Clustered IndexesDescription Clustered indexes store data rows for the index columns in sortedorder. In general, the primary key or the most frequently used indexon each table is a good candidate for a clustered index. This isespecially important for key highly-active core tables. Only oneclustered index can be defined per table.Default In Content Server 10.5 and later, many tables in the Content Serverdatabase have a clustered index.Recommendation OpenText does not recommend making schema changes such asadding clustered indexes to tables in the Content Server database.Additional clustered indexes may be added in future releases ofContent Server.Notes One benefit of clustered indexes is to avoid potential blocking by theghost record cleanup process when there is a high volume ofdeletes (such as with Records Management Disposition). Withoutclustered indexes, the cleanup process may require a table lock toscan for ghost records, blocking other operations.There are other situations where the ghost record cleanup processmight fall behind the rate of row deletions. You can monitor thisusing the sys.dm_db_index_physical_stats (see Appendix B– Dynamic Management Views (DMVs)). Review columnsGhost_Record_Count (ghost records ready for cleanup) andVersion_Ghost_Record_Count (ghost records retained by anoutstanding snapshot isolation transaction).CollationDescription The collation for a database defines the language and character setused to store data, sets rules for sorting and comparing characters.It also determines case-sensitivity, accent-sensitivity, and kana-sensitivity.Default When SQL Server is installed, it derives its default server-levelcollation from the Windows system locale. See article, Collationand Unicode Support.The default collation for a new database is the same as the SQLServer default setting. It is inherited from the MODEL database,where it cannot be changed.Databases restored from a backup originating from another serverwill retain their original collation.Recommendation To avoid potential issues, provide best performance, and ensurecompatibility with other products on the same server, OpenTextrecommends the following:•For new SQL Server installations, select a collation that iscase-sensitive and accent-sensitive for compatibility withother suite products (such as Archive Server).•Ensure that the Content Server database has the samecollation as the server (and hence the same as systemdatabases like tempDB).•Ensure that the collation for all objects in the ContentServer database are the same as the database collation.The exception to this is the WebNodesMeta_XX tables,which may derive a different collation from settings on theConfigure Multilingual Metadata administration page.•Contact customer support for assistance if you have anexisting deployment that has a database collation that isdifferent from the server’s, or that has tables or columns(other than WebNodesMeta_XX tables) that have acollation that is different from the database collation. Notes Refer to scripts in SQL Server and Database Information QueriesSQL Server and Database Information Queries for databasecollation and column collation that differs from database.Refer to Appendix C – Collation Fix on suggestions to fix collationissues.Compatibility LevelDescriptionThe database compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server.Default The compatibility level for newly created databases is the same as the MODEL database which, by default, is the same as the installedversion of SQL Server.When upgrading the database engine, compatibility level for userdatabases is not altered, unless it is lower than the minimumsupported. Restoring a database backup to a newer version alsodoes not change its compatibility level.Recommendation In general, using the latest compatibility mode allows the Content Server database to benefit from all performance improvements inthe installed SQL Server version.However, because of performance issues with SQL Server 2014and later, set the Compatibility Level to the version of SQL Server2012 (see article Alter Database Compatibility Level).ALTER DATABASE <dbname> SET COMPATIBILITY_LEVEL = 110When you change the compatibility level of the Content Serverdatabase, be sure to update statistics on the database after makingthe change.As an alternative, you can use trace flag 9481 and leave thecompatibility level unchanged. For more details, see TechnicalAlert.。