Unique Discount Code: LC15KCF8
Security! This is the word comes in mind of every concerned person when it come...
This post will show you how to change the collation settings in MS SQL Server for specific database...
In this post I will show you how you can resolve collation conflict error...
In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...
Generally, we create views and stored procedures (proc here after) ...
SQL Server is a database platform designed for providing large-scale transactions, e-commerce applications, data mining, etc. It is widely used on Business platforms for data analysis, data integration and processing components.
Structured Query Language (commonly known as SQL) is a programming language used for managing data held in relational database management system (RDMS) consisting of data definition language, data manipulation language and a data control language. However, the SQL server database consists of three files:
Among these three, one database file is Log File. The file maintains a log of all the transactions done in SQL Server Database so that information can be later accessed to recover the database. There must exist one log file for each database and it is possible that more than one log file can be created for a single database. The file is in .ldf file extension.How SQL Server Log File Corrupt?
The log files of the SQL server may be corrupted, i.e. while performing some action upon SQL database, an interruption in between the procedure is probable to occur due to which user first has to repair and recover the log file corrupted SQL server and then continue their transaction.
We will be dealing with corrupted log file for repairing it and recovering data from it by executing commands in SQL server as a hit and trial method. User may apply any one the following workaround and if none of the technique resolve the problem then it means that log file is highly corrupted and can only be repaired using a third party tool.
Solution 1: Using Backup of Log (LDF) File
If users create the backup of log file after every transaction, then they can easily retrieve their data from that backup file.
Solution 2: If Event Log Files are Corrupted
If event log files are corrupted, follow the following steps:
Solution 3: Put Database Online & Offline
If crash recovery function is enabled, then the database in online mode leads to transaction roll back. Follow the following steps:
Solution 4: Recover using DBCC CHECKDB
In order to repair a corrupt LDF file, use the WITH TABLOCK option for DBCC CHECKDB. It will recover the data from a corrupted LDF file that has been corrupted or damaged due to some reason such as logical corruption.
Solution 5: Recover using CHECKDB Command:
For rebuilding log files, we use another technique, i.e. putting database into Emergency mode and then executing CHECKDB command. However, to change the database mode execute following command:
ALTER DATABASE dbname SET EMERGENCY, SINGLE_USER
Similarly, for repairing the log file using CHECKDB command execute the following syntax:
DBCC CHECKDB(‘dbname’ , REPAIR_ALLOW_DATA_LOSS)
Here we end up with the fact that manual procedure has a limitation and the technique does not give guarantee the outcome of an exact solution for repairing the log transaction file corrupted in SQL server, it’s just a hit and trial method. However, another drawback is that it is too complex and if something goes wrong then the result may be complete database loss. Therefore, the most reliable and trouble free method is to use SQL Server Transaction Log Recovery Tool, which repair and recover data from corrupted log files. The data integrity is maintained and user can export LDF files into the featured output format for reading the entries without dependency on server.
Database Mirroring in SQL Server is deployed as a method to increase the availability of a SQL Server database in the event of a disaster. It can only be implemented in databases that have full recovery model and works on per-database basis. In database mirroring, two copies of a single SQL Server database are stored on different servers that are separated physically. While the Primary Server provides database to the clients, the Mirror Server acts as a standby server that takes over the place of physical server in case any accident occurs.
In this write-up, we will discuss the procedure to configure SQL Server Database Mirroring
The procedure of configuring SQL Server Database Mirroring is mainly divided into two sections:
For every database mirroring session, following requirements are need to be fulfilled:
In case the server instances run in different domain user accounts, all the accounts require a login into the master database of the other accounts. In case the login does not exist, the users must create it.
For enabling certificate authentication on a server instance, the administrator must configure all the server instances to use both outbound and inbound connections certificates. It is to be noted that outbound server needs to be configures before inbound connections.
Follow the below-mentioned steps for establishing database mirroring in SQL Server:
With the help of the above-mentioned procedure, you can easily configure SQL Server Database Mirroring.
|Jan 5 2016||SQL Server 2016 – AlwaysOn Basic Availability Group||http://www.pythian.com/blog/alwayson-basic-availability-group-sql-server-2016/|
|July 31 2015||SQL Server and OS Error 1117, Error 9001, Error 823||http://www.pythian.com/blog/sql-server-and-os-error-1117-error-9001-error-823/|
|July 9 2015||Reading System Logs on SQL Server||http://www.pythian.com/blog/reading-system-logs-on-sql-server/|
|July 20 2015||Reading System Logs on SQL Server - Part 2||http://www.pythian.com/blog/reading-system-logs-sql-server-part-2/|
|Sep 30 2015||Import / Export Multiple SSIS Packages||http://www.pythian.com/blog/importexport-multiple-ssis-packages/|
|July 28 2014||Unexpected Shutdown caused by ASR||http://www.pythian.com/blog/unexpected-shutdown-caused-by-asr/|
|Jan 23 2014||Script to Collect Database Information Quickly||http://www.pythian.com/blog/script-to-collect-database-information-quickly/|
|Nov 22 2015||Script to Collect ALL Database Information wtih VLF Count||https://gallery.technet.microsoft.com/Script-to-Collect-ALL-82664699|
|May 3 2013||Collect Cluster Information using TSQL||https://gallery.technet.microsoft.com/scriptcenter/COLLECT-CLUSTER-INFORMATION-9a75e4a7|
|Mar 9 2013||Configure Auto Growth in Fixed MB||https://gallery.technet.microsoft.com/scriptcenter/Configure-AutoGrowth-in-f4f3d7d1|
|Jun 26 2015||Script to Monitor Database Mirroring Health||https://gallery.technet.microsoft.com/scriptcenter/Script-to-monitor-database-0f35c5d7|
|Jun 26 2015||Script to Monitor AlwaysOn Health||https://gallery.technet.microsoft.com/scriptcenter/TSQL-for-AlwaysOn-Health-6aae827d|
Last month, I have requested my manager for an approval so that I can attend SQL Server Geeks Annual Summit 2015, and now when I hear back from him, I am really very excited that I will be attending.
You must be wondering what is so exciting about this Summit ? Well, let me tell you, there are number of reasons, at least for me to be that excited.
There are some personal reasons as well for me to attend this event, as I already mentioned – to meet few friends in-person, and to make few more friends. I have been knowing few of these friends while I was aspiring DBA, when I was MVP and when I was being transformed as a professional.
I have been knowing Satya Shyam K Jayanti when I was aspiring DBA, I learned many things from him. Amit Bansal, I know him since 2007 when I was an MVP, though we didn’t meet each other personally we have had many occasion to talk over phone, email and chat. Prashant Kumar is my colleague and friend, we’ve already met in-person and we talk to each other often as he is working as technical escalation, one level up then me. I will also meet Pinal Dave ( Blog | Twitter ) if he is in city, he don’t need introduction in SQL Server community, he is like brother to me. There are others whom I have read and it would be fun and exciting to meet if time permits, they are Benjamin Nevarez, Sarabpreet Singh and Manohar Punna.
I would like to thank them all and wish belated Guru Purnima, all as I have learned things from them all. I have always admired dedication and zest that Pinal and Amit showed for community, I wish someday I will be able to match that level of energy and dedication. Sorry I missed to name few, if I write about them all this list and post will not end.
In last, I would like to thank Amit once again for organizing this event and providing a platform to learn and socialize with alike minded community people. I would also like to thank my manager – John Eisbrener and CEO – Paul Vallee and my company Pythian for sponsoring me so that I can enjoy the company of alike minded geeks and learn something new.
See you there at #SSGAS2015!!!!
I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS. Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance.
With lot of excitement I tried connecting to SSAS – it was unsuccessful attempt!! It shows me an error message which says - A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
As we always do – Googled and it lead me to the connect item filed by Aaron Bertrand ( Blog | Twitter ). Reading through the post I came to know that if I try to connect to the named instance SSAS it’s chance that I get this very error. I also have the similar case, I have renamed my machine hence this error showed up with the named instance.
Workaround: As suggested in the comment section, I have granted NTFS permission on folder program files (x86)\microsoft sql server\90\shared\asconfig for the SQL Browser service account and I am sorted!!
Reference: Microsoft Connect ID 679792photo credit: JLaw45 via photopin cc
—Hemantgiri S. Goswami (http://www.sql-server-citation.com)
Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me.
I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified. I can say, I am lucky, but all I know that I have worked hard – a lot. I have tried to learn a lot, from friends, from colleagues, from online web forums, books online, books and MS Evangelists, and still learning. And, that pays me well, I participate in community activities, speaks, blog and have been able to put my thoughts together to publish a book on SQL Server Cluster – this way I am surviving as a professional.
To be honest, during my career I have interviewed a lot many people and many of them are certified but they don’t have exposure and practical troubleshooting skills which was expected; apart from this, there are dumps available that can made it easy to clear an exam. Please, Neither I am not criticizing those who has passed nor I am saying all who haven’t passed aren’t good as professional, but I am just trying to make a point why I haven’t attempted one. With this kind of experience it never interest me to pass the certification.
And, then, MCM was introduced. I have heard a lot about Oracle exams which has lab test as well and MCM is similar kind of exams. Brent Ozar ( Blog | Twitter ) blogged about What is MCM in 2010. I have followed all his posts about MCM and has developed my interest in attending MCM training followed by an exam. Event though the exam and training fees are way too high for me, I have made my mind that I will utilize the money my employer provide each of us for training + some from my pocket to get train and earn this title – but, sadly, it is gone now. Since MCITP is a pre-requisite for MCM, I have made my mind, attempted and failed. However, I was sure, I was reading more and will clear it in next attempt. But, I am not sure, whether I will be taking an MCITP now.
I will be more than happiest person, if this decision for MCM exam are reversed or some relevant / at par exams are introduced. In our country, it is said that “whatever happened has happened for the good, and whatever that is going to be happen in future will be for good”. I am hoping that this saying will hold true.
At the end, I would like to say, if you are believer and aspirant of this exam just like me please take some time and vote on the MS Connect site for reconsideration.
Hemantgiri S. Goswami (http://www.sql-server-citation.com)
People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.
SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.
To use the BCP utility from TSQL, we need to execute “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -
Once successfully executed, the below script with the required changes could be executed to download the files -
Hope, this helps & save a lot of your valuable time.