Friday, August 16, 2013

[SQL 2012] Backups \ Maint Jobs AlwaysOn SQL 2012

[SQL 2012] Backups \ Maint Jobs AlwaysOn SQL 2012

Backups \ Maint Jobs AlwaysOn SQL 2012

Posted: 16 Aug 2013 03:00 AM PDT

Hello all, I have installed and completed a AlwaysOn 2 node VM failover cluster, what fun :-D! I was starting to setup the backup jobs and ran across how AlwaysOn works with this. A little back ground, we slammed this in to meet a deadline and boot contractors out so my learning was put on hold until after we went live, Ya me as the DBA!So I went and change the back preference to Any Replica, this allowed me to back up the primary node and place the tlog BU, re-index, checkdb, update stats, clean files jobs. Now the secondary replica I want to backup as well. The notes I had read from a MVP DBA had said that if I used Any Replica I could backup both...No Dice! I can put a copy only full backup on the secondary but it will not backup the user database, just master and MSDB.I also found the Exclude Replica option but cannot get a clear explanation on it, it could be the lack of sleep I have had over the past week slamming this into Prod, which I am not happy about but thus is the life of a DBA, right! I am sure we all know!Besides the backup, I want to setup Maint. Jobs on both. Now I know that AlwaysOn is Mirroring and FCI, it works, great, I know if I rebuild some indexes it will copy over to the secondary, that's great too however this is two separate databases so I want them both the be clean. :)So my questionsa. How can I backup both P and S?b. What Maint. Jobs should be set up on each?Thanks!

Differential Backup Failed

Posted: 15 Aug 2013 09:03 PM PDT

Hi,I've scheduled M-Plan to perform Full & Diff. backups, till yesterday those plans were ran fine, but Today Diff. backup job failed with the reason below --Executing the query "BACKUP DATABASE "" TO DISK = N'..." failed with the following error: "Cannot perform a differential backup for database "", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly (Error -- "-1073548784")I already performed Full backup job, but still failing with the same reason...please help me..it is an emergency...

Tempdb gets full when I run query

Posted: 16 Aug 2013 02:08 AM PDT

