Wednesday, June 12, 2013

[SQL 2012] Filestream, AlwaysOn and large files

[SQL 2012] Filestream, AlwaysOn and large files

Filestream, AlwaysOn and large files

Posted: 11 Jun 2013 11:59 PM PDT

hi all,I'm dealing with some new topics. The more I read about the more questions I have, not finding answers.Maybe some of the questions can be answered here. Would be great :-)[u]Background:[/u]we are starting a new project using SQL 2012 and AlwaysOn High Availability Groups.On on the requests: we have to store files. What for? A customer, using a frontend, can execute different queries an save the results to different files. Then the customer can show a list of his files and download them at any time.[b]Some of the files to store can grow to a size of 200-500 MB.[/b][b]Our database wil be placed on a high availability group, using synchronous mirroring.[/b][u]The questions[/u]1) Will the files on the file system copied to the secondary replicas too?2) If adding a 500 MB file to a file stream table, will it automatically be transferred to the storage of the seondary replica(s)?3) If yes, will this be done as a single transaction? This would take some time blocking all other transactions transferred to the secondary replicas?4) What difference would it be to use the FileTable-Feature with non_transacted_access instead of FileStream only?5) Are there other senseful possibilities for saving files of this size having context to the database?Thank you for any help :-)

Keeping roles and security in sync mirrored DB's

Posted: 11 Jun 2013 06:41 PM PDT

HiWhat is best way to keep roles and security in sync on mirrored systems? (both Instance and DB logins)What do people have set-up to ensure that in the event of a disaster, users can log into the switched DB?Thanks

Strange behavior with ODBC

Posted: 11 Jun 2013 08:54 AM PDT

We have a TM1 server that accounting uses for their numerology magic... anyway, it connects to our SQL server via ODBC. We have six connections - we use Microsoft Great Plains, and each division of our company has it's own database. Anyway, all six of these were setup using SQL authentication, and with the "sa" account.Five of them work flawlessly; the sixth fails to connect, and in the SQL log we see the following message:[code="other"]Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.Error: 18456, Severity: 14, State: 58.[/code]I am puzzled by this... not sure first of all why it's saying "Server is configured for Windows authentication only" because obviously that is not the case; the other five ODBC entries are pointing at the same server using the same account. Also, the fact that it's passing a blank username instead of "sa" is strange.I reentered the password multiple times; even tried deleting and recreating the ODBC entry. Same results. Finally, I was able to get it working by using Windows authentication instead of SQL authentication.So basically I am wondering if anyone has any ideas as to why it would be different for this connection and not the others. All six were previously pointing to a SQL 2005 instance, all with SQL authentication, and were working just fine. Now they are pointing to a new server running SQL 2012.

BLOCKING & PERFORMANCE ISSUES IN SQL SERVER 2012

Posted: 11 Jun 2013 06:54 AM PDT

Hi ,Last week we increased RAM and processors. now ,In our environment we have 2 processors,6 cores and hyper threading present ,so a total of 24 processors (6*2*2) available with 48GB RAM. I gave 28gb for sql as max server memory. in event viewer after a day i got insufficient memory to run the query. There are lot of blockings so that application gets hanged and nothing is working fine. Could anyone suggest me in which aspect i should check the performance of sql.Now i have increased as 38 gb out of total of 48 gb for sql server. Server:sql server 2012 enterpriseOS:Windows server 2012 standardIn our applications we have long running threads with many joins ,but before there was no issues like blocking .....we face once we increased RAM and processors ...Need to resolve blockings and run apps fastly . Any one please help me !!!!

Justify Hardware

Posted: 11 Jun 2013 06:30 AM PDT

This is most likely my biggest weakness at the moment, but I do not know how to go about justifying better hardware with some real facts that the non techs would understand. So what is the best approach to this. Not sure if this would help, but the basis system info: I am running a virtual SQL 2012 server 96GB RAM 8 cores.It is attached to a Equilogics SAN Array (15k disks)I am getting PATEIOLATCH_EX (you can add almost all the codes after the _)Surprisingly, partitioning actual speed the system up tremendously.Some stats:to delete 107,000 records it takes about 15 seconds batching 5000 at time (this seems long)to Select 107,000 and insert them into another table is take 12 seconds using the SSIS data flow componentSQL_Noob

PBM is coming up with errors when executing policies against groups in CMS groups

Posted: 11 Jun 2013 06:31 AM PDT

Hi All, I have SQL 2012 SQL server which I am using as central server for Central management server. Also I have created varous policies to manage the server which are mostly SQL 2008 or R2 or 2012. I am running the PBM (policy based management) tool as the SQL server scheduled job and its working for local server, however not working for servers under CMS . Policies are correct and can be run for local or for multiple remote server, if I execute this via management studio or manually. However When i run this as SQL server job, it keeps on failing. I have attached the error below. Also I have already created proxy server account for this and alreay checked permission for SQL server agent account on all servers. Configuration of SQL server is also checked for remote connection, TCP/IP enabled, named pipes enabled and for firewall. Every thing else works fine and I am able to connect to other server via CBM and from local server. However when I run this code , It keeps on failing with connectivity issue... Any idea will be really helpful. [color=red]Attach is the error message[/color] <DMF:Exception type="string">Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy DATABASE MAINT - LastLogBackupDate. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server . ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)<?char 13?> at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)<?char 13?> at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)<?char 13?> at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)<?char 13?> at System.Data.SqlClient.SqlConnection.Open()<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()<?char 13?> --- End of inner exception stack trace ---<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()<?char 13?> at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Common.ServerConnection.Microsoft.SqlServer.Management.Common.ISfcConnection.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection.get_ServerVersion()<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.<CalculateTargets>d__1d.MoveNext()<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(IEnumerable objectSet, Condition condition, AdHocPolicyEvaluationMode evaluationMode, Object[]& conforming, TargetEvaluation[]& violating)<?char 13?> at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(SqlStoreConnection targetConnection, Condition condition, AdHocPolicyEvaluationMode evaluationMode, String policyCategory, Object[]& conforming, TargetEvaluation[]& violating)<?char 13?> at Microsoft.SqlServer.Management.Dmf.Policy.EvaluatePolicyUsingConnections(AdHocPolicyEvaluationMode evaluationMode, SfcQueryExpression targetQueryExpression, Int64& historyId, ISfcConnection[] targetConnections)<?char 13?> --- End of inner exception stack trace ---</DMF:Exception> <DMF:ID type="long">1</DMF:ID> <DMF:Result type="boolean">false</DMF:Result> [color=red]PIECE OF CONNECTIVITY CODE[/color] # Evaluate specific Policies against a Server List # Uses the Invoke-PolicyEvaluation Cmdlet param([string]$ConfigurationGroup=$(Throw ` "Paramater missing: -ConfigurationGroup ConfigGroup"),` [string]$PolicyCategoryFilter=$(Throw "Parameter missing: ` -PolicyCategoryFilter Category"), ` [string]$EvalMode=$(Throw "Parameter missing: -EvalMode EvalMode")) # Parameter -ConfigurationGroup specifies the # Central Management Server group to evaluate # Parameter -PolicyCategoryFilter specifies the # category of policies to evaluate # Parameter -EvalMode accepts "Check" to report policy # results, "Configure" to reconfigure any violations # Declare variables to define the central warehouse # in which to write the output, store the policies $CentralManagementServer = " TEST" $HistoryDatabase = "TESTAd" # Define the location to write the results of the # policy evaluation. Delete any files in the directory. $ResultDir = "E:\PolicyManagement\EvaluationLogs\" $ResultDirDel = $ResultDir + "*.xml" Remove-Item -Path $ResultDirDel # End of variables #Function to insert policy evaluation results #into SQL Server - table policy.EPM_PolicyHistory function PolicyHistoryInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $ConfigurationGroup, $PolicyCategoryFilter, $EvaluatedPolicy, $EvaluationResults) { &{ $sqlQueryText = "INSERT INTO policy.EPM_PolicyHistory (EvaluatedServer, EvaluatedGroup, EvaluatedCategory, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$ConfigurationGroup', N'$PolicyCategoryFilter', N'$EvaluatedPolicy', N'$EvaluationResults')" Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop } trap { $ExceptionText = $_.Exception.Message -replace "'", "" } } #Function to insert policy evaluation errors #into SQL Server - table policy.EPM_PolicyHistoryErrors function PolicyErrorInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $ConfigurationGroup, $PolicyCategoryFilter, $EvaluatedPolicy, $EvaluationResultsEscape) { &{ $sqlQueryText = "INSERT INTO policy.EPM_PolicyHistoryErrors (EvaluatedServer, EvaluatedGroup, EvaluatedCategory, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$ConfigurationGroup', N'$PolicyCategoryFilter', N'$EvaluatedPolicy', N'$EvaluationResultsEscape')" Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop } trap { $ExceptionText = $_.Exception.Message -replace "'", "" } } # Connection to the policy store $conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$CentralManagementServer;Trusted_Connection=true"); $PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn); # Create recordset of servers to evaluate $sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true"); if ($ConfigurationGroup -eq "local") { $q = "SELECT @@servername;" } else { $q = "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].[fn_EPM_ServerGroupInstances]('$ConfigurationGroup');" } $sconn.Open() $cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn); $cmd.CommandTimeout = 0; $dr = $cmd.ExecuteReader(); # Loop through the servers and then loop through # the policies. For each server and policy, # call cmdlet to evaluate policy on server while ($dr.Read()) { $ServerName = $dr.GetValue(0); foreach ($Policy in $PolicyStore.Policies) { if (($Policy.PolicyCategory -eq $PolicyCategoryFilter)-or ($PolicyCategoryFilter -eq "")) { &{ $OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $ServerName ), (Encode-SqlName $Policy.Name)); Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile; $PolicyResult = Get-Content $OutputFile -encoding UTF8; $PolicyResult = $PolicyResult -replace "'", "" PolicyHistoryInsert $CentralManagementServer $HistoryDatabase $ServerName $ConfigurationGroup $PolicyCategoryFilter $Policy.Name $PolicyResult; } trap [Exception] { $ExceptionText = $_.Exception.Message -replace "'", "" $ExceptionMessage = $_.Exception.GetType().FullName + ", " + $ExceptionText PolicyErrorInsert $CentralManagementServer $HistoryDatabase $ServerName $ConfigurationGroup $PolicyCategoryFilter $Policy.Name $ExceptionMessage; continue; } } } } $dr.Close() $sconn.Close() #Shred the XML results to EPM_PolicyHistoryDetails Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query "exec policy.usp_EPM_LoadPolicyHistoryDetail" -ErrorAction Stop

