ms sql server (en)

ms sql server (en)

  • Changing service account for MS SQL Server 2012 and the following issues Changing service account for MS SQL Server 2012 and the following issues

    Assume that you need to change service account for Microsoft SQL Server 2012 service. If earlier it was System, then now you need to work under some domain account.

    Procedure itself of changing account is quite simple. Just start Sql Server Configuration Manager and in SQL Server Services subtree change properties of service.
  • Step-by-step deploying AlwaysOn on MS SQL 2012 Step-by-step deploying AlwaysOn on MS SQL 2012

    Before Microsoft SQL Server 2012 and mechanism AlwaysOn were released, database administrators had only Database Mirroring as solution for replicating databases. It was the only option to get the "cheap" high availability. And Database Mirroring was not so good as it intended to be. Moreover, as my testings showed (and this is NOT written in documentation), it is NOT synchronous replication and transaction logs.

    In short - we have studied the behavior of the database during intensive recording and different faults. What failures we saw:

    • Planned shutdown of the master server (manual service stop) of the master server without any notification of the mirror server.
    • Unplanned shutdown of the master server.

    Oddly enough, it was a planned shutdown that showed that there is a difference between a mirrored database on the master and the mirror servers.

    Therefore, I would not recommend to use Database Mirroring in production deployments, but take a look at the AlwaysOn technology.
  • Mass import into MS SQL database script with Powershell Mass import into MS SQL database script with Powershell

    While I've been testing failover cluster of Microsoft SQL Server 2012 (more details: Step-by-step deploying AlwaysOn on MS SQL 2012) I was needed to generate mass import of some data into database.

    As soon as I'm very bad in T-SQL, I decided to write script on Powershell for this task.

    We will need:

    • Database itself
    • Table in it for testing purposes
  • Grant access to Azure SQL PaaS Grant access to Azure SQL PaaS

    When you need to grant access to Azure SQL PaaS database, you can use following T-SQL scripts.
  • Transparent Data Encryption / TDE in SQL Server: enable, keys & certificates, backups, restore Transparent Data Encryption / TDE in SQL Server: enable, keys & certificates, backups, restore

    Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure SQL Data Warehouse data files, known as encrypting data at rest. You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
  • Find servers with MS SQL Server installed with Powershell Find servers with MS SQL Server installed with Powershell

    Here is the script that will find servers in your domain with Microsoft SQL Server installed.

    Script can handle unresponsive servers with pings via WMI calls, which is useful if ICMP blocked for some reason. Also - I'm using Powershell Remoting, so please be sure it is enabled on your servers.