Hi AllI have 5 temp tables that are inner joined to load 6 million rows into a master temp table.The query that loads 6-7 million rows from these 5 primary temptables into master table is taking more than 2 hrs and throwing an error [i]Msg 9002, Level 17, State 4, Line 201The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases[/i]Below are the temp tables and the query used to load data into master temp table.#noncalc_Data -- 160033 rows#CHTYAMT_DATA -- 11538 rows #BADAMT_DATA --73783 rows#INSADJ_DATA -- 554835 rows#INSOSB_DATA -- 47871 rows#OSPATBAL_DATA -- 83934 rows#PRECALC_DATA -- 972092 RowsSELECT #NonCalc_DATA.accountNumber, #NonCalc_DATA.mrn, #NonCalc_DATA.guarantorNumber, recordAccountId , patientType , insurance1FinancialClass , insurance2FinancialClass , insurance3FinancialClass , #NonCalc_DATA.currentFinancialClass, payer, accountstatus , hospitalId , serviceType , #NonCalc_DATA.guarantorLastName, #NonCalc_DATA.guarantorFirstName, #NonCalc_DATA.guarantorMiddleInitial, #NonCalc_DATA.guarantorAddress1, #NonCalc_DATA.guarantorAddress2, #NonCalc_DATA.guarantorCity, #NonCalc_DATA.guarantorState, #NonCalc_DATA.guarantorZip, #NonCalc_DATA.guarantorHomePhone, #NonCalc_DATA.guarantorSSN, #NonCalc_DATA.guarantorDOB, #NonCalc_DATA.guarantorGender, #NonCalc_DATA.guarantorEmployer, #NonCalc_DATA.patientLastName, #NonCalc_DATA.patientFirstName, #NonCalc_DATA.patientMiddleInitial, #NonCalc_DATA.patientAddress1, #NonCalc_DATA.patientAddress2, #NonCalc_DATA.patientCity, #NonCalc_DATA.patientState, #NonCalc_DATA.patientZip, #NonCalc_DATA.patientHomePhone, #NonCalc_DATA.patientSSN, #NonCalc_DATA.patientDOB, #NonCalc_DATA.patientGender, #NonCalc_DATA.patientEmployer, #NonCalc_DATA.admitDate, #NonCalc_DATA.dischargeDate, #NonCalc_DATA.arPostingDate, #NonCalc_DATA.lastBillingDate, #NonCalc_DATA.lastPaymentDate, #NonCalc_DATA.selfPayDate, #NonCalc_DATA.closingDate, #NonCalc_DATA.returnMailFlag, #PRECALC_DATA.totalCharges_48 , #PRECALC_DATA.accountBalance_49, #PRECALC_DATA.totalPayments_50, #PRECALC_DATA.TotalAjustments_51, #CHTYAMT_DATA.charityAmount -- 52 , #PRECALC_DATA.InitialPatientResponsibility_53, #OSPATBAL_DATA.outstandingPatientBalance, #PRECALC_DATA.patientPayments_55, patientAdjustments_56 , #BADAMT_DATA.badDebtAmount --57, #PRECALC_DATA.insurancePayments_58, #NonCalc_DATA.insurance1Payments --59, #NonCalc_DATA.insurance2Payments -- 60, #NonCalc_DATA.insurance3Payments -- 61, #INSADJ_DATA.insuranceAdjustments --62, #INSOSB_DATA.insuranceOutstandingBalance --63, insurance1OutstandingBalance , insurance2OutstandingBalance , insurance3OutstandingBalance , hisUserId , agencyCode , earlyOutPlcmtDate , badDebtPlcmtDate , collectorId , billingType , adminHold , balanceVisible, billingIndicator , fileType , firstStatementDate , lastStatementDate , ins1PlanCode , ins2PlanCode, ins3PlanCodeINTO #MASTER_DATA FROM #NonCalc_DATA INNER JOIN #PRECALC_DATA ON #NonCalc_DATA.accountNumber = #PRECALC_DATA.ACCOUNT INNER JOIN #CHTYAMT_DATA ON #PRECALC_DATA.ACCOUNT = #CHTYAMT_DATA.ACCOUNT INNER JOIN #BADAMT_DATA ON #CHTYAMT_DATA.ACCOUNT = #BADAMT_DATA.ACCOUNT INNER JOIN #INSADJ_DATA ON #BADAMT_DATA.ACCOUNT = #INSADJ_DATA.ACCOUNT INNER JOIN #INSOSB_DATA ON #INSADJ_DATA.ACCOUNT = #INSOSB_DATA.ACCOUNT INNER JOIN #OSPATBAL_DATA ON #INSOSB_DATA.ACCOUNT = #OSPATBAL_DATA.ACCOUNTthe temp tabels do not have any indexes but I tried adding and it didnt help.Does it help if I use table variable or how can I resolve this Issue.

Strange Duration numbers for a server-side trace

Posted: 15 Aug 2013 11:55 PM PDT

Trying to hone in a few procedures to identify reads and durations. The reads are undercontrol after some indexes tweaked but still seeing high durations (with low reads). 1) If I add up Duration for SP:Statement Completed - it should equal SP:Completed.:SP:Completed PROCCHILD 1000SP:Completed FUNCTIONCHILD 0SP:Completed PROCPARENT 2000The parent stored proc called PROCPARENT Calls the 2 above it - PROCCHILD and FUNCTIONCHILD. Duration here should be 1000, but it seems to double. 2) Similarly - If I add up SP:StmtCompleted for a give proc. I would expect SP:Completed to equal all of the statements. Its the sum of all the parts. Unless I am missing something which I clearly am. Is there a recompile event or something like that thats not attributable directly to a statement? If so, then I need to include that in my trace.

SSRS 3.0 adding percentage field (calculated field)

Posted: 15 Aug 2013 09:39 PM PDT

Hi all,I am doing a report and in one of the datasets I have to do a calculated field named % Current AR, this should be a simple calculation (Current AR/Total AR) as I normally do in excel, but now I can't get it to do it, anyone has a suggestion? Do you need more information than the one in provided:-).I would really appreciate some assistance since this is driving me crazy...Regards,Daniel

The way to link 2012 with 2008 R2 throuh merge replication

Posted: 15 Aug 2013 11:14 PM PDT

