Synchronizes a Microsoft SQL Server Analysis Services database with another existing database.

Synchronize Element (XMLA)

Other Versions

Syntax

Xml

<Command>
   <Synchronize>
      <Source>...</Source>
      <SynchronizeSecurity>...</SynchronizeSecurity>
      <ApplyCompression>...</ApplyCompression>
      <Locations>...</Locations>
   </Synchronize>
</Command>

Element Characteristics

Characteristic	Description
Data type and length	None
Default value	None
Cardinality	0-n: Optional element that can occur more than once.

Element Relationships

Relationship	Element
Parent elements	Command
Child elements	ApplyCompression, Locations, Source, SynchronizeSecurity

Remarks

The Synchronize command synchronizes the target database with a source instance and database specified in the Source element. Optionally, the Synchronizecommand synchronizes remote partitions defined on the source database.

Depending on the storage mode used by objects stored in the backup file, the Synchronize command synchronizes information as listed in the following table.

Storage mode	Information
Multidimensional OLAP (MOLAP)	Source data, aggregations, and metadata
Hybrid OLAP (HOLAP)	Aggregations and metadata
Relational OLAP (ROLAP)	Metadata

During a Synchronize command, a read lock is placed on the source database and a write lock is placed on the target database. Both locks are released after theSynchronize command has completed.

For more information about synchronizing databases, see Backing Up, Restoring, and Synchronizing Databases (XMLA).

Handling Errors and Warnings (XMLA)

Other Versions

Error handling is required when an XML for Analysis (XMLA) Discover or Execute method call does not run, runs successfully but generates errors or warnings, or runs successfully but returns results that contain errors.

SSAS MSMDSRV.INI Settings for Multi-User Environments

SSAS MSMDSRV.INI Settings for Multi-User Environments

change text size: A A A

• Comments (0)

posted 7/2/2013  by MMilligan -  Views: [4869] 

Here are excerpts from the SSAS Operations Guide for SQL Server 2008 R2 regarding the recommended settings for a multi-user environment.  If you want more information you must read the whitepaper.

-------------------------------------------------------------------------------

2.3.2.4    Heap Settings, DataStorePageSize, and DataStoreHashPageSize During query execution, Analysis Services generally touches a lot of data and performs many memory allocations. Analysis Services has historically relied on its own heap implementation to achieve the best performance. However, since Windows Server 2003, advances in the Windows Server operating system mean that memory can now be managed more efficiently by the operating system. This turns out to be especially true for multi-user scenarios. Because of this, servers that have many users should generally apply the following changes to the msmdsrv.ini file.

Setting	Default	Multi-user, faster heap
<MemoryHeapType>	1	2
<HeapTypeForObjects>	1	0

It is also possible to increase the page size that is used for managing the hash tables Analysis Services uses to look up values. Especially on modern hardware, we have seen the following change yield a significant increase in throughput during query execution.

Setting	Default	Bigger pages
<DataStorePageSize>	8192	65536
<DataStoreHashPageSize>	8192	65536

-------------------------------------------------------------------------------

2.3.2.5    Clean Up Idle Sessions Client tools may not always clean up sessions opened on the server. The memory consumed by active sessions is non-shrinkable, and hence is not returned the Analysis Services or operating system process for other purposes. After a session has been idle for some time, Analysis Services considers the session expired and move the memory used to the shrinkable memory space. But the session still consumes memory until it is cleaned up. 
Because idle sessions consume valuable memory, you may want to configure the server to be more aggressive about cleaning up idle sessions. There are some msmdsrv.ini settings that control how Analysis Services behaves with respect to idle sessions. Note that a value of zero for any of the following settings means that the sessions or connection is kept alive indefinitely.

Setting	Description
<MinIdleSessionTimeOut>	This is the minimum amount of time a session is allowed to be idle before Analysis Services considers it ready to destroy. Sessions are destroyed only if there is memory pressure.
<MaxIdleSessionTimeout>	This is the time after which the server forcibly destroys the session, regardless of memory pressure.
<IdleOrphanSessionTimeout>	This is the timeout that controls sessions that no longer have a connection associated with them. Examples of these are users running a query and then disconnecting from the server.
<IdleConnectionTimeout>	This timeout controls how long a connection can be kept open and idle until Analysis Services destroys it. Note that if the connection has no active sessions, MaxIdleSessionTimeout eventually marks the session for cleaning and the connection is cleaned with it.

