The environment for SQL Replication is 3 level:

Multiple Publisher (Changed default port in SQL local and Client)
Remote Distributor (Changed default port in SQL local and Client)
Multiple Subscriber (Changed default port in SQL local and Client)

When I set up Publisher and Distributor as one server (same SQL Instance), then the agent will succeed.
But when I set up Publisher as an Individual SQL Server and a Remote Distributor at the other SQL server. The SQL Replication will fail.

The message I received below:

Message: Named Pipes Provider: Could not open a connection to SQL Server [5]. 
Message: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor". 

This could be done through a few code steps. Please follow the instruction below:

Save your Excel File as “Excel Marco-Enabled Workbook (*.xlsm)


·         Add one column to help you to identify which filed has Capital character visibly
·         Type the formula in to the filed (Change your target field, in the case is A2), then apply to the whole column
IF it returns “FALSE”, it means you have UPPER case in the field
                =EXACT(A2,LOWER(A2))

After a two-week long time of studying the document and having a lots conversations with the vendor and System admin. I finally figured out the security scope for the SQL DBs on SCSM.

Document download link: http://www.microsoft.com/en-us/download/details.aspx?id=27850

Neither the vendor or the document would cover 100% correct settings on practical environment. At least not mine in my company.

I will list the summary below





"You are not logged in as the database owner or as a user that is a member of the db_owner role"
Solution:
obviously, you need to grant the account as DB_OWNER. That's only for avoid the annoying GUI warning. Otherwise, "CREATE TABLE" is enough for Creating Tables. 


Microsoft.BizTalk.Bam.Management.BamManagerException: The BAM deployment failed. ---> Microsoft.AnalysisServices.OperationException: OLE DB error: OLE DB or ODBC error: Cannot open database "BAMPrimaryImport" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'bam_rta_xxxJobXBamView', Name of 'bam_rta_xxxJobXBamView'.
Server: The current operation was cancelled because another operation in the transaction failed.

Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the SQL server does not have dependency on it.
I created a new SAN storage and got a Owner Node after add a Disk to Cluster Storage like below
image
Have you ever encountered “Aborted” status on “Change Data Capture Designer for Oracle by Attunity”?
or on “Oracle CDC for SSIS”? Then you dump CDC trace even used “SOURCE” but can’t find useful information.
For me, yes. I’ve encountered the same situation and I’ll describe a little about my story and how to troubleshooting.
My Environment:
  • Windows 2012 R2
  • SQL 2012 SP2 + CU6

I believe lots DBAs have encountered the error when you add a remote Publication or Subscription.
We do know the “SERVER NAME” should match the value of “@@SERVERNAME” or “Select * from sys.sysservers;
select * from sys.servers

And if you find it doesn’t “MATCH”, then you have to do the step below:
SP_dropserver xxxx
SP_addserver xxx, 'local'
For my situation, we used the name instance I have not the issues above, even though I did it again. It still doesn’t FIX it.
But I’ve found 2 interesting things then I changed it, then it fixed. These 2 things you must ensure it’s available if you want to deploy SQL REPLICATION
  • Open firewall UDP port 1434 for SQL Server Browser uses. I just opened TCP port 2382
  • The SQL instance name CANNOT be hidden from network.

[CDC] Error 22859, 622, 2601

No comments

2015-05-12

When you enable SQL CDC (Change Data Capture) feature on DB and table. There are two job created in SQL Agent
  • cdc.dbnamexxx_capture
  • cdc.dbnamexxx_cleanup
The best practice is to create another SQL “FILE GROUP” and “FILE” then assign CDC tables to the new “FILE GROUP”. I’ve done it. But the job failed and showed the message below

  1: Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. 
  2: Refer to previous errors in the current session to identify the cause and correct 
  3: any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.


and

  1: The Log-Scan Process failed to construct a replicated command from 
  2: log sequence number (LSN) {000015a9:0000395a:001b}. 
  3: Back up the publication database and contact Customer Support Services. 
  4: [SQLSTATE 42000] (Error 18805)  Log Scan process failed in processing log records. 
  5: Refer to previous errors in the current session to identify the cause and correct 
  6: any associated problems. [SQLSTATE 42000] (Error 22859)  
  7: The statement has been terminated. [SQLSTATE 42000] (Error 3621)  
  8: The statement has been terminated. [SQLSTATE 01000] (Error 3621)  
  9: The call to sp_MScdc_capture_job by the Capture Job for database 'xxxxxx' failed. 
 10: Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)

There are two system DMV you can query to see the errors rather than see the job logs.
  • sys.dm_cdc_log_scan_sessions
  • sys.dm_cdc_errors

The easiest way to get SQL version

No comments

2015-05-08

Most of people suggest to use @@VERSION” and “SERVERPROPERTY

But when we manage SQL server, DBA or developer need to do a lots tedious/repeatable tasks to determine the SQL version in order to meet different needs. So the method above is not what I need.

The most convenient way and the easiest way to remember the syntax is

It shows not only the detail SQL version but also the Windows version

But you have to do lots “STRING” function if you want to manipulate the info for program purpose

Message
Login failed for user ‘Domain\MachineName$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: MachineName]

It’s related to SSRS Reporting Server and run as Network Service account. It’s supposed to be good. Because when you added a computer to domain, it will register Local system and Network Service account permission to the SPN records.
That’s why sometime you don’t know WHY you put Network Service account as a service account, then everything will be fixed. (Reference 2)
I’ve been encountered the error for al long time, and I’ve tried lots methods from Google.com
https://connect.microsoft.com/SQLServer/feedback/details/529716/problem-with-cannot-find-database-id-0-the-database-may-be-offline

"Now, what I found out: This server has an AUDIT on a database. If the audit is enabled, I get this error. If I disable the audit, the query runs fine."
Situation:

Could not allocate space for object 'dbo.SORT temporary run storage:  141564582166528' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

Solution:
CHECKDB (Part 6): Consistency checking options for a VLDB



Once you set up SQL Log Shipping, it will produce 4 Major jobs:
I assume the Primary server is R1, the Secondary is R2
  • Alerts (Both on Primary R1 and Secondary R2)
  • Backup (On Primary)
  • Copy (On Secondary)
  • Restore (On Secondary)
I’ve set up already and Backup/Copy/Restore are all worked fine and correctly. But Alerts on Primary R1 has been sent all the time below