Hello folks!I am going to create the merge replication between 2 instances one of which runs on 2008 R2, and second on 2012.Surely, 2012 is publisher and distributer, 2008 R2 - subscriber.The goal is to organyze 2-way merge replication.The question is hypotetical: Is there a way to restore the 2012's state on 2008 R2? backup/restore won't work as MS doesn't support backward compatibility. I don't like the Generate Scripts service either. Could I use the replication snapshot to make the state of 2008 R2 identical to 2012? How would it be working? Are there the other ways (including 3Dparty's products)?

Good Book On SSRS 2012

Posted: 15 Aug 2013 09:22 PM PDT

http://goo.gl/aXPZU

data tier application vs database project

Posted: 15 Aug 2013 01:06 PM PDT

Can someone explain when to use one over the other please?Thanks

Mapping Dissimilar Data

Posted: 02 Aug 2013 10:35 AM PDT

For a busy OLTP application, what is the most efficient SQL method/process/tool/technique to map two dissimilar databases in real time? Queries from a new application need a homogenous interface whether connected to an old, inefficient legacy DB or to a new one with different structure. Is this what SCHEMABINDING does?See attached diagram.

Importing data from an AS400 database in SSIS

Posted: 15 Aug 2013 04:15 AM PDT

So I am attempting to import data from an IBM AS400 database into SQL Server. I can do it easily enough via linked server - however the processing time is off the chart. In an attempt to speed things up i am trying to create an SSIS package that will import the data. The problem I am having is that the date on the AS400 is stored in the format CYYMMDD. So I need to pull 90 days back when I import the data into SQL. Does anyone have any experience in converting a SQL date to CYYMMDD in SSIS? I have 2 functions in SQL that will convert it to the appropriate format: The Main Fuction is below:CREATE FUNCTION [dbo].[fnStdToDate](@SQL_Date DATETIME)RETURNS INTAS BEGIN RETURN CAST(CASE WHEN YEAR(@SQL_Date) > 1999 THEN '1' ELSE '' END + SUBSTRING(CAST(YEAR(@SQL_Date) AS VARCHAR(4)),3,2) + dbo.udf_padl(CAST(MONTH(@SQL_Date) AS VARCHAR(2)),2,'0') + dbo.udf_padl(CAST(DAY(@SQL_Date) AS VARCHAR(2)),2,'0') AS INT)ENDCREATE function [dbo].[udf_padl] ( @cString nvarchar(4000) , @nLen smallint , @cPadCharacter nvarchar(4000) = ' ' )returns nvarchar(4000)as begin declare @length smallint , @lengthPadCharacter smallint if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0 set @cPadCharacter = space(1) select @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode if @length >= @nLen set @cString = left(@cString, @nLen) else begin declare @nLeftLen smallint set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString end return (@cString) endCREATE function [dbo].[udf_padr] (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )returns nvarchar(4000)as begin declare @length smallint, @lengthPadCharacter smallint if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0 set @cPadCharacter = space(1) select @length = datalength(@cString)/ (case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode select @lengthPadCharacter = datalength(@cPadCharacter)/ (case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode if @length >= @nLen set @cString = left(@cString, @nLen) else begin declare @nRightLen smallint set @nRightLen = @nLen - @length -- Quantity of characters, added on the right set @cString = @cString + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen) end return (@cString) endSo i can run the following sql and get the converted date: select dbo.fnStdToRdcDate(getdate()-90)Result: 1130517So my question is how can I make this compatible with SSIS? It seems I am somewhat limited with the expression builder. Thanks in advance for any help and comments.

SSIS: FTP task fails when called from execute package task

Posted: 15 Aug 2013 09:39 AM PDT

Hi All,So we've got a standard FTP package that pulls a file from an FTP server and deposits it nicely in the folder of your choosing, pretty standard stuff. When I execute the package in SSDT it runs through no problems and grabs my file like a good little package.However! When I call this FTP package using an Execute Package Task from within another SSIS task the execute package task fails giving the wonderfully useful error message: FTP Package Failed. Eventvwr is similarly useless and I've even had a look at procmon with no pointers in the right direction.I'm using the standard FTP transfer and not SFTP. Has anyone come across similar issues?Cheers,Jim.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog