SuperCoolMoss's questions

We're currently have one way transaction replication from a SQL Server 2005 OLTP publisher/distrbituor to two subscribers (one SQL 2005 and the other SQL2008 R2). Replication security is via the SQL Agents' domain service account (the same account is used on all boxes).

The SQL2008R2 subscriber is used for BI purposes and hosts a database that has a subset of the Production publisher database tables, with different security and indexes.

We need to migrate this BI subscriber to a newer box with more performant hardware.

The plan is as follows:

• Stop replicating to the BI box (continue replicating to the other subscriber).

  Backup all databases on the BI box (including system databases).

  Restore all databases (including master in single user mode) to the new BI box (this has SQL Server 2008R2 already installed).

  Take the old BI box off the network and shut it down.

  Rename and Re-IP the new BI box to be the same as the old box.

  Rename SQL (sp_dropserver and sp_addserver).

  Reboot box.

  Switch replication back on.

Are there any flaws in this approach?

I need to delete .txt files in a particular directory based on date. Can I do this through T-SQL?

We use a dedicated HP EVA SAN to host the SQL backend for our application. I believe the disks are in a large shared pool (24 disks RAID10).

The SAN is split into several LUNs which hold the SQL components(OS, System DBs, TempDB, Logs, Data, Indexes, Backups).

Is there any performance benefit in having these LUNS split this way when using a shared pool of disks - could we just have configure one large LUN to hold it all?

When I monitor the LUNs using perfmon physical disk counters it does look as though each LUN is isolated.

I'm trying to connect between two SQL Servers to set up log-shipping.

I'm getting the following error when connecting from the Publisher to the Subscriber:

Cannot connect to DRBOX

Additional information: Cannot generate SSPI context. (Microsoft SQL Server).

I can connect ok from the Subscriber to the Publisher.

Publisher is 2 node active passive cluster, Windows 2003 R2 Enterprise x64 SP2, SQL2005 Enterprise SP3 Subscriber is stand alone, Windows 2003 Standard x32 SP1, SQL 2005 Enterprise SP3

Can users who are members of SQL2008's server DBCreator role, access databases created/restored by another user with the same permissions, without having to grant any additional permissions?

I was using perfmon to monitor writes per second on a LUN containing a single SQL2005 database data file. I was under the assumption that database files are only written to during checkpoints or by the lazy writer process, is this assumption correct? as something seemed to be writing to the LUN when the checkpoint and lazy writer processes were inactive.

[EDIT]

The writes seem to happen every 3 seconds or so and is 510 bytes long? I have my suspicions that it's not SQL writing to the LUN but some OS process.

[EDIT]

This write is actually hitting all my LUNs simultaneously - so can't be SQL.

If I change the fill factor of an index, to make the change effective do I have to rebuild the index using "alter index rebuild" or can I also use "alter index reorganise"?

I have a situation where an application is over time opening a cumulative 6000 + connections to a 32 bit SQL2005 SP2 backend from a couple of app servers eventually causing internal memory pressure (dll unload from mem2leave area messages in log whilst simultaneously crashing app). I assume that the application is at fault (not closing connections correctly). I've thrown this back at the app guy but he's suggesting that the problem may lie with SQL server as it's not occurring on a similar UAT environment. Any suggestions for what I can do on the SQL side? I've considered increasing the mem2leave area but worried this will just delay/mask the real problem.

I understand that setting the lock pages in memory privilege to the service account housing SQL2005/2008 services on a 64 bit system uses the AWE API and effectively stops SQL's buffer pool from being paged to disk - increasing stability.

But if you've a dedicated box with enough free memory so you're confident SQL won't page - is native SQL 64 bit memory management faster?

I'm trying to change the maximum transaction retention period in our SQL2005 tranasaction replication environment from the default 72 hours to 120.

Either through T-SQL or GUI I get the following message:

Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25 Supply either @job_id or @job_name to identify the job.

The T-SQL I'm running is a as follows:

sp_changedistributiondb @database = 'distribution', @property = 'max_distretention', @value = 120

Any ideas anyone?

Possible Duplicate:
Can you help me with my software licensing question?

I'm tempted to use a DR server which holds a mirrored copy of a database to also perform test restores and integrity checks of the principle database.

There maybe issues with network bandwidth (copying the backup from the primary server along with the mirrored traffic) and CPU capacity on the secondary DR server (performing redo and running checkdb on Standard edition), besides these, if I do restore and recover the primary database on the seondary DR server, will I have to license the box?

I wouldn't be using this copy of the primary database for anything other than running checkdb (honest guv).

I recently set up SQL 2005 database mirroring (synchronous with witness) on a couple of environments and enjoyed the experience but have a few questions:

1. Does the transaction log chain get broken when a fail-over occurs? Do I need to schedule a full backup to continue transaction log backups on the secondary server?
2. I've noticed that DBCC CHECKDB is executed during a fail-over, does anyone know what options are specified? will this impact VLDBs?
3. The mirroring endpoints seem to be tied into my domain account I was using whilst configuring mirroring through the wizard. Will mirroring fail if my domain account is ever removed? Should I alter the endpoints to use the server's domain service account?

An architect in our company has designed a solution based on 64 bit SQL2005 Standard edition synchronous mirroring between a physical (4 quad core, 32GB RAM) server and a virtual DR server (4 virtual CPUs with 16GB RAM) in two geographically remote data centers with a witness server (1 virtual CPU). Storage is Enterprise class SAN in both data centers.

The front end app is web facing with mixed read/write usage.

As a DBA (who wasn't consulted at the design stage) I'm worried this configuration has been designed with minimizing redundancy as the main criterion and that it's not going to work as a real world solution - network latency and performance of the virtual box will cause unacceptable response times? And even poorer performance if a fail-over is invoked.

Does anyone have experience of a similar set up?

I have a large table which has a clustered index with an identity primary key. I'm deciding on the correct value for the fill factor for this table to minimise page splits. We maintain indexes using a script run daily which measures fragmentation and takes appropriate action. The table contains variable length columns.

My first thought was to set it at 100 (as records should only be written to then end of the table) but I assume that changes to the variable length columns could also cause page splits, so I'm now veering towards 90.

Any advice appreciated.

What are the pros and cons of giving SQL's service account administrator privileges?

We're planning a server which is a clustered SQL2005 Enterprise 64bit with a database of which a subset of tables are replicated one way via transactional replication to single subscriber located at different data center for reporting purposes. The distributor is co-located with the publisher on the cluster. A SAN is used for storage.

As well as clustering, the client want resilience for the publisher database in the event of a SAN failure. They have a second lesser SAN (from a performance perspective) available in (unfortunately for a DR perspective) the same data center. This will have a single 32 bit server with SQL2005 32bit Enterprise attached. The client are aware that in the event of a SAN incident they'll no longer have clustering or replication and a lesser level of performance.

I'm debating whether to use log-shipping or database mirroring to provide the database DR. We're using Quest LiteSpeed for backups and could use it for shipping compressed transaction log backups.

Do either of the two technologies (mirroring or log-shipping) have a lesser impact on the publisher database from performance and replication latency perspectives?

Today I had a situation where I wanted to cancel an update to a table and several indexes and immediately restore back the database from a backup.

I issued a kill command to kill the updating SPID. I then issued a kill with statusonly and there was several tens of thousands of seconds roll back awaiting. I needed to restore the database in a hurry (therefore wasn't concerned with rolling back) - but you cant kill a SPID that's rolling back (as far as I know), or restore a database with outstanding connections.

I decided to stop the sql services, delete the database's mdf and ldf physical files, start the sql services - which shows the database in a suspect state. Then restored the database.

Is there a more controlled / sensible way to approach this situation?

When removing the identity property from a column via the GUI design table option I believe the table (and if the column is a key field; any associated non-clustered indexes) are recreated.

Does anyone know of a way to drop the identity property from a column without causing this overhead?

We have a largish database (350GB) which is replicated to a subscriber which is georaphically in a different location. Unfortunateley we have a slow connection between the two sites.

Today we're getting the following error at the distributor:

The subscription(s) have been marked inactive and must be reinitialized.

Is there anyway we can kick this back into life without rebuilding the subscription from a snapshot?

We're planing to have two SQL servers updated by synchronous database mirroring. This will include a third environment configured as a witness server.

For standard windows patching - what order should we patch / reboot the servers?