01/16/2004 Mssql2

The server has run into a problem, we're checking the disks currently. If all is OK, it should be back shortly.

[vawebdev]

John,
It has been almost an hour and the server is still down. Is there any update?

It looks like the server has experienced a rather severe issue. We are currently looking at making the decision to reinstall the O/S.

CLARIFICATION:

We are going to reinstall SQL, not everything on the server. Re-install to begin in the next 5 minutes.....

MSSQL 2k is reinstalling now. I am going to reinstall it upgrade it to SP3 then put all data back in the data dir. This should fix the issue. I hate reinstalling any thing but this was about the only fix MS had.

[admin]

msSQL2k SP3 installing now. After thats done just need to put the data dir. back and pray.

[antic]

OH YES!! *swoons*

The main reason using SQL Manager is a pain is the amount of time it takes to DO anything! This is a godsend! If we can just get Brad to install it on a server for us.

The question is, does it support multiple users at one time, or will we each need to installed the .NET app in our own account somewhere?

If the latter is the case, then the Matrix will have to run the MSI on every one of their Windows servers to register the COM object that's needed... read on...

I successfully installed the ASP.NET application on a new site I created on NT14, hoping beyond hope that this is all I needed to do, and then could log in to my SQL account from there.

But alas! There's a COM object that needs to be installed (which is obviously done locally by the MSI script). The app runs, and appears to log in to the SQL server using my normal login name and password, but generates an error when trying to list the databases, saying a required COM object is missing.

I have also installed in locally, and can access my local SQL server no probs. However when I type in the remote SQL server (eg. mssql3.mydomain.com), using my normal sql login details, it still takes aaaages to list the databses and do anything, of course, same as SQL Manager does. So much so, that you will need to add this line to the app's web.config file, otherwise the scripts time-out:

<system.web>
...
<httpRuntime executionTimeout="600"/>
...
</system.web>

I'm sure everyone will want to use this app.... can we please have a test installation set up somewhere? Hopefully the one installation will support multiple users...

[antic]

I think the import/export is data-only unless I'm mistaken. In any case it wouldn't be a true SQL BACKUP statement because the privileges on our accounts just don't allow it. It will at most be the same as a DTS type backup using SQL Enterprise Manager.

Newmem, the most expedient way I've found for making changes to SQL data online is using MS Access. Just set up an ODBC link to the SQL database, and use Access's "import table" function to import the tables you want to view and edit right into Access. Then you can open the Access MDB file any time you want to edit data, without using web pages or the slooowww Enterprise Manager.

You'd think Microsoft would just somehow speed up Enterprise Manager.. it's just the incredibly time-wasting lag which makes it suck for online stuff.

[Brangwyn]

Newmem, ASPEnterpriseManager will let you change your data thats no problem, the reference made above I believe was made in relation to changing the actual database schema.

If you wanted to change that data via a query though you would simply use something like

UPDATE table1 SET column1 = 'whatever' WHERE ( column2 = 'John Xyz' AND column3 = '02/20/2004')

The import/export function of the MS tool works very similar to phpmyadmin it simply creates a .SQL file containing all the commands to recreate both database schema, stored procedures and/or data and the file is then downloaded and saved locally. So yes this would be perfectly adequate as a backup of small/medium size databases at least.

[newmem]

thanks......but can AspEnterpriseManger help me change the value without running a query? Like EM?

The MS tool sounds great......hope Brad plans on testing and implementing it. That would solve the problem of making end users responsible for making backups in case the automatic-backup system fails or all data is lost.

EM is not possible to use at all, even if I could connect it takes too long to show the db and not everyone has got fast and stable net access.

[DatabaseMentors]

For most of us, the GUI of Enterprise Manager is great for editing the database schema. Actually I typically use EM to generate my script then build a huge SQL text file that I run in Query Analyzer. Of course, I have the tools installed to do that. Technically speaking, everything EM does is build T-SQL statements and execute them via a GUI, therefore with proper knowledge of T-SQL (and Books Online) anyone could write the SQL statements, and pass them via a web page. A simple form could be on an ASP page to enter the SQL String and execute the command through the web server connection to the SQL Server.

My issue still remains, how do I make a backup of my clients databases. I have SQL Server running locally on my development machine. I have EM. I have tried to do a full export (generating DTS) and I do not have permissions according to MSSQL2. What I have actually done is created a SQL Script that literally builds a table of SQL Statements required to build the schema of the current database (it is missing some features because I felt it was quicker to do manually than building code to do it, like adding roles or granting permissions). I have this script that builds the table of statements as a Stored Proc (sp_BuildSQL) on MSSQL2. I then wrote a simple web page that will open a connection to MSSQL2 (source) and a connection to my development server (Destination). With the source connection I open the dataset result of my Stored Proc (sp_BuildSQL) and then for each record in the dataset I execute the string through my destination connection. After the building of the schema, I then copy the data -- currently manually. This too could be scripted. One thing to be careful with is Identity columns. Set your identity off prior to importing source data so ID fields maintain integretity.

This is a very complicated process, fortunately I was able to code this much. It works and is fairly fast. I just hate having so much bandwidth being used everytime I need to make an off-line backup of my client's database.

Thanks to Vortech for replacing the CPU on MSSQL2 -- It seems we have finally seen consistant uptime since the 2-3 weeks that plagued starting this thread. Unfortunately my clients are still doubtful.

Scott Friend
Database Mentors

[soky]

I would think that once you have made a carbon copy of the table structure in your local DB... DTS scheduled can accomplish your backups automatically as often as you like.

The other method I would look into is a "pull" publication... sorry I can't help with that though.

--- I have been trying to come up with this scheme as well... it would appear that DTS with a scripted data transformation is the best way.

step one: create a duplicate database locally. (Careful with indexes and keys)
step two: Create a DTS package including and data transformations.
Step three: Schedule the DTS to run every night at 2:30 am (or at a time convenient to us all)
Step four: Create a back up schedule for your local database that coincides with your DTS run.

Now you have a working copy of your database locally + a backup version if needed to restore on another SQL server.

These are just my ideas based on a recent issue I was forced to resolve... what ever you chose; I would appreciate hearing about your solution.

[Brangwyn]

Originally Posted by newmem

thanks......but can AspEnterpriseManger help me change the value without running a query? Like EM?

Newmem, ASPEnterpriseManager will let you change your data thats no problem

EM is not possible to use at all, even if I could connect it takes too long to show the db and not everyone has got fast and stable net access.

But once the initial waits over it should be quite usable, I've used it on 56k modem connections before when out of town and never had any real complaints.

My issue still remains, how do I make a backup of my clients databases. I have SQL Server running locally on my development machine. I have EM. I have tried to do a full export (generating DTS)

Never had a problem exporting a DB via DTS from Vortech, you are logging in with the customers credentials aren't you ? .. you also need to make sure you don't copy security objects or settings and create the customers login on your local SQL Server as well.

[DatabaseMentors]

Actually my error message when attempting to copy via DTS was saying that I didn't have access to database XYZ while I am logged in as DBO for MYDB. I have no idea where it gets the database XYZ from, when in EM I can manipulate objects in MYDB without any problems.

I suppose I should make a trouble ticket about it, but honestly I was so fustrated with responses I had received during the last outage, my energy was better spent building a way to work around the limitations that seem ever changing. (Now I am working on explaining to my clients about POP disappearing.)

Thanks for telling me that I SHOULD be able to use DTS and that a trouble ticket should be able to fix it up for me.

Scott Friend
Database Mentors

[antic]

Just a heads-up... I installed Microsoft SQL Web Data Administrator (see post #77) and from that time I couldn't use Enerprise Manager to connect to either my local SQL server databases or the ones in my live accounts. The error I was getting back was "SQL Server does not support encryption."

[edit]My apologies, it wasn't that, it was the previous version - "Web Data Admin" found at http://www.microsoft.com/downloads/d...displaylang=en (see post #83) which caused this problem. I have reinstalled the later version from post #77 and it is working ok. Don't install the version in post #83.[/edit]

Uninstalled it, still no go... had to do a System Restore back to just before it was installed, then everything worked ok again.

Perhaps it installed a different certificate on my machine or played with encryption settings... just letting you know in case you have the same problem.

If this is indeed caused my SQL connection problem, I would advise extreme caution if Vortech thinks of installing this on one of the SQL servers, in case we all suddenly experience problems connecting.