If your server is under memory pressure and has many users connected, but few executing queries, you should consider lowering MinIdleSessionTimeOut and IdleOrphanSessionTimeout to clean up idle sessions faster.

-------------------------------------------------------------------------------

2.4.3    Multi-User Process Pool Settings 
In multi-user scenarios, long-running queries can starve other queries; specifically they can consume all available threads, blocking execution of other queries until the longer-running queries complete. 
You can reduce the aggressiveness of how each coordinator job allocates threads per segment by modifying CoordinatorQueryBalancingFactor and CoordinatorQueryBoostPriorityLevel as follows.

Setting	Default	Multi-user nonblocking settings
CoordinatorQueryBalancingFactor	-1	1
CoordinatorQueryBoostPriorityLeve	3	0

-------------------------------------------------------------------------------

If your SSAS instance shares a server with anything else you may also want to read my good buddy Jorge's blog post: Setting Memory Limits. 

I have been setting memory\lowmemoryLimit to 45, and Memory\TotalMemoryLimit to 60. 

You can also take advantage of shared memory when the Analysis Services instance is on the same server as the relational data warehouse instance.  More information here:  Analysis Services: 2 Quick Tweaks to Speed Up Cube Processing

-------------------------------------------------------------------------------

Did I miss any?  Do you have any additional tips to share?  If so, please sound off in the comments.

Log operations in Analysis Services

Location and types of logs

Analysis Services provides the logs described below.

File name or Location	Type	Used for	On by default
Msmdsrv.log	Error log	Routine monitoring and basic troubleshooting	Yes
OlapQueryLog table in a relational database	Query log	Collect inputs for the Usage Optimization Wizard	No
SQLDmp<guid>.mdmp files	Crashes and exceptions	Deep troubleshooting	No

We highly recommend the following link for additional information resources not covered in this topic: Initial data collection tips from Microsoft Support.

See Configuring the Analysis Services Query Log to learn more about query log configuration. Although the paper is quite old, query log configuration has not changed in recent releases and the information it contains still applies.

Mini dump (.mdmp) files

Dump files capture data used for analyzing extraordinary events. Analysis Services automatically generates mini dumps (.mdmp) in response to a server crash, exception, and some configuration errors. The feature is enabled, but does not send crash reports automatically.

Crash reports are configured through the Exception section in the Msmdsrv.ini file. These settings control memory dump file generation. The following snippet shows the default values:

<Exception>
<CreateAndSendCrashReports>1</CreateAndSendCrashReports>
<CrashReportsFolder/>
<SQLDumperFlagsOn>0x0</SQLDumperFlagsOn>
<SQLDumperFlagsOff>0x0</SQLDumperFlagsOff>
<MiniDumpFlagsOn>0x0</MiniDumpFlagsOn>
<MiniDumpFlagsOff>0x0</MiniDumpFlagsOff>
<MinidumpErrorList>0xC1000000, 0xC1000001, 0xC102003F, 0xC1360054, 0xC1360055</MinidumpErrorList>
<ExceptionHandlingMode>0</ExceptionHandlingMode>
<CriticalErrorHandling>1</CriticalErrorHandling>
<MaxExceptions>500</MaxExceptions>
<MaxDuplicateDumps>1</MaxDuplicateDumps>
</Exception>

Configure Crash Reports

Unless otherwise directed by Microsoft Support, most administrators use the default settings. This older KB article is still used to provide instruction on how to configure dump files: How to configure Analysis Services to generate memory dump files.

The configuration setting most likely to be modified is the CreateAndSendCrashReports setting used to determine whether a memory dump file will be generated.

Value	Description
0	Turns off the memory dump file. All other settings under the Exception section are ignored.
1	(Default) Enables, but does not send, the memory dump file.
2	Enables and automatically sends an error report to Microsoft.

CrashReportsFolder is the location of the dump files. By default, an .mdmp file and associated log records can be found in the \Olap\Log folder.

SQLDumperFlagsOn is used to generate a full dump. By default, full dumps are not enabled. You can set this property to 0x34.

The following links provide more background:

