Question about porting data into a new database.

We rewrote one of our legacy C#/asp.net applications that accesses a sql server 2000 database. The new database schema looks very similar to the old one. The major difference is that some of the atributes in the database tables are different. But pretty much we are using the same tables, plus or minus a few.

We need to import the old data into the new database. I have never had to undertake this type of thing before as I am not a DBA, but a developer. I feel a little scared about this whole process. My boss is open to us hiring a contractor to help with the process.

My biggest concern is the referential integrity of the database.

Can someone help me out. Does this sound like something that can be easily done or should I ask for some help.

Ralph

Can you backup existing DB and restore to the new server and "fix" it in place? As in adding/removing any columns, adding any new tables etc. This will at least guarantee you your existing data integrity. Or is this not an option?

|||

That sounds like a really good idea. I will give it some deep thought!

sql

question about locking

Hi,
I have a table where we save loan applications.
I've created a web page for processing these applications.
Now there are up to 10 people using the site, and they sometimes end up with
the same application.
In the table there is a column called STATUS. If the value of STATUS is -1,
the application is ready for processing.
The Id for the application is then selected, the table is updated and the
status changes to 1. Then the row is selected based on the Id.
They started out with only two people using the application, so everything
worked fine and everybody was happy, but not anymore.
So now Im looking into locking!
Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
better way to do this ?
BR,
SteinHi Stein
Locking is a huge topic, so you really should read about it in the Books
Online before you start changing your code.
You need to know that locking is not a problem in SQL Server. SQL Server
acquires and releases locks constantly. The problem is BLOCKING, when a lock
is held for too long and other processes block on the lock.
From your limited description, it's impossible to tell exactly what the
problem is, so we really can't know what will fix it.
The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
the SELECT. As soon as a result set is returned, the lock will be released.
So my guess is that it is not nearly enough to solve your problem.
You can use sp_who to see who is blocked, or use the current activity screen
in Enterprise Manager.
To solve this problem, you are most likely going to need to determine:
Which process is blocked and what exact statement is it trying to execute?
What process is it blocked by, and what resources are being locked by that
blocker process.
Also:
How big is the table?
What indexes are on it?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table where we save loan applications.
> I've created a web page for processing these applications.
> Now there are up to 10 people using the site, and they sometimes end up
> with the same application.
> In the table there is a column called STATUS. If the value of STATUS
> is -1, the application is ready for processing.
> The Id for the application is then selected, the table is updated and the
> status changes to 1. Then the row is selected based on the Id.
> They started out with only two people using the application, so everything
> worked fine and everybody was happy, but not anymore.
> So now Im looking into locking!
> Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
> better way to do this ?
> BR,
> Stein
>|||Hi Kalen,
I'll try to explain.
This is the table:
CREATE TABLE [dbo].[App_Application](
[LOPENR] [int] IDENTITY(1,1) NOT NULL,
[SOKNADID] [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATUS] [int] NULL,
CONSTRAINT [PK_SOKNAD_tmp] PRIMARY KEY CLUSTERED (
[LOPENR] ASC
) ON [PRIMARY]
) ON [PRIMARY]
The table has over 100 columns, so I just included the first four.
So, in this table we save all new applications.
I use the value of the STATUS column to see if the applications is under, or
done processing.
And this is the SP:
LTER PROCEDURE [dbo].[App_GetNextApplication]
@.Ident VARCHAR(20),
@.UPDATESTATUS BIT = 0
AS
BEGIN
DECLARE @.appIdentity VARCHAR(20),
@.Tag VARCHAR(20)
IF @.Ident IN (
SELECT IDENT
FROM App_Application app
WHERE app.IDENT = @.Ident
AND app.STATUS IN (
SELECT
VERDI
FROM
ApS_ApplicationStatus
WHERE
TAG = '<behandling>'
)
)
BEGIN
--Bruker har sknader som er til behandling EDIT: User allready has an
applcation under processing
SET @.tag = '<behandling>'
END
ELSE BEGIN
--Bruker har ingen sknader som er til behandling, hent ny EDIT: User
has no application under processing, get a new one
SET @.tag = '<ikke_registrert>'
END
SET @.appIdentity = (
SELECT TOP 1 app.SOKNADID
FROM App_Application app
WHERE app.STATUS IN (
SELECT VERDI
FROM ApS_ApplicationStatus
WHERE TAG = @.tag
)
ORDER BY app.POST_DATO ASC
)
IF @.UPDATESTATUS = 1 BEGIN
DECLARE @.STATUS AS VARCHAR(20)
EXEC ApS_GetApplicationStatus '<behandling>', @.STATUS OUTPUT
UPDATE App_Application
SET STATUS = @.STATUS,
STATUS_FORANDRET_IDENT = @.Ident,
STATUS_FORANDRET_DATO = GETDATE(),
IDENT = @.Ident,
REG_DATO = GETDATE()
WHERE SOKNADID = @.appIdentity
END
SELECT @.appIdentity AS 'SOKNADID',
app.IDENT,
app.POST_DATO
FROM App_Application app
WHERE app.SOKNADID = @.appIdentity
END
So then, where I set the @.appIdentity, I need to make sure that two users
never end up with the same identity.
Or maybe I have to lock until the @.UPDATESTATUS section is done ?
I dont know, I need help! :)
BR,
Stein Lundbeck
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:<uRoQXwCeGHA.3632@.TK2MSFTNGP02.phx.gbl>...
> Hi Stein
> Locking is a huge topic, so you really should read about it in the Books
> Online before you start changing your code.
> You need to know that locking is not a problem in SQL Server. SQL Server
> acquires and releases locks constantly. The problem is BLOCKING, when a
> lock is held for too long and other processes block on the lock.
> From your limited description, it's impossible to tell exactly what the
> problem is, so we really can't know what will fix it.
> The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
> the SELECT. As soon as a result set is returned, the lock will be
> released.
> So my guess is that it is not nearly enough to solve your problem.
> You can use sp_who to see who is blocked, or use the current activity
> screen in Enterprise Manager.
> To solve this problem, you are most likely going to need to determine:
> Which process is blocked and what exact statement is it trying to execute?
> What process is it blocked by, and what resources are being locked by that
> blocker process.
> Also:
> How big is the table?
> What indexes are on it?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
> news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
>|||Hello Stein,
It seems that in the whole SP you read a row from table and you do n't want
other sessioin to update and/or read the row before you update/process the
row in the SP. You may want to use consider set "repeatable read isolation
level" for this sitaution and put all these query in one transaction. The
row shall be locked in the whole transaction. Also, you may use hint
"xlock" for select query so that it could lock other reads.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Question about connection pooling

If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools?

I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.

Hi,

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

Another thing is that maximum connection pool size is configurable (within connectionstring), 100 is the default maximum limit.

|||

joteke:

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

What if my ASP.NET applications all use the same application pool? In this way, are they still have separate pools?

Thank you!

|||They don't because they have different appdomains. Every ASP.NEt application has its own AppDomain. Only connections in same application, using exactly same connection string, would use the same connection pool.|||

Ok! Now I got it.

Thank your for your help :)

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me