Thursday, March 21, 2013

[SQL 2012] Always ON Secondary database Read Only Connection.....Please help.

[SQL 2012] Always ON Secondary database Read Only Connection.....Please help.

Always ON Secondary database Read Only Connection.....Please help.

Posted: 21 Mar 2013 02:17 AM PDT

Dear All,I have configured SQL always on with two server and enabled SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing by using below query.But when i am checking the Read only connection using (sqlcmd -S X.X.X.X -E -d DatabaseName-K ReadOnly) the connection is still routing to primary serever only...Please help if any additional things to do--Specify a read_only_routing_urlALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG1'WITH( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG1.TESTDOMAIN.local:1433' ))-------------------------------------------------------------------------------------------------ALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG2'WITH( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG2.TESTDOMAIN.local:1433' ))---------------------------------------------------------------------------------------------------Specify a read-only routing listALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG1'WITH( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2') ))-------------------------------------------------------------------------------------------------ALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG2'WITH( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('ALWAYSON-AG1') ))-------------------------------------------------------------------------------------------------Testing Method:C:\ sqlcmd -S X.X.X.X -E -d DatabaseName -K ReadOnly: Select @@serverName: GoPrimary server Name am getting.!

SQL server 2000 upgrade to SQL server 2012

Posted: 20 Mar 2013 04:21 PM PDT

While migrating from SQL server 2000 databases to SQL server 2012 databases , we need to follow one of the below approach .SQL server 2000>>SQL Server 2005 SP4 >> SQL server 2012• Clean approach to migrate Remediation of Non ANSI SQL 2000 code to ANSI complained code • TOC of SQL server 2005 is less than the TOC for SQL server 2008• Amount of remediation for data type deprecation, syntax changes is high from SQL 2000 to SQL 2008 migration SQL server 2000>>SQL Server 2008 R2 >> SQL server 2012• Remediation for SQL 2000 and SQL 2005 will be handled in on go• TOC of SQL server 2008 is higher than the TOC of SQL server 2005• Most of the remediation issue will be address at the intermediate migration Kindly let me know your views about the best approach .

system FlushCache happening often

Posted: 24 Sep 2012 03:28 AM PDT

SQL 2012 - enterprise... OLTP environment...Experiencing the following - several times per hour. Has anyone experieced the following:FlushCache: cleaned up 513008 bufs with 448519 writes in 75641 ms (avoided 92621 new dirty bufs) for db 10:0average throughput: 52.99 MB/sec, I/O saturation: 104, context switches 5758last target outstanding: 80800, avgWriteLatency 0The system is generating the flush cache and causing severe performance degradations.

An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue'

Posted: 20 Mar 2013 10:10 PM PDT

What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.

Video tutorials for MDS and DQS for 2012

Posted: 20 Mar 2013 04:59 PM PDT

Hi Team,Can any one point me to good video tutorials on using features in MDS and DQS.I am good with installing the MDS. Thanks in advance

Licensing for Evaluation before Purchase?

Posted: 20 Mar 2013 04:34 AM PDT

We are looking to create a new SQL box, and there is also a desire by management to migrate from 2008 to 2012 SQL Server when they do it. Anybody know Microsoft's policy on evaluation before purchasing licenses (i.e. before they by licensing for 2012 on the box they want to make sure upgrading doesn't break their existing system)?

Data size error in SSAS 2012 Tabular Table Import Wizard for n/varchar(max)

Posted: 20 Mar 2013 11:06 AM PDT

Does anyone know the actual maximum string length values for columns of type varchar(max) or nvarchar(max) when using the Table Import Wizard in a tabular project?I'm working my way through the Wrox book Professional SQL Server 2012 Analysis Services with MDX and DAX and trying my hand at tabular projects for the first time. A couple of the tables provided in the AdventureWorksDW relational database have nvarchar(max) fields. When attempting to add these tables via the Table Import Wizard, I receive an error. No error number or code is provided, just this:[quote]The size of a data value in table '<table name plus long identifier string>' column '<column name>' was too large to fit in that column. The current operation was cancelled because another operation in the transaction failed.[/quote]If I filter out the column from the import, the rest of the tables import fine, but the text didn't mention needing to exclude the columns.I checked out the [url=http://msdn.microsoft.com/en-us/library/gg492146.aspx]Data Types Supported[/url] in BOL, and it contains the useful tidbit [quote]You cannot import from a varchar(max) column that contains a string length of more than 131,072 characters.[/quote] "OK," I thought, "These fields are nvarchar(max), so that would probably max out at 65,536 characters since each character requires twice the bytes. There must be strings longer than that in my data."However, when I ran both MAX(LEN(<field>)) and MAX(DATALENGTH(<field>)), one of the tables having the issue returned a maximum length for the field in question of only 34,568 characters and 69,136 bytes, well under the limit in BOL. Thinking it may be an issue with nvarchar(max) as opposed to varchar(max), I copied the data to a new table, moving the nvarchar(max) data to a varchar(max) field. Attempting to import from the varchar(max) field (now only 34,658 bytes) still generated the same error.I haven't been able to locate any other information online about the error or about actual maximums enforced or whether or not it may be impacted by available memory (since it all has to be stored in-memory for the VertiPaq engine). I'd much appreciate any insight anyone has to offer.

Upgrade SQL/SSRS 2008R2 to 2012 SP1

Posted: 20 Mar 2013 04:07 AM PDT

I'm testing upgrading SQL/SSRS from SQL 2008 R2 SP2 to SQL 2012 SP1 (11.0.3349). Most of my reports' data source is a Dynamics NAV DB where most of the number fields are decimal(38,20). I'm finding that when I have a zero value in a column on a report that the report loses formatting and throws xml exceptions when rendered to Excel. So a field formatted for currency would become 0.00000000000000000 and renders as text in Excel 2010, but if there's a value in the cell then the formatting is fine. I'm looking on the MS site and there is documentation that this is an Excel issue and was supposed to be fixed in a CU for Office 2010 Excel, but I didn't see it in the release notes for the CU. The error is "Excel found unreadable context.." There are no errors in the report itself only rendering. Rendering to other formats is fine. I've found a workaround where I change the value in a report by using an if statement to make it equal to zero (IIF(value=0,0,value), but I've got several hundred reports in my library and I wouldn't even know how many cells I'd have to change. I'm just looking to see if anyone else has ran into this issue during their upgrade testing process.I can repeat the error in a new report if I use as a Dataset Select Cast(0 as decimal(38,20)) d1 , CAST(0 as decimal(10,2)) d2Export to Excel gets this error<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="<logFileName>error056200_05.xml</logFileName><summary>Errors">http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error056200_05.xml</logFileName><summary>Errors were detected in file 'C:\Users\...ME...\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\P42V8D4D\_TestExcel.xlsx'</summary><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>The d1 column is now formatted as text 0.00000000000000000000 instead of as currency and the d2 column is formatted correctly
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog