Best of Q&A from Webcast: SQL Server2005 Management and Administration

Greetings!

Below I’ve pasted an edited and cleaned up copy of most of the Q&A from today’s webcast on SQL Server 2005 Management Administration. Big BIG thank you to Bryan von Axelson for handling the Q&A on the backend, and who’s work this really represents.

-Kevin

“As a technet subscriber I have been watching the development of SQL2k5 and have participated in some of the beta / ctp iterations, now I seem to be having problems eliminating those installs from my computer and am having problems installing SQL2k5 developers edition from my technet subscription, the install seems to see a nonexistent ‘default instance’ has this been an ongoing problem and is there a quick and fast solution?”

I have experienced the same thing, here is a link to thread on microsoft.com looks like Dan Jones might have a script for you, see his response and try e-mailing him for the script - https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=150545&SiteID=1

“If you installed SQL Server in Windows Authentication mode can you go back and change it to mixed mode later?”

Yes, you go into the properties of the Server, and there is a checkbox to add mixed mode

“If I have different domains which has different password policy exists. Which password policy will be used for SQL authentication logins if I opt Enable password policy.”

The domain that the SQL Server is a member of.

“Can you backup database and transaction log in the same backup job?”

Yes

“What are the advantages to registering servers, instead of just connecting to them in the object explorer?”

Registered Servers are just a way to manage or maintain the list of servers you’re responsible for and can easily connect to.  You may not want to connect to all servers.  But if they are registered and something happens (it goes offline or is otherwise unavailable) you will see it in registered servers window.

“Are the backups stored on the SQL server itself, when using the backup and restore tool?”

If you choose it, it can be stored on a disk on the SQL Server, another server, or go to tape. This is setup when creating the backup job.  I typically will back it up to a different disk than the database files are on during the day, and then these backup files will go to tape at night during the server backup.

“There are 2 data centers. both data centers have sql server, clusters, SAN. what kind of disaster recovery strategy will guarantee 0% data loss in case if data center 1 goes down (power outage or some other trouble). All the users are working with data center 1, what is the safest and the fastest way to deliver data to data center 2? SAN replication ms mirroring (not released till March if I’m correct), log shipping will not insure 0% data loss. between data centers is OC3 (155 Mb/s) ~45 ms latency typical.”

You are on the right track.  Mirroring will address this because you can put it in a mode that the data has to be written to both servers until it tells the application it can continue. You might want to look at Peer to Peer Replication, it is transactional based replication and can eliminates distance, there still is a latency so it won't be 0 %.

“Is there a functionality that will create a data dictionary for me on all the tables and view that i have?”

You should be able to pull this with one of the new DMV (Dynamic Management Views).

“What command did you type to get an editor? And is that editor part of SQL Server 2005 install?”

The editor he used was the old ED editor.  It’s not included with SQL, but an old DOS-like tool.  You can set what your editor is in an environment variable and then launch it from within SQLCMD interactive mode.

“I may have missed this but - to connect from one dbserver to another does a link have to be registered or can a connection be set up via a connection string only?”

You can connect without registering a server, by selecting Connect in the Object Explorer.

“Does SQLCMD replace OSQL?”

Yes – but don’t fear.  OSQL is still in SQL 2005 and available to use your current scripts with.  It might not be in the next version of SQL, however.  This is so you’ll have time to migrate your scripts to SQLCMD.

“But can you connect via a script, like you would in c#. If there is a global reporting server and maybe 30 sql servers in the field and want to have a script to run that programatically connects to the 30 servers and performs query. I was hoping to do this via a dynamic script - everything runtime.”

Yes you could create a script to connect

“How can I use SQLCMD to update record to related tables in one cmd.”

Check out - https://msdn2.microsoft.com/en-us/library/ms170207.aspx

“I didn't follow the reason why I should use config manager instead of service manager to manage SQL-related services. Could you elaborate more? thanks very much.”

There are hooks into the SQL Service and operating system, it has been recommended by the SQL Product group to use the SQL config tool, because this tool has been developed to manage these services. The Service Manager does not, for example, know which registry areas also need to have special permissions granted to your service account chosen.

EXTRA: Cool Tutorial on SQLCMD
https://msdn2.microsoft.com/en-us/library/ms170207.aspx

“What is Physical Design Structure - is it like a Diagram in SQL Server 2000?”

Is it partitions, indexes... - https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

“Does online indexing effect the SQL DB peformance?”

A little, yes. Minimally, it will take longe to index it as opposed to offline indexing, but that is okay because we are online.

“If I have bad clustered index, [Database Tuning Advisor] will recommend I drop that clustered index”

Yes

“The Tunning advisor is available just in 2005 version how about the 2000?”

In SQL Server 2000 we had the Index Tuning Wizard.  And yes, you can run the new DTA against a SQL Server 2000 served database.

“Is xp_sqlmaint still supported?”

Yes - https://msdn2.microsoft.com/library/ms188408.aspx

“What is the better way to setup report server? Snapshot, or backup and restore the database to report server?

I would prefer the backup/restore.  Database Snapshot is only available in the Enterprise Edition of SQL Server 2005, so you may only have backup/restore as an option.

“Is Analysis Manager Built in to the Management Studio as well?”

Yes

“Are there any tools to analize statistics, like space usage by database, table, how do they grow, the same for log, amount of connections, cpu usage, I/O, etc. any reports that allow to see how does the statistic change?

Yes.  Look at the new DMV (Dynamic Management Views) as well as in the management studio you have some reports on the summary view.

“Will we be going over Analysis Services?”

No that is considered BI.  Here are some links for you to start with - https://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=SQL+Server+2005+Analysis+Services

“Is ED editor part of SQL Server 2005?”

No, thank goodness.

“Can you talk about enhancements to SQL Agent Centralized management (MSX/TSX) in 2005?”

A lot of changes focused around security, and performance - Here is a great link on the changes - https://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/newsqlagent.mspx

“Does SQL Server have Database event alerts?”

Yes

“Is operation manager [MOM 2005] part of the SQL 2005 Install or is it an extra add on?”

Microsoft Operations Manager 2005 is a separate product.
https://www.microsoft.com/mom

“Good show.”

Thanks!

“In profiler can we trace activity based on Database_role?”

There are numerous settings in profiler, but I am not aware of a "Role"

“What was the blog URL again, please?”

https://blogs.technet.com/kevinremde  (but you already knew that)

“How do you setup database mail?”

Wow.. big question. But you'll find it under Management. Just right click on Database Mail and "Configure Database Mail". :)

Now THAT's a Jet Ski!