Anything and Everything

Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable. If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1. To create a tail log backup, use below script BACKUP LOG [DB Name]    TO [TLog_Device_Location]      WITH NO_TRUNCATE;   GO Scenarios That Require a Tail-Log Backup If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP Transact-SQL statement. If a database is offline and fails to start and you need to restore the database, first back up the ta

Data Domain provides secure storage for Backup and Archive data Scalability and Performance Reduces storage required by 10-30 times Protects upto 150PB of logical capacity in a single system Completes backups faster - upto 68TB per Hour Efficient Resource Utilization Sends only deduplicated data across the network to reduce bandwith required Reliable access and Recovery End-to-end data verification, fault detection, and self-healing Seamless Integration Integrates with leading backup, archiving and enterprise applications          

https://blog.pythian.com/fix-sql-server-to-postgresql-linked-server-error-requested-conversion-is-not-supported/

I ran into an interesting situation when executing a Remote Stored Procedure on Linked   Server using four part query.    Vista-3139 which is a SQL Server 2005 instance is linked to the SERV2003\SQLDUMMY which is a SQL Server 2000 named instance. When I tried to execute a stored procedure: EXEC [SERV2003\SQLDUMMY].SQLAdmin.dbo.sp_ddlevents   Got the following error message. Could not connect to server SERV2003\SQLDUMMY ' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. I checked the Security settings of the linked server and it was set to “Be made using the login's current security context", which is valid considering that the user has all the permissions to login to the remote SQL server. Then I changed the security context to use a SQL authentication, but still it failed with the similar error message as described above. To troubleshoot this further, I ran

Aliases Description -  https://blogs.msdn.microsoft.com/sql_protocols/2007/01/07/connection-alias/ Configuring Aliases -  https://blogs.msdn.microsoft.com/wwepmcoe/2010/01/07/sql-server-alias-names/

Reporting Service Solutions - here , YouTube video

If the certificate being used on the server was generated using the Legacy Key option in the certificate request form, the private key for that certificate will be stored in Microsoft's legacy Cryptographic API framework. When the web server tries to process requests using its new, Cryptographic Next Generation (CNG) framework, it appears that something related to the RSA private key stored in the legacy framework is unavailable to the new framework. As a result, the use of the RSA cipher suites is severely limited. To avoid the issue, you can try to generate the certificate request using the CNG Key template in the custom certificate request wizard.

A potentially dangerous Request.Path value was detected from the client (*) The * character is not allowed in the path of the URL, but there is no problem using it in the query string: http://localhost:3286/Search/?q=test* It's not an encoding issue, the * character has no special meaning in an URL, so it doesn't matter if you URL encode it or not. You would need to encode it using a different scheme, and then decode it. For example using an arbitrary character as escape character: query = query.Replace("x", "xxx").Replace("y", "xxy").Replace("*", "xyy"); And decoding: query = query.Replace("xyy", "*").Replace("xxy", "y").Replace("xxx", "x");

Event ID 36882: The Certificate Received From the Remote Server Was Issued By an Untrusted Certificate Authority. Because authentication relies on digital certificates, certification authorities (CAs) such as Verisign or Active Directory Certificate Services are an important part of TLS/SSL. A CA is a mutually-trusted third party that confirms the identity of a certificate requestor (usually a user or computer), and then issues the requestor a certificate. The certificate binds the requestor’s identity to a public key. CAs also renew and revoke certificates as necessary. For example, if a client is presented with a server’s certificate, the client computer might try to match the server’s CA against the client’s list of trusted CAs. If the issuing CA is trusted, the client will verify that the certificate is authentic and has not been tampered with. The Schannel provider creates the list of trusted certification authorities by searching the Trusted Root Certification Authori

Smart Re-index Def: Smart re-index keeps the database available unlike normal re-index and the type of re-index(rebuild or re-organization) will be decided based on the fragmentation percentage.  To set up smart reindex, we need to check the fragmentation level of the database and decide whether it should be index rebuild or reorganizing. If fragmentation percentage <30 then reorganize else rebuild After the reindex is performed, we update statistics for that database.  Here's the git link for smart re-index Smar reindex code Above mentioned is one of the ways to configure smart re-index. A job can be scheduled to run the above code and reports can also be configured to check the fragmentation percentage.

                                                                Active Directory in Windows  When a windows server is bought it comes with many capabilities like AD groups for security, IIS(Internet Information Services), ability to built own VPN and many more of which Active directories are created with a domain and the computers which access the network will be connecting to the domain controllers. Practically, there will be a cluster of domain controllers where data will be replicated all over the cluster.

Database Snapshots in SQL Server What is DB Snapshot? Snapshots are read only and are a very good option for reporting purposes. All the reporting related hits could be made on the snapshot instead of on the the actual database, and the burden on the active database could be minimized. Also, as snapshots are read only, chances of any undesired updates by the subscriber (who reads data for reports etc.) could be negated. A database snapshot is a read-only, static view of a SQL Server database (the source database ).It is transactionally consistent with the source database as of the moment of the snapshot's creation and always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.  Multiple snapshots can exist on a given source database. Each database snapshot per

                                                         Active Node in a Cluster Def : This script when run sends a mail whenever there is a change in the active node on which the SQL instance is currently running. Please find the below link for code. https://gist.github.com/tejaswidatla/164e528d8a5b83687a5f892c9b41d9d6 NOTE: Passive node is one that does not have any active service running on it (just like with SQL server services may not be running on passive node but that doesn't mean passive node can be offline). There is a quoram disk which has data on how the drive mapping must happen when the services failover.

                                                                     SQL Server  Its been a while that I have updated my blog. Though Databases and SQL was something that I used to stay away as much as possible because for some reasons, I got to work on these all the day for 8-9 hours and sometimes haunting in the nights and weekends as well. However, it has been a good journey so far and I found some interesting stuff in SQL Server. So, this triggered an idea a couple of weeks back to share some cool stuff that I am learning in SQL Server over numerous sources. Hope I can make this a good series. As there are not much visitors to my blog, let me start with wishing me a good luck. So, here starts the SQL Server Series!!!!!!