• Looking Deeper into SQL Server using Minidumps
• How to create a user mode dump file
• How to use the Sqldumper.exe utility to generate a dump file in SQL Server
• 
  

Msmdsrv.exe is ssas service BI Quick Questions:

BI Quick Questions:

——>

SSAS Configuration File

1)       What is the name of SSAS configuration file?

Answer: Msmdsrv.ini and its default location is (?:\Program Files\Microsoft SQL Server\MSAS??.Instance_Name\OLAP\Config)

2)      How to find path of configuration file, if SSAS is not installed using default paths?

Answer: Steps as follow:-

a)      Go to Services Console (Services.msc)

b)      Find SSAS Service

c)       Right click SSAS Service and go to its properties

d)       In General  section look for Path to executable

e)      Below Path to executable you will find complete path which looks something like this for default configuration –

“C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\bin\msmdsrv.exe” -s “C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Config”

f)        Second part of the Path got location of Config directory where you will find msmdsrv.ini file

3)      How to rebuild SSAS msmdsrv.ini (Configuration) file?

Answer: Steps as follow:-

Steps as follow:-

a) STOP SQL Analysis Services

b) Go to SSAS Config folder (Default Path – ?:\Program Files\Microsoft SQL Server\MSAS??.Instance_Name\OLAP\Config)

c) In config folder you will find two files msmdsrv.ini and msmdsrv.bak, move these two files in any temporary folder

d) Start SSAS

e) After starting SSAS service you will find copy of newly created msmdsrv.ini and msmdsrv.bakfiles in config folder

f) File is created with default settings for all properties

e) To start with you can provide right path of Data, Teamp, Log and Backup Directory, otherwise root directly will be used for these folders. In a newly created msmdsrv.ini file, path looks something like this – <DataDir>\data</DataDir> which means use root directory for creating Data Folder

<——

SSAS 2008, 2012 SQL Server Analysis Services - Performance optimization msmdsrv.ini

Here are some real life values from testing in a customer Environment that helps you improving query, processing Performance of SSAS Cubes. We build 3 different classes of Servertype's each is dedicated for SSAS Service without any other app on it. There is a class for developers and Designers (design) optimized for both query and processing and one class (Build) for processing the cubes (on example a monthly Basis) another one for enduser Access and BI Frontendtools optimized on reading with multiple users. Testing was done on HP DL385 with 2x12 Core AMD CPU's and 128 GB RAM. Performance improved from the original Settings to theses with factor 2-4.

	Design	Build	Frontend- useraccess
Msmdsrv.ini config directory of SSAS (don’t miss to make a copy before editing)
Query: MaxThreads	48 (2 pro Core)	48 (2 pro Core)	48 (2 pro Core)
Process: MaxThreads	120 (5 pro Core)	288 (12 pro Core)	120 (5 pro Core)
Memory: MemoryHeapType	2	2	2
Memory: HeapTypeForObjects	0	0	0
Memory: LowMemoryLimit	35	65 %	35
Memory: PreAllocate	25	30	25
Memory: TotalMemoryLimit	80 % (in Byte )	Ca 80 % (in Byte e.g 100 GB = 107374182400 byte)	80
CoordinatorExecutionMode	-8	-8	-8
Process:BufferMemoryLimit	30 % (in Byte angeben)	Ca.30 % (in 32212254720 byte)	60 (default)
Query: DataStorePageSize	65536	65536	65536
Query: DataStoreHashPageSize	65536	65536	65536
CoordinatorQueryMaxThreads	-6	-6	-8
CoordinatorBuildMaxThreads	6	6	6
LimitSystemFileCache	16384 (MB)	16384 (MB)	16384 (MB)
CoordinatorQueryBalancingFactor	-1 (default)	-1 (default)	1
CoordinatorQueryBoostPriorityLevel	3 (default)	3 (default)	0
OLAP Process: AggregationMemoryLimitMin	4 GB (in Byte )	4 GB (in Byte 4294967296 byte)	4 GB (in Byte )
OLAP Process: AggregationMemoryLimitMax	20 GB (in Byte )	20 GB (in Byte 21474836480 byte)	20 GB (in Byte )

Настройка свойств сервера в службах Analysis Services

msmdsrv ini configuration analyzer