Hi all,
I would like to share with you an issue that I got recently because I didn't set up correctly AlwaysOn to enable readable database on the secondary replica.
Problem description: When I am trying to connect to my application with .Net or PowerShell, I got the following issue only when I add ApplicationIntent=Readonly on my connection string:
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: No connection could be made because the target machine actively refused it..Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information seeSQL Server Books Online..
Reproduction: I am able to reproduce with SQL CMD: sqlcmd -S myListener -E -K ReadOnly -Q "SELECT @@SERVERNAME" However, when I remove -K ReadOnly, it works fine.
Other interesting stuff, I can see a login/logout on my primary server inside the profiler trace, but nothing on the secondary
Solution: In my case, the issue was that the read_only_routing port mismatch with the port of my instance. I enclose below what you have to look.
How configure correctly AlwaysOn readable secondary replica:
1. Add Application Intent in the connection string by adding: ;ApplicationIntent=ReadOnly2. ALLOW_CONNECTIONS property of the secondary replica is READ_ONLY.
3. The READ_ONLY_ROUTING_URL for each replica is set. The TCP port is a port number that is used by the Database Engine of the SQL Server instance
alteravailabilitygroupmyGroupNamemodifyreplicaon'myMachine1\instanceName1'with(secondary_role(read_only_routing_url='tcp://myMachine1:1433'))
GO
alteravailabilitygroupmyGroupNamemodifyreplicaon'myMachine2\instanceName2'with(secondary_role(read_only_routing_url='TCP://myMachine2:1433'))
4. The READ_ONLY_ROUTING_LIST option must be set for each replica in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP Transact-SQL command, as part of the PRIMARY_ROLE replica options. The READ_ONLY_ROUTING_LIST can contain one or more routing targets. Multiple routing targets can be configured. Routing occurs in the order that targets are specified in routing list.
alteravailabilitygroupmyGroupNamemodifyreplicaon'myMachine1\instanceName1'with(primary_role(read_only_routing_list=('myMachine2\instanceName2')))
alteravailabilitygroupmyGroupNamemodifyreplicaon'myMachine2\instanceName2'with(primary_role(read_only_routing_list=('myMachine1\instanceName1')))
Select Replica_Server_name,Endpoint_url,Secondary_Role_allow_Connections_Desc,Backup_Priority, Read_only_Routing_URLfrom Sys.AVailability_replicas
selectdns_name,port,is_conformant,ip_configuration_string_from_cluster FROM sys.availability_group_listeners
5. Then I installed the both KB to fix this issue System.ArgumentException: Keyword not supported: 'applicationintent'.
6. To enable Kerberos, I configured in Active Directory by a domain administrator for each availability group listener name
Configure SPN in AD for each AG Listener name - A Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group. (http://msdn.microsoft.com/en-us/library/hh213417.aspx#SPNs)
setspn -A MSSQLSvc/myListenerName.FQDN:1433 myDomain\myserviceAccount
7. Configuring the Windows Firewall to Allow SQL Server Access
If you got any connectivity issues, I invite you to read: How to resolve SQL Server connectivity issues
Regards,
Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |
Problem description: I got a specific scenario where I have to failover two or more AlwaysOn Availability Groups in the same time. To explain by another way, I have to create a dependency between 2 Availability Groups. If one of them failover, the second one has to move also on the same node.
In my case, I have to do that because, I’m not allow to put in the same group all databases. And a third application has to perform a query (write/read) between N databases from two different 2 Availability Groups.
Action plan: As it’s not a normal or expecting behavior, I will share with you, how I did to perform this task.
Step 0: Copy past the script PowerShell below on all nodes involved (SQL_Node_1 and SQL_Node_2 and SQL_Node_3) inside C:\HADRON\failoverAutomaticHadronGroup.ps1
import-modulefailoverclusters$Cluster_Name = "myWindowsClusterName";
$my_AG1_Name ="AG1";$my_AG2_Name ="AG2";
# We are looking for the current node of my_AG1_Name$AG_1 =get-clustergroup -name$my_AG1_Name-Cluster$ClusterName|select-object OwnerNode,State
# then We are looking for the current node of my_AG2_Name$AG_2=get-clustergroup -name"IngestPub"-Cluster$my_AG2_Name|select-object OwnerNode,State
if($AG_1.OwnerNode.Name -ne$currentTriggerNode -and$AG_2.OwnerNode.Name -eq$currentTriggerNode ){ # so we move the group AG_1 to the same node of AG2 Move-ClusterGroup"$my_AG1_Name"–Node$AG_2.OwnerNode.Name -Cluster$ClusterName ;}elseif ($AG_2.OwnerNode.Name -ne$currentTriggerNode -and$AG_1.OwnerNode.Name -eq$currentTriggerNode ){ # so we move the group AG_2 to the same node of IngestPub Move-ClusterGroup"$my_AG2_Name"–Node$AG_1.OwnerNode.Name -Cluster$ClusterName}
Step 1: Open eventvwr.msc
Step 2: Go on the chanel: Application and Services Logs\Microsoft\Windows\FailoverClustering\Operationnal
Step 3: Select eventID 1201 (The Cluster service successfully brought the clustered service or application 'XXXXXX' online.)
Step 4: Right-click -> Attach Task to this Event
Step5: Check start a program
Step 6: Select the powershell script of the step 0
NB: To modify the task, open Task Scheduler, please go to the Windows Scheduled Task Microsoft-Windows-FailoverClustering_Operational_Microsoft-Windows-FailoverClustering_1201.
Problem description: You have got odd number of nodes. To prevent possible ties in the quorum vote you added a witness disk to the cluster and adjusted the quorum mode in the cluster manager.
However, when you perform a failover from SSMS on our SQL Server 2012 Availability group, we got a validation WSFC quorum vote configuration warning with action required
Error message: The current WSFC cluster quorum vote configuration is not recommended for this availability group.
Cause: This issue occurs because of a known issue in Windows Server Failover Clustering.
Resolution: To resolve this issue, you have to apply the hotfix below on all computers node of your cluster:
A hotfix is available to let you configure a cluster node that does not have quorum votes in Windows Server 2008 and in Windows Server 2008 R2http://support.microsoft.com/kb/2494036
Current status: After applied the fix, you should see that the warning is vanished.
Problem description: When you try to connect to your SQL Server by using powershell, .Net, sqlcmd... you got this error message:
System.ArgumentException: Keyword not supported: 'applicationintent'. at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value) at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
Cause: The message occurs when you include ApplicationIntent=ReadOnly (AlwaysOn Availability Groups properties) in your connection string. And the server does not recognize the property.
KB Extract number 2544514: "When the ApplicationIntent property is set to ReadOnly, the client requests a read access operation when it connects to an AlwaysOn-enabled database. The server will enforce the read-only access operation both at connection time, and during a USE database statement, but only to an AlwaysOn-enabled database. When the ApplicationIntent property is set to ReadWrite, the client requests a regular (read-write) access operation when it connects to an AlwaysOn-enabled database. The default setting for the ApplicationIntent property is ReadWrite."
Resolution: You have to install the update that introduces support for the AlwaysOn features from SQL Server 2012
- Update 4.0.2 for Microsoft .NET Framework 4 – Runtime Update
- An update introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1
Problem description: We got some mini memory dump. In side our SQL error log, we can see the error message below
Stack Signature for the dump is 0x00000000950A8E26 External dump process return code 0x20000001. External dump process returned no errors.Error: 8624, Severity: 16, State: 116. Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services. ex_raise2: Exception raised, major=86, minor=24, state=116,
severity=16, attempting to create symptom dump ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16
Interesting link: We found out a similar issue. However, I am in SQL Server 2005 SP4 (9.00.5000.00 (X64)), and the issue below was fixed in SQL 2005 SP2. E.G. FIX: Error message when you run a query in Microsoft SQL Server 2005: "Msg 8624, Level 16, State 116 Internal Query Processor Error: The query processor could not produce a query plan"
Cause: We got the query from the dump, but you could also get the query with a profiler trace with the events TSQL SQL:BatchCompleted or Stored procedure RPC:Completed with Error and Waring Exception. We the query we are able to reproduce the issue each time. We saw that the dump was generated by statistics stream corrupted (spotted that in the generation script of the objects)
Resolution: We run an update statitics with fullscan to solve the issue on all objects called by the query.We run again the query and the issue didn't occur.
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |
Problem description: After you moved SSAS databases to a new drive on the same server, you are not able to administrate Analysis Service. SSAS databases is up and running. When you add a database or user, it appears that users are added successfully, but when going back out of the dialog nothing happened.
Work done: To move you database to a new drive, you followed the step below. Step 1: With Microsoft SQL Server Management Studio, connect to Analysis Server, then right Click on the server and click on Properties. Step 2: Edit the path of you data directory. And replace the old path by the new one. Step 3: Stop the SQL Server Analysis Services Service. Step 4: Move the Data to the new directory location. Step 5: Start the SQL Server Analysis Services Service. Step 6: verify that you can browser all database.
Cause: Inside the SSAS log, you can see an issue relating to something not allowing SSAS to update or access this file
Message: Service started. (Source: ?\D:\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000) Message: The file '\\?\E:\OLAP\Data\master.vmp' could not be deleted. Please check the file for permissions. (Source: ?\D:\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001F)
I run Process Monitor and after SSAS restart I saw an access denied on various file and also on master.vmp.
For insformation: The master.vmp file is the master version map that contains the GUIDS for all of the objects and the version of each object that currently exists on the server. When the server starts, it looks at the master.vmp file and need to update it ( More information with my colleague Karan)
Resolution: You can solve this issue you can give the right "Modify" to SSAS service account to this new data folder.
Others points:
1. A database can be moved in many ways (SSMS Attach/Detach, AMO, XMLA). You could find some various way in the bol (Move an Analysis Services Database)2. You can only move the file regarding Data, Backup, Log or Temp. However it's not supported to move manually the BIN and Config directory. You must reinstall your instance.
Analysis Services is clustered as a generic service resource on a Windows failover cluster. This applies to 2005, 2008, 2008R2, and 2012.
Since SSAS 2005 version, Analysis Services is fully integrated into clustering Setup (the install of SQL): - SSAS 2005 cluster setup is adding the service on all cluster nodes on the same setup - SSAS 2008, the setup have to be done node by node Analysis Services is presented to the cluster as generic resource with the type Application Service and doesn’t implement his own explicit IsAlive method. Unlike SQL Server, Analysis Services doesn't have a cluster resource .dll that knows how to do that work like connect to AS and run a simple query to check that it responds.
Possible cause of failure that is detected for an Application Service: - The service stopped(ie. crash, or was killed) then the cluster resource would notice - A dependency in the group where the generic service is configured failed (a disk fails or goes offline, an IP, virtual name, mount point, SQL server…other dependencies affecting the group)
Note : Application Service will NOT detect a hang situation in SSAS, where all connections are rejected.
Problem description : The PowerShell command below uses to run every 5 minutes. But since 2 days, I got the error message below : Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.
(Invoke-Sqlcmd -ServerInstance $R -Database SSAS_Monitor -Query "INSERT INTO dbo.SSAS_DISCOVER_SESSIONS SELECT '$I', getdate(), * FROM OpenRowset('MSOLAP', 'Data Source=$I;' ,'SELECT * FROM `$system`.DISCOVER_sessions WHERE SESSION_USER_NAME <> ''$proxy''')")
Work done : - I had a look to windows event system and application but I didn’t see any error about that.- I didn’t see any reboot or new installation of any Hotfix or whatever.- I can see that provider for 32bit and 64bit are installed for MOLAP.5 (2012 RTM for the both)- I can see that provider for 32Bit only was installed for MOLAP.4 (2008 R2 RTM)- Process Monitor didn’t give me any clue.- I restarted the server just in case, but theissue was still there.
Workaround : It looks like my query is using now MOLAP.4 instead of MOLAP.5 (I guess but not sure). As the version for 2008R2 is installed only in32bit,the script failed with the message The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server
Step 1 : Unregister the both DLL:
regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
Step 2 : Register the both DLL, with 32Bit in first position:
regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
Step 3 : Restart SQL Service
After that, everything was working well :-)
Relative links :
- Explanation of Regsvr32 usage and error messages- How to perform a SQL Server distributed query with OLAP Server
You will be not able to migrate from SQL 2000 to 2012 directly. Migration from 2000 to 2012 requires an intermediate step.
SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1.
If you try to restore a backup database from SQL Server 2000, you will get the error number 3169 with the following message: The database was backed up on a server running version %ls. That version is incompatible with this server, which is running version %ls. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
You will have to make the migration in two steps:
- Step 1: Make a first migration from SQL 2000 to SQL 2008 for instance. You need to be SQL 2000 SP4, then follow this step : Migration SQL Server 2000 to SQL Server 2008 - Step 2: Make a second migration from SQL Server 2008 to 2012.
I invite you to read the following bol :
- Supported Version and Edition Upgrades- Use Upgrade Advisor to Prepare for Upgrades- Breaking Changes to Database Engine Features in SQL Server 2012
Note : SQL Server Upgrade Advisor is available for 2012 : Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. X86 Package (SQLUA.msi)X64 Package (SQLUA.msi)
It's not supported to Add or Remove features for Clustered instances
SQL Server setup 2012 does not support adding features to an existing failover cluster instance. By using the wizard, you will get the following error message:- Instance name '<Instance Name>' is already in use. To continue, specify a unique instance name.- The SQL Server failover cluster instance name '<virtual name>' already exists as a clustered resource. Specify a different failover cluster instance name.Analysis Services cannot be added to an existing instance of SQL Server. To share a resource group with an instance of SQL Server, you must choose to install Analysis Services during the initial installation of SQL Server. On the same note removing features from a clustered instance is also not supported.
Reference regarding old version: You cannot add or remove features to a SQL Server 2008 or SQL Server 2008 R2 failover cluster
Previously, SSAS was listening on all IP addresses of the cluster group using the default port (2383). Any alternate port configuration is ignored until version 2005 and 2008/R2. It’s not the same behavior with a standalone instance.
But since Analysis Services 2012, we are able to change the default port in cluster by editing the properties or msmdsrv.ini file.
Don't forget to configure your firewall to allow incoming requests on TCP port 2383. See also others port: 2382, 80,440 (Configure the Windows Firewall to Allow Analysis Services Access)
Test case:
- In my test below, I run the command tasklist /svc | findstr msmdsrv to get the processID of my instance SSAS 2012.
- Then, I run netstat -ano | findstr <myProcessID> to check if my instance is listening on my new configured port 5555.
- As you can see, it works :-)
Regarding the limitation of 2005/2008:- http://connect.microsoft.com/SQLServer/feedback/details/377639/2008-rtm-ssas-named-analysis-services-instance-port-doesnt-adjust-to-the-configured-port-after-a-failover- http://support.microsoft.com/kb/2466860- http://blogs.msdn.com/b/karang/archive/2009/09/05/sql-server-analysis-services-port-sql-2005-2008.aspx
IMPORTANT (9 November,2012) : Although you can connect to instance like "virtualName:port" , my colleague from Support Team confirm me, that we are not allow to change de port 2383 on clustered instance.
Port configuration for an Analysis Services cluster : On computers that have multiple network cards, Analysis Services listens on all IP addresses using the port you specify. On a clustered instance, Analysis Services will listen on all IP addresses of the cluster group, but only on TCP port 2383. You cannot specify an alternate fixed port for a clustered instance. (http://msdn.microsoft.com/en-us/library/ms174937.aspx#bkmk_cluster
What is Parameter Sniffing? First of all, don't be scared about parameter sniffing. It's an expected behavior.
When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query. Take the following stored procedure as an example: create procedure dbo.SearchProducts @Keyword varchar(100) asselect * from Products where Keyword like @Keyword
Assume the table is has approximately 100,000 rows, and has a single-column nonclustered index on the Keyword column.
Let's say you call this the first time and pass in a parameter @Keyword='XBOX%'. Suppose the number of rows in the table with a keyword starting with XBOX is very small -- perhaps just a few dozen rows. The optimizer might choose to use a query plan that uses the index on the keyword column to evaluate the LIKE, then a bookmark lookup to retrieve the other columns for the row. This index seek + bookmark lookup plan will be cached and reused for subsequent executions of the procedure.
Performance Problems Caused by Parameter Sniffing However, at some point in the future the server must compile/recompile a new plan for the stored procedure (the prior plan may have been aged out of cache or auto update statistics kicked in on the Products table, etc). Unfortunately, the particular execution of the procedure that compiled the new plan had a @Keyword parameter of 'KINECT%'. Suppose that the filter 'KINECT%' returns 10% of the rows in the table. When compiling the procedure with this parameter, SQL might select a query plan that uses a full table scan. That plan would be ideal for the parameter 'KINECT%', but would be a terrible plan for other, more selective, search criteria. Unfortunately, following the recompile, the table scan plan would also get cached and reused. The performance of subsequent executions with more typical parameter values would suffer. Parameter sniffing allows SQL to compile a plan that is tailored to the type of parameter that is actually passed into the stored procedure. Generally speaking, this feature allows more efficient stored procedure execution plans, but a key requirement for everything to work as expected is that the parameter values used for compilation be "typical". Unfortunately, as illustrated in this hypothetical example, a procedure or parameterized query may occasionally be executed with an atypical parameter (data skew is often at play in these cases). Parameter sniffing performance problems can affect all sorts of queries, but queries that use LIKE (like the example described above) are especially prone to this class of problem. Performance problems caused by parameter sniffing are generally considered to be By Design.
Action plan:
Since SQL Server 2005 we have a new functionality that permits individual plans recompilation instead of compiling entire execution plan for single store procedure. You could find below some workaround when you are affecting by a performance issue.
1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:
create procedure dbo.SearchProducts @Keyword varchar(100)AsDeclare @Keyworddummy as varchar(100)Set @Keyworddummy = @Keywordselect * from Products where Keyword like @Keyworddummy
2. To prevent this and other similar situations, you can use the following query option:
OPTIMIZE FORRECOMPILE
3. Disable auto-update statistics during the batch
Problem description: Last week, I got the error below inside BO.
Error: 8623, Severity: 16, State: 1.The query processor ran out of internal resources and could not produce a query plan.
Cause: This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. In my case, my customer has been getting the following error when attempting to select records through a query with a large number of entries in the "IN" clause (> 10,000).
Resolution: Our recommendation is to simplify the query. You may try divide and conquer approach to get part of the query working (as temp table) and then add extra joins / conditions.
See also remarks in the BOL (http://technet.microsoft.com/en-us/library/ms177682.aspx)“Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table. The large IN clause needs to be changed to a table. “
Others workarounds: You could try to run the query using the hint option (force order), option (hash join), option (merge join), option (querytraceon 4102) with a plan guide. By enabling the traceflag 4102, we will revert the behavior to SQL Server 2000 for handling semi-joins.
Interesting KB:- FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2- FIX: Error message when you run a complex query after you install Cumulative Update 3 or Cumulative Update 4 for SQL Server 2005 Service Pack 2: "The query processor ran out of internal resources and could not produce a query plan"
Sometime , Analysis services engine terminates unexpectedly with an internal error when unexpected exception occurred. In this case, you could find in the log directory various mini memory dump.
It is recommended to contact Microsoft Support to help on the analysis of the dump to identify the root cause of the problem
$serverName = "serverName"$instanceNameOLAP = 'mySSAS_InstanceName' # Get the internal OLAP instance Name $InternalInstanceName=(get-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\OLAP").$instanceNameOLAP
# Get the registry path where dumpDir is located $keyName="HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"+$InternalInstanceName+"\CPE"
# Get the value of ErrorDumpDir key$serverLogFolder=$(Get-ItemProperty $keyName).ErrorDumpDir
# Lists the objects stored in this folder where the extension is .mdmp.$dump = dir $serverLogFolder | ?{$_.name -match “^*.mdmp$”}
foreach ($b in $dump | SELECT LastWriteTime,Name){ write-host 'Found memory dump inside '$serverLogFolder break;}
$dump | SELECT LastWriteTime,Name, Length
Script updated 9th April 2012.
You could find below a sample a script powershell to see which property is no default value of Analysis Services:
$serverName = "serverName\SQL2008_InstanceName"
# Load the AMO assembly in Powershell [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
# Create a server object$serverAS = New-Object Microsoft.AnalysisServices.Server
# Connect to your Analysis Services server $serverAS.connect($serverName)
# Creation of my dataset to store my result$myDS = new-object System.Data.DataSet$myDS.Tables.Add("myDS")$myDS.Tables["myDS"].Columns.Add("name",[string])| out-null$myDS.Tables["myDS"].Columns.Add("value",[string])| out-null$myDS.Tables["myDS"].Columns.Add("defaultvalue",[string])| out-null
# Browsing all properties to see which one has been changed foreach($b in $serverAS.ServerProperties | SELECT name,value,defaultvalue ){ # Check if the current value is different from the default value if ($b.value -ne $b.defaultvalue) { # Add a new row to my Data Set $dr = $myDS.Tables["myDS"].NewRow() # Fill the cells $dr["name"] = $b.name; $dr["value"] = $b.value.ToString(); $dr["defaultvalue"] = $b.defaultvalue; $myDS.Tables["myDS"].Rows.Add($dr)
}
# Query the result$myDS.Tables["myDS"] | SELECT name,value,defaultvalue;
I will try to illustrate how to get various information about Analysis Services by using powershell.
You could find below the first script to make the first connection:
# Create a server object :$serverAS = New-Object Microsoft.AnalysisServices.Server
# Select the information$serverAS | SELECT Name,Edition,Version
You will find below a summary of important points to know to use the mount points with SQL Server.SQL Server and SSAS supports mount points in cluster if and only if at least one letter named as the primary drive by instance. Then you can add your mount points.You must also meet the following prerequisites:1. You can not put the files directly to the root mount point. It is imperative to create the root of each mount point, a directory, then place the files in that directory.2. As part of a cluster for each mount point, you must create a cluster resource. Then add a dependency to indicate that SQL Server will not start if all dependencies and disk mounting points are not all online.
In addition, references to KB articles:1. FIX: You are prompted to format the volume when a formatted volume is mounted on a NTFS folder that is located on a computer that is running Windows Server 2008 or Windows Vista (http://support.microsoft.com/kb/971254/ )
2. It is recommended not to put files to the root of the mount point for support by Microsoft for support. Solution: create a directory to deposit your files.
E.G. SQL Server 2008 setup fails to install on a Windows Server 2008-based cluster mount point(http://support.microsoft.com/kb/2216461/)“Note: SQL Server does not support installing to the root of the Volume Mount point because the permissions are not propagated to the Mount Point since Windows does not let you replace permissions on the root of a volume. To prevent problems during new installations and applications of updates to SQL Server, to create a folder on the root of the mount point and install to that folder. At this point, permissions are set correctly. If you previously installed to a root directory, we highly recommended that you create a folder, validate the current database integrity by using the DBCC CHECKB process, and then move the database to the folder.” To work around this problem, simply create the root of each mount point, directory, then move the files in that directory.
3. Cluster : Dependencies and mounts point : http://support.microsoft.com/kb/819546/ “The SQL Server 2005 (and later versions) resource depends on the SQL network name resource and the physical disk resources that hold its data. When mount points are being used together with the physical disks, each mount point must appear as a cluster resource. Additionally, each mount point must also be added as a SQL Server dependency. If only the root physical disks dependency is added and the mount points are not added, database corruption will occur on failover. Database corruption may also occur when SQL Server is restarted without failing over.”
4. Cluster : SQL Server support for mounted volumes(SQL 2000,2005 and 2008) : "Because of the number of available drive letters, the number of the virtual instances on a cluster is limited to 25. SQL Server 2005 and later versions have the same limitation. SQL Server supports use of mount points hosted only by drives that have drive letters that are not hosted by other mount points." (http://support.microsoft.com/kb/819546)
I share with you a problem I had recently on SSIS.
Problem Description: When you start treatments in parallel, your SSIS packages fail error. You have two servers and one reproduces this behavior.You got the following error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Cause: We initially verified that the versions of drivers were exactly the same on both servers.- The server with the issue was in DataAccess64 Sybase build 15.05.00.9000- The server without the issue was in DataAccess64 Sybase build 15.05.00.1015
Resolution: We have copied only directories DataAccesset DataAccess64 Sybase client (build 15.05.00.1015) to the client installed (build 15.05.00.9000).
The problem has been fixed with this workaround.
If you can not fix your problem I invite you to generate a dump, then call MS support.
Many of you ask me for a script sample to install SQL Server 2008 R2 Cluster. You could find below a sample based on the following BOL:How to: Install SQL Server 2008 R2 from the Command Prompt
Before to begin I invite you to read my previous article: Installation of SQL Server 2008 clusters on Windows 2008 .For failover cluster installations, you must be a local administrator with permissions to login as a service, and to act as part of the operating system on all failover cluster nodes.
Step 1: Configuration file of first node
Create a file called "FailedOverInstallation.ini". Then copy/paste the parameters below with your own values
;SQLSERVER2008 Configuration File[SQLSERVER2008] INSTANCEID="InstanceName"ACTION="InstallFailoverCluster"FEATURES=SQLENGINE,REPLICATION,FULLTEXTQUIET="True"INSTANCENAME="InstanceName"FAILOVERCLUSTERDISKS="DiskRessourceName"FAILOVERCLUSTERGROUP="SQLServerGroupName"FAILOVERCLUSTERNETWORKNAME="NetworkName"INSTALLSQLDATADIR="J:\\"SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"FAILOVERCLUSTERIPADDRESSES="IPv4;AdressIP;Public;255.255.255.0"SECURITYMODE="SQL"SAPWD="*********"SQLSYSADMINACCOUNTS="Domaine\YourAdminAccountName"
SQLSVCPASSWORD="*********"AGTSVCPASSWORD="*********"AGTSVCACCOUNT="Domaine\SQLAgentAccountName"SQLSVCACCOUNT="Domaine\SQLServerAccountName"
Step 2: Installation of the first node
Run the following command in the command prompt to call the configuration file on the first node.
C:\installFiles\setup.exe /ConfigurationFile=C:\FailedOverInstallation.ini /IACCEPTSQLSERVERLICENSETERMS
Step 3: Configuration of secondaries nodes (Add Node)
;SQLSERVER2008 Configuration File*[SQLSERVER2008]ACTION="AddNode"QUIET="True"INSTANCENAME="InstanceName"FAILOVERCLUSTERGROUP="SQLServerGroupName"FAILOVERCLUSTERNETWORKNAME="NetworkName"
Step 4: Installation of the second nodeRun the following command in the command prompt to call the configuration file on the second node.
C:\installFiles\setup.exe /ConfigurationFile=C:\AddnodeInstallation.ini /IACCEPTSQLSERVERLICENSETERMS
Good installation.
Problem description: When you are using SQL AS Resource Monitoring Tool (http://asactivityviewer2008.codeplex.com/), you got the error message: "Input string was not in a correct format".
Cause: This issue is due to improper format
Resolution: Change on your client machine, the regional and language options settings located in Control Panel.
Problem description: We are running a very huge SELECT and import the result set inside another table. We found out that we got some duplicates ROWS in the destination table. However it shouldn't occur because each row is unique.
Cause: We noticed at this time that another query was updating the clustering key of the index. Updates to the clustering key can cause rows to move within the index.We were meeting a situation where the physical location of a row within a table changes due to an modification operation. The result is that the same row may be revisited multiple times at its original location. Reading the data using an index whose key is going to be update is an example of the Halloween effect.
Recommandation: In most cases it is best to start with a clustered index on every table.
A good candidate for the clustered index key must have the following characteristics: - Narrow in size (since repeated in all nonclustered) - Unique - Incremental - Static
As in a cluster index, the data rows are stored in order, based on the cluster index key, this issue occurs because the key is updated. You wouldn't normally expect the key to be frequently update.
Resolution: To ensure that a row is not updated, encountered again by the same scan, we fix the issue with the hint HOLDLOCK and a plan guide
Links:- The "Halloween Problem" for XML APIs- Halloween Protection
Happy Halloween! ;-)
SQL Server 2012 developer training kit is available. It will help you to take advantage of the improvements introduced in SQL Server 2012 in your application.
Download: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27721
More detail: http://blogs.msdn.com/b/rdoherty/archive/2011/10/12/learn-sql-server-2012-development-now.aspx
Enjoy :-)
Problem: Remote T-Sql debugger from SSMS SQL 2008 fails with one of the following issue: Could not attach to SQL Server process on 'MyInstance'.The RPC server is unavailableORUnable to start T-SQL debugging. Could not attach to SQL Server process on 'MyInstance'.So, you are not able to remotely connect in SQL Server Management Studio to your development server in SQL Server 2008
Troubleshooting to do: All tests below are not only for the RPC issue but also for others issues like authentication.Test 1: When you locally connect to the same instance the issue is not reproduced Test 2: Check if TCP 139, TCP 445, UDP 137 and UDP 138 are open from both side (server and client) (e.g.: Configuring the Transact-SQL Debugger )Test 3: Check if SQL management Studio is lauched with "Run an administrator..." Test 4: Check if SQL service account can connect to the client machine. When you are using T-SQL debugging, SQL service account will communicate with the machine host Management Studio.It's an additional reason to set up SQL service with domain account
Test 5: Check if SSMS and SQL server service are in the same domain. if not, set up the same account and password that SQL server is using.Test 6: Check if your domain account is sysadmin.Test 7: Check if Kerberos is enable correctly and SPN are ok for your instance.- The SQL Network Interface library was unable to register SPN- SQL Server 2008 connectivity issue : cannot generate SSPI context
Test 8: Check from server side that the service below are started- TCP/IP NetBIOS Helper- Remote Registry - RPC Service
I got several times the same questions around index and fragmentation on SQL2008. So I decided to add one more item on Internet to discuss about the reconstruction and reorganization of the index and how detect index fragmentation.
StarterAs fragmentation can have a negative impact on the efficiency of data access; one of the main tasks of a DBA is to maintain database indexes.In the life cycle of a database, fragmentation is expected behavior and natural. If the database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
Main courseSince SQL Server 2005 the sys.dm_db_index_physical_stats Dynamic Management Function returns the size and fragmentation information for the data and indexes of the specified table or view.
1. There are 3 mode in the syntax of sys.dm_db_index_physical_stats:
2. There are 2 kind of fragmentation on indexes.
In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing and rebuilding depends on defragmentation values. The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. The value should be as close to zero as possible. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation.
The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).
DessertThe script will work for both SQL 2005 and higher versions. The aim is to reduce index fragmentation by recreating, reorganizing, or rebuilding the index:
DOWNLOAD THE SCRIPT SAMPLE HERE
To run the stored procedure without execute the rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1
To run the stored procedure and defragement indexes : EXECUTE handdleFragmentationIndexesTo run the stored procedure and defragement indexes on a specific database : EXECUTE handdleFragmentationIndexes @databaseName = 'myDatabaseName'
This script sample is extract in part from the following BOL : sys.dm_db_index_physical_stats (Transact-SQL)
SymptomI was not able to shrink the transaction log of my database. I got the following error message when I ran an DBCC SHRINKFILE (N'LogicalName' , NOTRUNCATE)
Cannot shrink log file 2 (XXLogicalNameXX) because all logical log files are in use. Cannot shrink log file 2 (XXLogicalNameXX) because the logical log file located at the end of the file is in use.
Environment: my database was a publisher of my transactional replication and the recovery model was in SIMPLE.
Troubleshooting stepStep 1: if your recovery model is FULL, be ensuring that you made backup log. BACKUP LOG databaseName TO DISK='C:\fileName.TRN'
Step 2: Check the log space used with the command dbcc SQLperf(logspace). Do you have a free space?
Step 3: use the DBCC OPENTRAN Transact-SQL to verify if there is an active transaction in a database at a particular time. If yes kill it.
Step 4: check the value of log_reuse_wait_desc
select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases where name LIKE 'yourDatabaseName'
CauseIn my case, column log_reuse_wait_desc returned REPLICATION (e.g. BOL Factors That Can Delay Log Truncation). So the log was not truncated because records at the beginning of the log are pending replication.
Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. Typically the Log Reader agent will parse the entire log and then mark each log record as replicated by executing sp_repldone.
ResolutionWhen I tried doing the same manually, my issue was fixed:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
With sp_repldone, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. So if you are not aware with her impact, I recommend you to drop your Publication, Subscription and Disabled Replication. Then run the shrink command and recreate the replication.
Reference - A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server- How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server- Transaction Log Truncation- Shrinking the Transaction Log- How to use the DBCC SHRINKFILE statement to shrink the transaction log file