Speakers dinner on the way

Leave a comment

I was selected as a speaker at the SQL Saturday #275 in Copenhagen, Denmark. Now I’m at the hotel, polishing the presentation while waiting for the speakers dinner.

<yoda>A nice tradition, the speakers dinners are. </yoda>

Hopefully a lot of people will join tomorrow for a day full of SQL Server stuff.

Links and downloads will show up after the conference…

SQL Saturday Homepage

Restore Master

1 Comment

You’ve just rebuilt your master database. You discover that your other databases are gone, all logins are missing, pulse is rising…

If you take regular backups of your system databases (master and msdb primarily) you have nothing to worry about. If your database files are on undamaged drives you are even better up.

To get all the logins back and all of your user databases “re-attached” you only need to restore your master database. And restoring the msdb database gets you the jobs, schedules and alerts back as well.

To tell the truth: This is not something you do every day, it may even be a bit scary. So here is a simple way to restore your master database, that works in SQL Server 2008, 2008 R2, 2012 and in 2014.

NOTE: commands are written against the default instance (named MSSQLSERVER). If you have a named instance, you’ll have to find the name of it first…

1. Start an elevated command prompt.

2. Run : net stop mssqlserver

3. Navigate to your instance’s “binn” folder (i.e. “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”)

4. Run : sqlservr.exe -m -c -s MSSQLSERVER
This starts sqlservr (the server executable) in single user mode (-m) as a console/not service application (-c). And the instance name is MSSQLSERVER (the default instance)

!I got a warning from Windows built in firewall at this step. It’s perfectly OK to ignore, and NOT let the application communicate on networks – we’re local so the restore will work anyway.

5. Start ANOTHER Command prompt – DO NOT close the other one.

6. Run : sqlcmd -s. –E
SQL Server command tool logs in to the default instance, locally (-s.) with Windows Authentication (-E)

7. 1>restore database master from disk = ‘full path to backup file ‘
2>GO

Your other window should now be back at the command prompt. Database restored.

Since the method uses only console tools, it works fine on servers with sparse UI possibilities Ler

Rebuild Master

2 Comments

The master.mdf is corrupt. The disk failed… Powerfailure? This never occurs at a convenient time. The ERRORLOG-file has a message similar to this.

Error 2(failed to retrieve text for this error. Reason: 15100) occurred while opening file
‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf’
to obtain configuration information at startup.

This is of course easily fixed. It’s only to resore the backup we have of all our system databases. Just one small problem… SQL Server doesn’t start.

Time to rebuild the system databases. But first : Documentation!

  1. Look for “Authentication mode is MIXED.” (or “Authentication mode is WINDOWS-ONLY.” ) Write it up.
  2. Document what Collation you want. If you want “same as before”, just forget it. Otherwise write the new Collation on the same piece of paper. (I’m documenting “Finnish_Swedish_CI_AS”)
  3. How do you connect to SQL Server? If you have a backslash somewhere in your servername, you have a named instance. (connecting to COMPUTER\FINANCE gives you the instancename FINANCE) However if you only use the servername, you probanly have a default instance, in which case the instancename is MSSQLSERVER – Onto the paper!

 

Command:

Same syntax since SQL Server 2008. Still works in SQL Server 2014 (CTP2) and has no signs that it would stop working:

Setup.exe /ACTION=REBUILDDATABASE
/INSTANCENAME=MSSQLSERVER  
/SQLSYSADMINACCOUNTS=win7x64pro\mikael 
/SAPWD=P@ssw0rd
/SQLCOLLATION=Finnish_Swedish_CI_AS

  • ACTION – obviously
  • INSTANCENAME – the instancename documented in item 3.
  • SQLSYSADMINACCOUNTS – Windows account(s) who will be sysadmin of the server.
  • SAPWD – This parameter MUST be present if item 1 states the server started with MIXED autentication.
  • SQLCOLLATION – from item 2.
  • QUIET – I recommend using this. Saves some clicking.

OK, now you’ll have to run this command in an elevated command prompt. That is the command prompt starting in C:\Windows\System32 instead of in your user profile. Then it’s only to navigate to the folder of “setup.exe” and run the command line. All words are typed on one line BEFORE hitting ENTER!

If your installation is made with C:\Program Files\ as the root, the setup.exe can be found here:

SQL 2008 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe

SQL 2008 R2 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe
(I found another setup.exe in the same folder as for SQL 2008, but running that file generated errors instead of databases…)

SQL 2012 – C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\Setup.exe

SQL 2014 CTP2 – C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14CTP2\Setup.exe

and my guess is that RTM of SQL 2014 will use the same path as before:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\Setup.exe 
or potentially 
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14\Setup.exe

 

For all Swedish speaking readers, please check out SQL Server på svenska

Prepairing for SQL Saturday

Leave a comment

Copenhagen is the location for this free event about SQL Server. My session this time is about LSN and the different backup combinations. How you can mess things up by yourself and how to avoid it…

ASYNC_NETWORK_IO for dummies

Leave a comment

I often come across “less than optimal” applications, when I do performance tuning. You know, that kind of application that are kind of responsive, but the main issue is the slowness. Everything about the application is sloooow…

What is one of the signs that you might have a slow client application? (Disclaimer: Slow = Doesn’t consume SQL Server data in zero or less milliseconds. All applications are more or less slow, but abnormal slowness can make users mad.)

The magical query:

SELECT *
FROM sys.dm_os_wait_stats
order by wait_time_ms desc

According to Guy Bowermans article, the statistics lifetime is (about) the time of the SQLTRACE_INCREMENTAL_FLUSH_SLEEP.

I now did some illustrative tests: I wrote an extremely simple console application, selecting the ID column from a table with ~1000000 rows.

Code: (C#, Console project)

static void Main(string[] args)
{

SqlConnection con = new SqlConnection(“Data Source=.;Initial Catalog=testdata;Integrated Security=SSPI;”);
SqlCommand cmd = new SqlCommand(“SELECT SalesOrderID FROM [Sales].[SalesOrderDetail]”, con);
SqlCommand cmdclearstat = new SqlCommand(“DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);”, con);

con.Open();

cmdclearstat.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
int a = rdr.GetInt32(0);
//Console.WriteLine(rdr.GetInt32(0));
}

con.Close();

}

When I ran this code, the SQLTRACE_INCREMENTAL_FLUSH_SLEEP was 4000 at the time of the application end. The ASYNC_NETWORK_IO was at 122, giving us a wait ratio of 0.03.

The “problem” resulting in the real issue is: My application takes 4 seconds to run – without any feedback to the user. Not many users are comfortable with that. The “solution” is to uncomment the Console.Writeline() line. (I actually also removed the int a… line, in order to reading the same amount of objects.)

Results was amazing! I could follow the progress of the application as it looped through my rows. When the application was ready, the SQLTRACE_INCREMENTAL_FLUSH_SLEEP had reached 120024! Yes that’s correct! Two minutes of wonderful interaction 🙂 And the ASYNC_NETWORK_IO (the time SQL Server waited for my application to get the next row) was 120201. That is a ratio of  1.0015.

The server indicates that a “slow” application has made the server wait a lot! So what should you do?

Sometimes you have to choose between the unresponsiveness and the slowness, but sometimes you can get the best of the two worlds. You can try to update the screen every 10000 row, or even every 50000 row. Even with the code overhead, this application completed in virtually the same time as the blazing fast one. The difference was ASYNC_NETWORK_IO waited for 160 ms, resulting in a  0.04 ratio. Still acceptable, and the application (randomly) outputted data, making it seem more responsive.

“Optimal code fragment” replacing the while(rdr.Read()) statement:

int count = 0;
while (rdr.Read())
{
int a = rdr.GetInt32(0);
if (count % 50000 == 0)
{
Console.WriteLine(a);
}
count++;
}

Hopefully you’ll be able to identify some issues with these samples.

Certification Rush

1 Comment

Just hours ago I did my seventh certification in four days. Mr Brain is ready for some Christmas. The guys at Informator, Malmö sponsored my certification frenzy (thanks Acke) so I could do two certs per day, between 8 and 10, before the full time consulting started.

Result was OK. Not perfect scores, but still passing score on all 7 SQL Server 2012 certifications. Tonight I’ll celebrate with friends and tomorrow it’ll be business as usual 🙂

So it’s official

Leave a comment

I’ve been working for myself for more than eight years now. I like it. The fact that I’ m my own boss. I decide what to do and what to buy 😊. However sometimes you need co-workers. You know, the persons you can talk to and ask stuff without the risk that they will think you are strange and will never hire you again…

I got the opportunity to work for SolidQ. A really “front line” kind of company. I will still be in business as usual, but I can also be reached at my new company. I havre already found this arangement useful and fun, and  and can’t wait to contribute to the SolidQ community.

mwedham@solidq.com

Older Entries Newer Entries