system query being constantly run against instance, but dont know why

Posted: 11 Jun 2013 04:59 AM PDT

The following query is being run against my 2012 instance, once for each DB, on a very regular basis.[code="sql"]select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid from [<db name>].[sys].[filetable_updates_XXXXXXXXXX] with (readpast) order by table_id[/code] XXXXXXXX is a number.I dont have filetable enabled on the server so cant see why sql might be running this. Google has not turned up much. Any ideas on this?

Login Failure due to Mirroring for Report Server

Posted: 11 Jun 2013 04:28 AM PDT

I have set up two SQL Server 2012 (Standard Edition) boxes running Report Server. I configured the two Servers (Server A and Server B) to be mirrored. All working just fine. I have failed over and failed back still everything working fine. What I have noticed that in the Server Log errors on Server B (mirror server) that I keep getting the error : "Login failed for user 'Service Login Account'. Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: <local machine>]." I can understand why this would happen as the Report Server is not available for connection but was wondering if this could somehow be turned off as it fills up the error log without any useful information. I tried stopping Report Server Services, while that stopped the error, it also dissconnected mirroring and I had to go back and start mirroring from scratch. I know mirroring for Report Server is a bit different than normal User databases but I would think there should be some way to mirror the Database without generating all these errors. Any insight or workaround would be greatly appreciated...Thanks in Advance...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog